0

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.

Briguy37
  • 8,342
  • 3
  • 33
  • 53
  • Wouldn't a comparison with `-1` cause casting to a numerical value for comparison purposes? – tadman Sep 06 '13 at 19:08
  • 1
    how about `select varCharColumn from myTable where varCharColumn in ('-1', '');` – jaczes Sep 06 '13 at 19:10
  • @jaczes: Yes, that is what I have now and works. It was just tough to track that down, and I'd like to know how the casting works. – Briguy37 Sep 06 '13 at 19:17

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    The [documentation](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in) says otherwise: "If all values are constants, they are evaluated according to the type of ***`expr`***" (in this case, the type of the `varCharColumn` column - which one presumes is a string, in which case all values should be compared as strings and `-1` would cast to `'-1'` implicitly). However, the documentation does say: "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." – eggyal Sep 06 '13 at 19:24
  • Yet the OP's behaviour is not reproduced on [sqlfiddle](http://sqlfiddle.com/#!2/5b29e/1), which is consistent with the manual's warning against combining quoted & unquoted values ("Mixing types may therefore lead to inconsistent results.") and inconsistent with a mere misunderstanding of type conversion. – eggyal Sep 06 '13 at 19:33
  • Thanks Gordon, this does explain the behavior. I did `select count(*) .. where .. in (0)`, and got the same count as `.. in (-1, '')`, so that indeed seems to be what is going on here. – Briguy37 Sep 06 '13 at 19:35
  • Note: For `.. val in (-1, 'B')`, I get true in my current version of MySQL, and I'm getting different results than eggyal in his sqlfiddle, so this looks to be a version-dependent issue. I decided to go with his answer though yours seems to explain what is going on in my version as it will immediately warn future developers against doing this. – Briguy37 Sep 06 '13 at 20:04
  • @Briguy37: I don't think it's "version dependent". Mixing types leads to "inconsistent results", as stated in the documentation (and quoted in my answer). – eggyal Sep 06 '13 at 20:15
  • You said `The first element says "this is a list of integers", so the second value is converted to an integer. And '' becomes 0.`. However, this is not true; You get the same results for `IN('', -1);` where the first element says "this is a list of strings". – HappyDog Apr 03 '20 at 10:00
2

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');
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Thanks for the answer, I appreciate it and will not mix quotes and non-quotes IN the future :) As Gordon answered my direct question, however, I am accepting his answer for this one. – Briguy37 Sep 06 '13 at 19:38
  • @Briguy37: I beg to differ; as explained in my comments to Gordon's answer, I don't believe his analysis is correct. – eggyal Sep 06 '13 at 19:42
  • Yes, the functionality of my version of MySQL (5.0.45-log) clearly does not align with the current documentation. Unfortunately the earliest version SQL Fiddle goes to for testing is MySQL 5.1.61, and I could not reproduce my issue on that either. Thus, it looks like either this functionality occurs on some range of versions before that or it is some local configuration on my database that is causing it. – Briguy37 Sep 06 '13 at 20:03
  • @Briguy37: I disagree - the functionality of your version aligns with current (and past) documentation perfectly: you are observing precisely the "inconsistent results" about which the manual forewarns. – eggyal Sep 06 '13 at 20:15
  • My comment was in reference to "If all values are constants, they are evaluated according to the type of expr and sorted." which seemed to either conflict with my results or the results from your fiddle depending on how MySQL's expr rules work. – Briguy37 Sep 06 '13 at 21:12