Why does MySQL return two different data sets if I do a `WHERE field=1` and `WHERE field='1'`?

Question

I noticed something weird -- MySQL is returning two different data sets when I do a query with WHERE field=1 and with WHERE field='1'.

The field is defined as enum('0', '1', '2').

How is it possible that I get different data sets?

For the first query I get roughly 500 records. For the second I get 19 (!!!).

I can't explain it. Any ideas?

Thanks, Boda Cydo.

Answer 1

What datatype is "field"? In one example you're comparing the value of "field" to the NUMBERIC value 1, and in the other you're comparing it to a string "1", i.e. a one character string that contains the text "1". The two are not necessarily the same. [Edit: I'd forgotten about MySql ENUM's, it's been a while since I actively used MySql, so for this specific case, what KennyTM said =)]

Without seeing the dataset you're using, and the table definition (to view datatypes), along with the specific version of MySql you're using, I can't give a better answer than that. But, it does boil down to the fact that the two queries you're performing, whilst superficially then same, are actually different.

Answer 2

In MySQL the enum index starts with 1 (actually, 0 is the empty string). So

field = 1

should be viewed as

field = '0'