MySQL Bugs: #61229: select on value 0 return the complete table

Bug #61229	select on value 0 return the complete table
Submitted:	19 May 2011 14:30	Modified:	25 Jun 2013 0:52
Reporter:	Patrick Savalle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.1.49	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	SELECT, syntax, value

Description:
This query returns the COMPLETE table, even while there is no matching value, the column has unique values and the syntax of the query is not correct. The type of the column is 'donator VARCHAR(30) NOT NULL'

SELECT * FROM mbr_donation WHERE donator=0

We tested in on 5.1.49 on Linux, on 5.0.7dev Windows, on 5.0.8dev MacOS.

We use InnoDB-engine.

How to repeat:
Just repeat on any VARCHAR column in your InnoDB-database.

Suggested fix:
Type-check.

if anything, this is a feature request to make SELECT statements adhere to strict type checks, and show a warning.  Here, is the basis of the problem.
The varchar is being casted to a numeric value of 0, which matches.

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

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

This is not a bug. Column value is, indeed, converted to double, results in 0 (as it is not a number), and then comparison returns TRUE for every row (that does not reperesent number).

Our manual clearly describes this at http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html:

"...
- In all other cases, the arguments are compared as floating-point (real) numbers."

But this is a valid feature request to give warning, at least in strict mode, as in Shane's example.

Hi Patrick,

Thank you for the feature request, and for your help in making MySQL even better!

I'm closing this feature request for now, as the default behavior has been changed according to what's described in this feature request.

I'm closing this as implemented, as I get a warning by default in 5.7.1 with a default configuration:

mysql> create table vctest (name varchar(50));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into vctest values ("Matt"), ("Corbin"), ("Lily");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from vctest where name=0;
+--------+
| name   |
+--------+
| Matt   |
| Corbin |
| Lily   |
+--------+
3 rows in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Matt'   |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Corbin' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Lily'   |
+---------+------+--------------------------------------------+
3 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.1-m11 |
+-----------+
1 row in set (0.00 sec)

This is because STRICT_TRANS_TABLE is now a default sql_mode.

mysql> show session variables like "sql_mode";
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

If you feel that I'm mistaken here, please let me know.

Thanks again!