Search code examples
mysqlsqlwhere-clausewhere-in

WHERE a OR b IN (x) does not match, but returns rows

My table looks like this:

| a  | ts_9 | ts_11 |
|----|------|-------|
| yx | 0    |       |
| xy | 0    |       |

And for some reason the rows get returned when I call:

SELECT * FROM things WHERE ts_9 IN ("asdewdwedewd") OR ts_11 IN ("asdewdwedewd")

Why does that happen?

Feel free to recreate it in your db with copy paste:

CREATE TABLE `things` (
  `a` char(12) NOT NULL DEFAULT '',
  `ts_9` decimal(2,0) NOT NULL,
  `ts_11` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`a`),
  UNIQUE KEY `a` (`a`) );

INSERT INTO `things` (`a`, `ts_9`, `ts_11`) VALUES ('yx', '0', ''), ('xy', '0', '');
Solution

  • Because ts_9 IN ("asdewdwedewd") evaluated to true. More specifically, ts_9 has value of integer 0. According to the documentation

    If all values are constants, they are evaluated according to the type of expr

    In other words, "asdewdwedewd" will be treated as an int. When mysql convert it to an int cast("asdewdwedewd" as signed integer), we get 0. Hence 0 in (0) evaluates to true.