Description:
MySQL produces an unexpected result when evaluating an IN() expression containing both NULL and a quoted numeric value.
According to the MySQL documentation, IN() returns NULL when no matching value is found and at least one expression in the IN() list is NULL. The documentation also states that comparison with NULL using = produces NULL.
However, the following query returns 1:
SELECT 0 IN (NULL, '0.5');
Observed result:
1
There is no value in the list that is equal to 0:
0 = NULL -> NULL
0 = '0.5' -> 0
Therefore, the corresponding explicit disjunction returns NULL:
SELECT (0 = NULL) OR (0 = '0.5');
Observed result:
NULL
This is inconsistent with the documented IN() semantics. In particular, the presence of NULL in the IN() list should cause the result to be NULL when no non-NULL list element matches.
The discrepancy can also be viewed as an inconsistency between the direct IN() evaluation and its element-wise comparison semantics:
SELECT 0 IN (NULL, '0.5'); -- 1
SELECT (0 = NULL) OR (0 = '0.5'); -- NULL
The issue appears to involve the interaction between IN() list type coercion, the quoted numeric value '0.5', and NULLpropagation.
The MySQL documentation specifically warns that quoted and unquoted values should not be mixed in an IN() list because their comparison rules differ, and notes that implicit type conversion may produce nonintuitive results. However, regardless of the conversion applied to '0.5', the documented NULL behavior requires IN() to return NULL when there is no match and the list contains NULL.
How to repeat:
SELECT 0 IN (NULL, '0.5');
SELECT (0 = NULL) OR (0 = '0.5');
Expected Result
The first query should return:
NULL
because:
0 = NULL -> NULL
0 = '0.5' -> 0
and there is no matching non-NULL value in the IN() list, while the list contains NULL.
The second query returns the expected three-valued-logic result:
NULL
Actual Result
SELECT 0 IN (NULL, '0.5');
-- 1
SELECT (0 = NULL) OR (0 = '0.5');
-- NULL
Suggested fix:
Reference
MySQL documentation for IN() states that IN() returns NULL when no match is found and one of the expressions in the list is NULL.
MySQL documentation also states that comparisons involving NULL using = return NULL.
