MySQL – Searching a varchar with a numeric zero

So, I came across an interesting “feature” in MySQL today. One that I had never seen in all my years of developing.

[code]
mysql> desc room;
+————-+—————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+—————+——+—–+———+—————-+
| opt_id | int(11) | NO | PRI | | auto_increment |
| address | varchar(30) | NO | | | |
+————-+—————+——+—–+———+—————-+
[/code]

What would you expect to get from this query?

[code]SELECT * FROM options;[/code]

Of course, you would get all records in the table above.

[code]
+———+————-+
| opt_id | address |
+———+————-+
| 1 | 10 Smith St |
| 2 | 25 Brick Ln |
| 3 | 18 Melfor Pl|
+———+————-+
[/code]

OK. So what would you expect to have returned from this query?

[code]SELECT * FROM options WHERE address = ‘0’[/code]

Nothing right? There is no record in the address column that is equal to the string representation of zero. And you would be right.

What if you remove the quotes around that zero?

[code]SELECT * FROM options WHERE address = 0[/code]

Logic would suggest that you could expect the same, since there is also no record in the address field that equals the numeric representation of zero. But here, is where we found an oddity:

[code]
+———+————-+
| opt_id | address |
+———+————-+
| 1 | 10 Smith St |
| 2 | 25 Brick Ln |
| 3 | 18 Melfor Pl|
+———+————-+
[/code]

It appears that when you search a varchar field using a numerical zero, MySQL (yes, even the current version) treats the zero as a wildcard.

This has been reported over on the MySQL bug tracker since March 2010, and the ticket has been closed as “Not a Bug.”

Explanation from Valeriy Kravchuk:
[quote_simple]Sorry, but this is known and documented feature of MySQL. When you compare numbers to strings, they are compared as float numbers. Any string that does NOT start with a digit is implicitly converted to number 0. Hence the results you get.

Read http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html for the details.[/quote_simple]

Not really an issue if you’re being a good person and binding parameters to SQL statements. But if you simply must have a query like this hard coded, just be sure to quote any search value used on a varchar field.

Leave a Reply

Your email address will not be published. Required fields are marked *