3

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?

Wunsz
  • 165
  • 3
  • 7
  • 2
    This begs the question: Why are you comparing a numeric column to a set of character based values? – Arth Jan 29 '16 at 14:48
  • This questions seems to be a duplicate of http://stackoverflow.com/questions/23929344/ignore-type-casting-in – Alessio Cantarella Jan 29 '16 at 14:49
  • It is used as part of general combo search field and searches through many columns like name, last name and some external ID which happend to be a 0. Ps. Indeed a duplicate though I haven't found it. – Wunsz Jan 29 '16 at 17:48

2 Answers2

5

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
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • 1
    And to answer the question you didn't ask: "how do I fix this?"... it depends on what behavior you want. If you want to perform a string comparison, then cast/convert `SomeOtherColumn` to character datatype. There's several ways to do that. You could use a `CAST()` or `CONVERT()` function. e.g. **`WHERE CONVERT(SomeOtherColumn,CHAR) IN ('Some', 'Set')`** – spencer7593 Jan 29 '16 at 16:13
  • I fixed it by casting to CHAR in the beginning bit I was wandering why it works that way. Thanks for the answer! – Wunsz Jan 29 '16 at 17:47
0

You should convert 0 to varchar:

select '0' in ('Some', 'Set') will evaluate to 0.

Alessio Cantarella
  • 5,077
  • 3
  • 27
  • 34