When typing an SQL query for use with MySQL I accidentally mistyped a hyphen/minus sign (-) in place of an equals sign (=). This resulted in the query being:
select id, field from table where id - 9;
instead of the intended:
select id, field from table where id = 9;
However, instead of giving me the expected syntax error it gave me a result set. What it gave me seems to be the inverse of what I intended as if i had issued:
select id, field from table where id <> 9;
The id column is of type int and the table is using the innodb storage engine. If I try the query against a table with an id column of type unsigned bigint I get an error though not the syntax error I expected:
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range
I have seen this behaviour in both version 5.7 (on Ubuntu 18.04) and 8.0 (on Ubuntu 22.04) of MySQL and with STRICT_TRANS_TABLES set in the mode.
Can anyone tell me why id - number
seems to be interpreted as id <> number
rather than a syntax error before I potentially embarrass myself by reporting it to MySQL as a bug?
I've tried searching the web for similar issues but to no avail.
Regards, Nachtkinder
CodePudding user response:
MySQL treats the WHERE
clause as a number constant
0 Is FALSE
and every other number is TRUE
And when the WHERE
is TRUE
it returns all rows
ANd of course when FALSE
nothing
see the example below
CREATE TABLE table1 (id int, field int)
INSERT INTO table1 VALUES (1,1),(7,7) , (10,10)
select id, field from table1 where id - 9;
id | field -: | ----: 1 | 1 7 | 7 10 | 10
select id, field from table1 where 1;
id | field -: | ----: 1 | 1 7 | 7 10 | 10
select id, field from table1 where 0;
id | field -: | ----:
db<>fiddle here