MySQL Bugs: #52155: Match of varchar column to a numeric zero returns all rows

Bug #52155	Match of varchar column to a numeric zero returns all rows
Submitted:	17 Mar 2010 18:51	Modified:	1 Sep 2011 18:24
Reporter:	Charles Ayres	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.0.20a	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	"type conversion"

Description:
A 'where' clause that matches a varchar column to the number zero appears to behave as if the zero was a wildcard.  The statement

   select * from mytable where varcharcol = 0;

returns all rows, no matter what the contents of the varchar column being referenced.  If you enclose the zero in quotes, the clause behaves as expected, so the problem seems to be in the type conversion.  This occurs with both myisam and innoDB tables - don't know about other engines.

Unfortunately, this bug was discovered through the malfunction of a 'delete' statement, which emptied the table.  :(

How to repeat:
Here is a session log which illustrates the problem:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2116 to server version: 5.0.20a-standard-log

mysql> desc room;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| room_id     | int(11)       | NO   | PRI |         | auto_increment |
| address     | varchar(30)   | NO   |     |         |                |
| description | varchar(1000) | YES  |     |         |                |
| capacity    | int(11)       | YES  |     |         |                |
| picture     | varchar(100)  | YES  |     |         |                |
| sched_viw   | varchar(100)  | YES  |     |         |                |
| sched_xen   | varchar(50)   | YES  |     |         |                |
+-------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> select room_id, address from room;
+---------+-------------+
| room_id | address     |
+---------+-------------+
| 1       | Rosenau 147 |
| 2       | Rosenau 167 |
| 3       | MHRC 0033   |
| 4       | MHRC 1304   |
+---------+-------------+
4 rows in set (0.00 sec)

mysql> select room_id, address from room where address = 0;
+---------+-------------+
| room_id | address     |
+---------+-------------+
| 1       | Rosenau 147 |
| 2       | Rosenau 167 |
| 3       | MHRC 0033   |
| 4       | MHRC 1304   |
+---------+-------------+
4 rows in set (0.00 sec)

mysql> select room_id, address from room where address = '0';
Empty set (0.00 sec)

The second 'select' statement passing an unquoted zero should have returned no rows like the third 'select' statement.  However it returned all 4 rows.  

Suggested fix:
None known, other than to make sure string literals are quoted.

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. Please, always compare numbers to numbers and strings to strings if you want to prevent undesired results.

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

Many thanks for the resolution. You're right, it's documented, although it still seems to me a rather non-intuitive and even dangerous way of handling it.

It may not be a bug, but Charle's assessment as a dangerous way to handle it is spot on. I'm pretty sure other RDBMS attempt to convert the 0 to a string '0' and then search the table for a match, rather than trying to convert the table strings to a float. I was bit with this when an external system submitted a 0 instead of a string and the lookup succeeded when it should have failed.

Thanks Tim - seems to me there are 3 preferable ways this could be handled:

a) as you suggest, convert the comparison expression to the type of the column 
   (if possible - else return an error);

b) Convert the column values to the type of the comparison expression, but
   return an error and rollback the transaction at the first unsuccessful
   conversion attempt, as does Oracle:
   SQL> select empno, ename from emp where ename = 0;
   ERROR at line 1:
   ORA-01722: invalid number
   
c) Convert the column values to the type of the comparison expression as it
   does now, but treat unsuccessful conversions as nulls rather than zeros.
   That way you shouldn't get a match where you really don't want one.

Thanks,
Charles

I'd vote for 'a' as the most intuitive (my expectation), with any of them being far superior to the current MySQL implementation. It seems like 'a' would be faster as well, with only a single conversion required. I was rather incredulous when my test returned all the rows of the table, once I figured out what was happening...seems like a double conversion (both values to a third type) is asking for trouble.

That being said, I just confirmed that MSSQL 2008R2 tries to convert the column value to the literal (as in 'b', like your Oracle example):

select * from db.dbo.testtable where value = 0;
Conversion failed when converting the nvarchar value 'red' to data type int.

The more I think about that, the less intuitive I find it.

Tim