CODEWITHSUNDEEP
mysqlsqlwhere-clausewhere-in
Basti
Basti

Reputation: 731

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', '');

Upvotes: 0

Views: 46

Answers (2)

Fabricator
Fabricator

Reputation: 12782

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.

Upvotes: 3

soft87
soft87

Reputation: 511

The optimizer brings the data to the same type. When translating a string to decimal, we get zero. It is necessary to predefine or convert data types.

Upvotes: 0

Related Questions