CODEWITHSUNDEEP
mysql
chuse
chuse

Reputation: 383

where clause produce weird result

I want to get the maximum value of a column for the first 1000 not null results for some condition. Then, when for the next 1000, and so on. I do this for different conditions, but here I found something strange, when I use dayofweek. The first command I show you works:

mysql> select max(id),max(d20) from (select id, d20 from data where d20 is not null and id<1000000 and dayofweek(day)=1 limit 1000) x;
+---------+----------+
| max(id) | max(d20) |
+---------+----------+
|  100281 |    13785 |
+---------+----------+
1 row in set (0.44 sec)

but actually I want this second command, which doesn't work as expected. 

mysql> select max(id),max(d20) from (select id, d20 from data where d20 is not null and id>100000 and dayofweek(day)=1 limit 1000) x;
+---------+----------+
| max(id) | max(d20) |
+---------+----------+
|  303765 |        0 |
+---------+----------+
1 row in set (0.02 sec)

Any clue?

Upvotes: 0

Views: 68

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Take the extreme case of the limit being 1.

That means, the subquery returns any row (there's no order by to make the row deterministic) that has id<1000000, which makes MAX(id) and MAX(d20) return the values from that row only. Hardly representative of the total collection.

Raising the limit to 1000 will just make the sample bigger, but will still give an indeterministic result depending on which 1000 rows are sampled (assuming there are more than 1000 rows that match). You may very well get a different result every time you execute the query, so expecting a particular result won't work.

If you need a deterministic result, add an ORDER BY to your subquery before limiting the results.

Upvotes: 1

Related Questions