Why would a query with an integer parameter on string column return some rows?

Stooshie · 10-28-2010, 05:20 PM

In the query:

Code:

SELECT string_column WHERE  string_column = 0;
# notice it is 0 not "0" or '0'

some rows are being returned.

I would expect all, none or an error, but not some rows, but I get:

Code:

aa4443c-121b142b48d--7fb1
aa4443c-121b142b48d--7faf
aa4443c-121b142b48d--7fae
B2A0682-123DCFC39AA--7E19
B2609A6-122CB3D8A7A--7EC3
.
.
.
B2609A6-122CB3D8A7A--7BAA
-C9673C4-12351158721--7E51
-C9673C4-12351158721--7E51
-C9673C4-12351158721--7E51

(The values returned are correct).

93 rows are returned dispersed throughout the table and there are 903 rows in the table

Is there any reason for this?

Thanks,

Andrew.

Old Pedant · 10-28-2010, 07:51 PM

I think the reason is that MySQL is BRAIN DEAD!!!

No other DB would do this.

But look here:

Code:

mysql> select n, n=0 from foo;
+----------------------------+------+
| n                          | n=0  |
+----------------------------+------+
| adam                       |    1 |
| bob                        |    1 |
| xyz                        |    1 |
| 11111111111111111111111111 |    0 |
| 000000000000000000         |    1 |
| !                          |    1 |
| @                          |    1 |
| #                          |    1 |
| a                          |    1 |
| A                          |    1 |
| 0                          |    1 |
| 1                          |    0 |
| 10                         |    0 |
+----------------------------+------+
13 rows in set (0.00 sec)

And then also look here:

Code:

mysql> select n,n=0,n=1,n=2,n=3,n=4 from foo where n in ('adam','0','1');
+------+------+------+------+------+------+
| n    | n=0  | n=1  | n=2  | n=3  | n=4  |
+------+------+------+------+------+------+
| adam |    1 |    0 |    0 |    0 |    0 |
| 0    |    1 |    0 |    0 |    0 |    0 |
| 1    |    0 |    1 |    0 |    0 |    0 |
+------+------+------+------+------+------+

So clearly the rule is this: "If the value is convertible to a number, then the numeric value is tested. But all values not convertible to a number are equal to numeric zero but not to any other numeric value."

What an UTTERLY perverted definition!!

I would LOVE to hear somebody on the MySQL team justify this definition.

Stooshie · 10-29-2010, 10:04 AM

Thanks Pedant for doing that testing. At least it makes it clear what MySQL is actually doing.

Playing devil's advocate, I suppose the thinking would be that it will only return 0 when there is, literally, nothing in there (e.g. 0, "0", "" and null)?

Well maybe!

Old Pedant · 10-29-2010, 06:52 PM

Of course, other databases wouldn't even allow the query. They would give you Type Mismatch Error or something akin. Only MySQL is so sloppy that it allows comparing numbers to strings. Ugh.