Why are strings matched to a primary key when it is set to zero (MySQL)? [duplicate]

Question

I have encountered a strange behaviour in MySQL that I cannot understand. I have a table where I have a row where the primary key is 0 (zero). The strange thing is that any string seems to match that row.

The SQL commands below creates a simple table with columns id and text. Then I add a simple row to the table, and modifies the id to be zero.

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    id INT NOT NULL AUTO_INCREMENT,
    text VARCHAR(45) NOT NULL,
PRIMARY KEY (id));
INSERT INTO test (text) VALUES ("Hello World!");
UPDATE test SET id = 0 WHERE (id = 1);

After these commands the table looks like this:

id	text
0	Hello world!

Now if I run the following command I expect an empty result, but the row with id=0 is returned. This won't happen if id = 1:

SELECT * FROM test WHERE id = "test";

Returns the following unexpected result:

id	text
0	Hello world!

Is this a bug, or is there some kind of explaination that I get a row with id=0? If I change it back to id=1, then an empty result is return as expected

Answer 1

When you compare "test" to id int MYSQL converts "test" to int, with a value of 0.

select cast('test' as unsigned)

Gives 0

Answer 2

select cast('test' as int), cast('aaa' as int)

+---------------------+--------------------+
| cast('test' as int) | cast('aaa' as int) |
+---------------------+--------------------+
|                   0 |                  0 |
+---------------------+--------------------+
1 row in set, 2 warnings (0.014 sec)

enough said

Also read https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html