MySQL Unexpected Results: "IN"-clause (number, 'string') on a varchar column

Question

In MySQL, why does the following query return '----', '0', '000', 'AK3462', 'AL11111', 'C131521', 'TEST', etc.?

select varCharColumn from myTable where varCharColumn in (-1, '');

I get none of these results when I do:

select varCharColumn from myTable where varCharColumn in (-1);
select varCharColumn from myTable where varCharColumn in ('');

---

Note: I'm using MySQL version 5.0.45-log (show variables like "%version%";)

Note 2: I tried this on a number column as well, but I do not get unexpected results there.

Answer 1

As documented under Comparison Functions and Operators:

You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN expression like this:

SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

Instead, write it like this:

SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

Answer 2

Your expression is:

where varCharColumn in (-1, '')

The list has to have consistent types. The first element says "this is a list of integers", so the second value is converted to an integer. And '' becomes 0.

In fact, any alphanumeric string that starts with a non-digit is also converted to 0 for an integer comparison. So, you have this situation

'A' in (0)   --> TRUE
'B' in (0)   --> TRUE
'A' in ('B') --> FALSE

You can readily test this with:

select 'A' in (0) union all
select 'B' in (0) union all
select 'A' in ('B');

You can see it in action with a column:

select val in (0), val in ('0'), val in (0, '')
from (select 'A' as val) t

This returns true, false, true. However, note that val in (-1, 'B') returns FALSE in this case. MySQL is treating the empty string differently from a real string, perhaps inconsistently with the documentation.

That this is true with columns is exhibited by:

select val in (0)
from (select 'A' as val) t;

Who said logic can't be fun?

To fix this, make the list all be of consistent types, probably by putting single quotes around the numbers.