Search code examples
mysqlsqlwhere-clauseclause

MySQL 0 IN ("Some", "Set")

I've got a MySQL query which uses IN clause.

SELECT SomeColumn
FROM SomeTable
WHERE SomeOtherColumn IN ('Some', 'Set')

However, I've stumbled upon a case, where SomeOtherColumn has a value of 0, and MySQL evaluates the where clause as TRUE:

SELECT 0 IN ('Some', 'Set')

Evaluates to

1 (TRUE)

MySQL version 5.5.46. Anyone has any idea why it happens so?

Solution

  • The 0 is interpreted as a numeric literal.

    The values in the parens are also evaluated as numeric literals. And both of the values in the IN list evaluate to a numeric value of 0.

    (Unlike other databases, MySQL is somewhat generous in the implicit conversion to numeric. Rather than throwing an "Invalid number" error, it does the best it can, and returns a result.)

    Compare to:

    Evaluate as string literal and the comparison:

     SELECT '0' IN ('Some','Set')

    Evaluate as numeric literals:

     SELECT 0 IN ('1foo','2bar')

    Test conversion to numeric:

     SELECT 'Some' + 0, 'Set' + 0

 SELECT '1foo' + 0, '2bar' + 0