MySQL Bugs: #15257: comparing any non numeric string to 0 leads to TRUE (1)

Bug #15257	comparing any non numeric string to 0 leads to TRUE (1)
Submitted:	26 Nov 2005 8:54	Modified:	28 Nov 2005 10:00
Reporter:	Olivier Clavel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Linux (Fedora core4 x86_64)
Assigned to:		CPU Architecture:	Any

Description:
Accidendtly making an error in a query today, I came accross the following behaviour which seems a bug to me. Comparing any string value to 0 leads to true (1). Since (thanks Xgc from #mysql for the formulation) "it's common for applications to quote values, even integral or numeric values, badly formed quoted integral values shouldn't match valid integral values"

How to repeat:
select 0='';
select 0='     ';
select 0='blabla';

create table bla(id int unsigned not null);
insert into bla values (0), (0), (1), (2), (3);
select * from bla where id='anystring';

Suggested fix:
All of the above tests should return 0 and the select * should return no rows

Thanks for your bug report.  I was able to reproduce this under FC4 (X86-32) using a recent 5.0bk clone

It's not true that comparing any string to 0 leads to true:

mysql> select 0 = '0';
+---------+
| 0 = '0' |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select 0 = '1';
+---------+
| 0 = '1' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

The results shown in this bug report are in accordance
with the comparison rules documented here:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

It's not true that comparing any string to 0 leads to true:

mysql> select 0 = '0';
+---------+
| 0 = '0' |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select 0 = '1';
+---------+
| 0 = '1' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

The results shown in this bug report are in accordance
with the comparison rules documented here:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

Ok Paul. I agree I was a little in a hurry and that the subject of the bug is not precise enough. I should have stated "any string that is not castable to a numeric value". Meanwhile, I still think this behaviour is wrong and should be changed.

Let's take an example: a web application with a form where I ask a user to enter an invoice numeric identifier so that I can select it in a table and send it back to the browser for edition. The number arrives on my web server as a string. To make sure I don't get any errors from mysql in case the user miss-typed the number (and accidently typed in a non numeric character or even did not type anything at all), I will quote the value.

Now, if there is effectively a non numeric character in my string or if it is the empty string, I expect mysql to return no result and not the row with identifier 0 in case it exists.

As Paul stated in his comment, comparing 0 to a string that is castable to a numeric value words as expected...

It sounds as though the situation that you are describing regarding
input taken from a web form is that of inserting data into a table
(not compariing it to anything as described in the original report).
If that's the case, I suggest that you can get the behavior your
want by enabling strict or traditional SQL mode.  Here's an example
that shows the difference without and with traditional mode:

mysql> create table t (i int);
Query OK, 0 rows affected (0.41 sec)

mysql> insert into t values(6);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values('6');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values('6x');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> set sql_mode = 'traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values('6');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values('6x');
ERROR 1265 (01000): Data truncated for column 'i' at row 1

Note that without traditional mode, '6x' results only in a warning.
With traditional mode, you get an error. I think this is what you
are looking for.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

closing, as explained by Paul.