| Bug #42241 | comparing non-numeric varchar fields to the number zero yields true | ||
|---|---|---|---|
| Submitted: | 21 Jan 2009 15:14 | Modified: | 21 Jan 2009 15:42 |
| Reporter: | Edam | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.0.51a-3ubuntu5.4 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[21 Jan 2009 15:42]
Valeriy Kravchuk
Current behaviour: mysql> select 0='abc'; +---------+ | 0='abc' | +---------+ | 1 | +---------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1292 Message: Truncated incorrect DOUBLE value: 'abc' 1 row in set (0.03 sec) is clearly described in the manual, http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html. If you ask for some other behaviour this will be a feature request that can be implemented in MySQL 6.1 or later.
Description: Comparing a VARCHAR field to the number 0 results in true where the value in the VARCHAR column is not convertiable to an integer. For example, SELECT * FROM some_table WHERE some_varchar_column = 0; returns all entries where some_varchar_column is not convertible to a number, or is the string "0". I assume this is because the comparison converts the varchar to an int beforehand, which results in the value 0. It doesn't seem obvious to me why the conversion is done this way around. The 0 could just have easily been converted to a string and the comparison done with strings. Is this part of the SQL specs or something? Also, since a string can represent all integers, but integers cannot represent all strings, doing the comparison with strings is arguably better - you don't risk the loss information in converting the string to an int. In the above example, if the comparison were done with strings, only entries where some_varchar_column was the string "0" would be returned, which is surely what the issuer of the SQL statement would have wanted, isn't it? How to repeat: /* create table and populate */ CREATE TABLE foo ( foo_id INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY, vcc VARCHAR(45) NOT NULL ); INSERT INTO foo VALUES( NULL, 'hello' ); INSERT INTO foo VALUES( NULL, '3' ); INSERT INTO foo VALUES( NULL, 'bye' ); INSERT INTO foo VALUES( NULL, '88' ); INSERT INTO foo VALUES( NULL, 'fish' ); INSERT INTO foo VALUES( NULL, '0' ); INSERT INTO foo VALUES( NULL, '6' ); /* this returns 4 rows! */ SELECT * FROM foo WHERE vcc = 0;