Mixing quoted and unquoted values in IN() condition - MySQL quirk or general issue?

It depends what you consider "non-intuitive". This returns false:

'00' in ('0', '01')

However, this returns true:

'00' in (0, '01')

I think the next few lines give an unintuitive example without mixing :

mysql> SELECT 'a' IN (0), 0 IN ('b');
        -> 1, 1

That you can extend :

SELECT 'a' IN (0, 1, '2'), 'a' IN ('0', '1', '2');
-> 1, 0
SELECT 0 IN (0.0, 'b'), 0 IN ('0.0', 'b');
-> 1, 1

Also there is this other 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 ('');

Everything is cast into float, most likely, according to this link :

[...] In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes places as a comparison of floating-point numbers.

And string are cast as 0.0, unless they start by digits. Also from the same link, there could be problems with floating point accuracy, and queries not using index because the type is not right (it must cast everything to float, so no index usage, I guess).

I think you might get something similar but not the same with every DBMS because you have to cast things to compare them. It might not be the exact same issue in SQL Server, because the data type precedence is not the same, but you should compare data of the same data type. According to this link that gives data type precedence for SQL Server :

1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)

int and string would be cast to int (not float) for a SQL server DBMS.

Running some simple tests seems that the control between data types is done correctly, despite what is written in the MySQL manual.

SELECT 0 IN ('0','00',0,00); -> TRUE
SELECT 0 IN ('0','01',1,01); -> TRUE
SELECT 0 IN ('1','00',1,10); -> TRUE
SELECT 0 IN ('11','10',0,10); -> TRUE
SELECT 0 IN ('1','01',1,00); -> TRUE
SELECT '0' IN ('1','01',1,00); -> TRUE
SELECT '0' IN ('0','00',0,00); -> TRUE
SELECT '0' IN ('0','01',1,01); -> TRUE
SELECT '0' IN ('1','00',1,10); -> FALSE
SELECT '0' IN ('11','10',0,10); -> TRUE
SELECT '1' IN ('11','10',1,10); -> TRUE
SELECT '15.32' IN ('11','10',1,15.32); -> TRUE
SELECT 13.12 IN ('11','10',1,13.12); -> TRUE
SELECT 00 IN ('11','00',1,13.12); -> TRUE
SELECT '00' IN ('11',00,1,13.12); -> TRUE
SELECT '00.0' IN ('11',00.0,1,13.12); -> TRUE
SELECT '00.00' IN ('11',0,1,13.12); -> TRUE
SELECT '00.01' IN ('11',0.01,1,13.12); -> TRUE

The above results can be seen in this SQLFiddle

But the above tests are not even close to testing all the different data types of MySQL.

In addition we should simply just think in what cases we would use the IN () operator.

MySQL writes that mixed data types offer surprises on results sometimes, but then again is it actually needed to have different data types inside IN ()?

In short no. What will be checked against the values inside the parenthesis will be a table column having specific data type.

For example doesn't comparing a column of TEXT against IN ('Hello','World',13) seems odd? I know that one could oppose the fact that in the column having data type TEXT you may have numerical values. Good, then just write the above like this IN ('Hello','World','13') since we were speaking about a TEXT column.

In case that we did not know the data type or if somehow the data type is dynamic and could some times change, then we should convert that field to the data type that we expect the majority of results would be.

1. Why does this cause problems in MySQL?

The example below should be able to show you the inconsistency about using IN across quoted (x='1a') and unquoted types (x=1). Note for the same value of x = 1, the same IN expression yields 0 in Query 1, but yields 1 in Query 2.

SELECT
  x, x IN ('1b','a1')
FROM
  (
    select '1a' as x
    union all select 1
  ) q1;

SELECT
  x, x IN ('1b','a1')
FROM
  (
    select 1 as x
  ) q1;

Results:

Query 1:
'1a': 0
1: 0

Query 2:
1: 1

For far I cannot observe inconsistency if I only alter the list inside IN. But I observed that pattern is like:

