MySQL hyphen/minus character in place of equals in where clause gives unexpected result

user_8505429 0
Viewed 20

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

mysql sql operators
1 Answers

MySQL treats the WHEREclause as a number constant

0 Is FALSE and every other number is TRUE

And when the WHEREis 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

user_51935360
Related