Should querying for empty string match zero on a primary key column?

Question

I have this mysql query that has made me totally stumped all day:

SELECT * FROM `table` WHERE id = "";

I am surprised that this query returns a row where id is "0". Is this the expected behaviour? And if yes, how should I go about excluding this row? I need this query to return no rows when I pass the empty string.

Now id is a primary key column and auto_increment, if that's may help.

EDIT

It gets even more interesting. I have just run the query with an arbitrary string, like so:

SELECT * FROM `table` WHERE id = "xyz";

It still returns the row with the id of zero. It looks like the query is somehow casting my query parameter to INTEGER. This is new to me!

score 0 · Answer 1 · answered Nov 25 '15 at 07:05

0

It will return false

IF('', 'true', 'false')

or It will return false

IF('', 'true', 'false')

For MySQL meaning zero or '' indicates false

answered Nov 25 '15 at 07:05

Abhishek Sharma

6,689
1
14
20

score 0 · Accepted Answer · edited May 23 '17 at 12:23

0

The problem with you query is that "" or "xyz" is implicitly converted to 0. Try this to see for yourself:

SELECT CAST("" AS SIGNED), CAST("xyz" AS SIGNED)

You can also have a look at the manual to better understand implicit type conversion.

I would suggested checking the value of your parameter before issuing the query. If the user has given a non-numeric value as input then inform him/her accordingly.

In MySQL you can use a method suggested in this post to check if the parameter value is numeric:

SELECT * 
FROM `table` 
WHERE id = "" AND (CONCAT('', @param * 1) = @param);

where @param = "" or "xyz" or "123".

edited May 23 '17 at 12:23

Community

1
1

answered Nov 25 '15 at 07:16

Giorgos Betsos

71,379
9
63
98

Yep. I guess "implicit type conversion" is what's going on. I was interesting when I was just reminded that `SELECT "xyz" = 0;` yields `TRUE`.So, I think the problem was created by a combination of this, and the (unusual) presence of a "0" value on that INTEGER column. – Ifedi Okonkwo Nov 25 '15 at 08:01

score 0 · Answer 3 · answered Nov 25 '15 at 10:29

The "unexpected" behaviour is indeed the expected one, considering implicit type conversion as pointed out by @Giorgos. Deferring to that then, I believe the most straightforward means of achieving what I want in this query is:

SELECT * FROM `table` WHERE CAST(`id` AS CHAR)="";
SELECT * FROM `table` WHERE CAST(`id` AS CHAR)="xyz";
SELECT * FROM `table` WHERE CAST(`id` AS CHAR)="123";

This way, both sides of the argument are STRING, and apples are compared with apples.

That being said, I'm going to accept @Giogos's answer. It does point to the solution.

Should querying for empty string match zero on a primary key column?

3 Answers3