expr IN (...array of values)

For expr with string, against string values: compare as string
For expr without string, against string values: compare as number
For expr with string, against numeric values: compare as number
For expr without string, against numeric values: compare as number

2. Is this a MySQL-specific quirk or does this apply to other database systems?

Case by case. For MSSQL I tell you no because when comparing string with number, they give you an error message like: Conversion failed when converting the varchar value '1a' to data type int.

1. Why does this cause problems in MySQL?
Engine needs to know how it will make comparisons.
If you compare column with integers, the column integer value will be compared with the IN list. If IN list items are strings, comparison will differ.
https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

2. Is this a MySQL-specific quirk or does this apply to other database systems?
It is not MYSQL specific. For performance reasons (indexing) it is always better not to make casting.

Why does this cause problems in MySQL?

It's not a bug, it's a feature.

Basically it's about how the database handles the field comparison. In particular, MySQL automatically converts the string value to a numeric value when comparing the numeric with string values. Since MySQL is written in C++ , somewhere in the code base, they should cast the string value to double prior to field comparison.

There is nothing special about the IN clause, I think. In the MySQL source code, I saw comments similar to this one:

    `WHERE a IN (b, c)` can also be rewritten as `WHERE a = b OR a = c`

Which makes sense and IN is (probably) treated the same way in code base. So based on this, if we have let's say something like this:

... WHERE '04.2' IN ('0', 4.2);

Which means '04.2' = '0' OR '04.2' = 4.2, and will return true, because, in C/C++:

"04.2" =  "0"  // string value comparison -> false
cast_as_double("04.2") = 4.2 // double value comparison -> true

The same applies for other cases, which resolve as true, e.g. 42 IN ('0042', 0), '3.00' IN (3, '1'), 0 IN (3, '0.00') etc.

Is this a MySQL-specific quirk or does this apply to other database systems?

This seems to be the case with other databases as well. If you like, you can test them online

• MySQL: https://www.db-fiddle.com
• PostgreSQL: https://www.db-fiddle.com
• MS SQL Server 2017: http://sqlfiddle.com/#!18/ff6b8/12807

Whilst there have been a lot of lot of answers and comments that provide examples of 'unintuitive' behaviour, most of these examples seem to be explained by the standard casting rules. In other words, the results were entirely consistent with what would be returned from SELECT A = B; for the given A and B.

"Because casting" doesn't seem like a particularly satisfying explanation for the paragraph I quoted in the question. That paragraph comes after a number of paragraphs explaining how type conversion affects the IN() statement, so it seems somewhat repetitive and redundant if that is all it's referring to.

My interpretation of the quoted paragraph is that it is an explicit statement that a IN(b, c) may give different results to a = b OR a = c in situations where b and c are quoted differently.

I was therefore looking to find an example where the result couldn't be explained by the usual casting rules.

I think the reason that we haven't seen a good example yet is because most answers focussed on comparing numbers, in string and non-string representations. However, by basing the test around string values instead, I have managed to construct a non-intuitive example that is not explained by simple type conversion rules and which is not equivalent to the individual comparisons ORed together; the comparison between 'test' and 23 gives different results depending on what other values are in the IN() list:

SELECT 'test' IN('fish');       --> 0
SELECT 'test' IN(23);           --> 0
SELECT 'test' IN('fish', 23);   --> 1 !!!

I have yet to come up with a good explanation about what is happening here - is there some rule being followed, or is it just a MySQL quirk? I also haven't got an answer to the second question, as that somewhat depends on the reason for the behaviour (e.g. if it is defined by the standard or is an artefact of an obvious optimisation, vs. just being a MySQL-specific quirk) but I guess this could be figured out by running the above test on other RDBMSs.

Any comments to help flesh this out (or answers that cover the missing elements) will be appreciated - I will update this answer with any further details that I manage to deduce and don't plan on accepting any answer (including my own) until I understand what's going on a little bit better.