SQL IN query behavior-CodePudding

Executing the following SQL statement;

select '2312' in ('2312,254,2111') as result1, 2312 in ('2312,254,2111') as result2

I am getting the following result

 --------- --------- 
| result1 | result2 |
 --------- --------- 
|       0 |       1 |
 --------- ---------

I would expect the opposite result. Having result1 to be true and result2 to be false. Could someone explain why?

CodePudding user response：

Using IN() with a CSV-string as parameter is not supported. It should be IN ('2312','254','2111') instead of IN ('2312,254,2111')

The reason seems to be a implicit type conversion.

Look:

SELECT 2312 IN ('2312,254,2111') -- result: 1
SELECT 2312 IN ('254,2312,2111') -- result: 0 -- interesting
SELECT 2312 = '2312,254,2111'    -- result: 1 << see ??
SELECT 2312 = '254,2312,2111'    -- result: 0

Only the first number in the string is relevant. The rest is ignored due to the implicit type conversion.

Also,

SELECT '2312' in ('2312,254,2111') -- is false

because no type conversion happens here. '2312' does not equal the only string '2312,254,2111' in the value list and hence the IN() operator return false.

If you use a list of values for IN() instead of a CSV-string, everything works als expected:

SELECT
   2312  IN ('2312','254','2111')  -- result: 1
, '2312' IN ('2312','254','2111')  -- result: 1
,  254   IN ('2312','254','2111')  -- result: 1
, '254'  IN ('2312','254','2111')  -- result: 1
,  2312  IN (2312,254,2111)        -- result: 1
, '2312' IN (2312,254,2111)        -- result: 1
,  254   IN (2312,254,2111)        -- result: 1
, '254'  IN (2312,254,2111)        -- result: 1

From the manual:

Implicit type conversion may produce nonintuitive results:
mysql> SELECT 'a' IN (0), 0 IN ('b');
        -> 1, 1
In both cases, the comparison values are converted to floating-point values, yielding 0.0 in each case, and a comparison result of 1 (true).