MySQL Bugs: #36111: SQL Query returns wrong reults

Bug #36111	SQL Query returns wrong reults
Submitted:	16 Apr 2008 5:48	Modified:	1 May 2008 3:46
Reporter:	P D	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.41	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	SQL

Description:
Attaching a file which will create a simple table and insert a few rows. Once this table is created with some data just ran the query select * from testtable where component_type = 'server' and surprisingly it returns a few rows which have a value as 0. The field type for component_type is tinyint and it has all integer values but still the where clause is analaysed and it returns some rows. Ideally it should either give a datatype mismatch or should not return any rows.

How to repeat:
Attaching a file which will create a simple table and insert a few rows. Once this table is created with some data just ran the query select * from testtable where component_type = 'server' and surprisingly it returns a few rows which have a value as 0. The field type for component_type is tinyint and it has all integer values but still the where clause is analaysed and it returns some rows. Ideally it should either give a datatype mismatch or should not return any rows.

This file creates a table and inserts a few records

Attachment: mysql5schema.sql (application/octet-stream, text), 737 bytes.

Also on 5.1.24 this happens!

backquoting and replacing the string does not change ..
select * from testtable where `component_type` = 'peter' -- same result

alter table `test`.`testtable` change `component_type` `blabla` tinyint(4) DEFAULT '0' NOT NULL;
select * from testtable where `blabla` = 'peter' -- same result

alter table `test`.`testtable` change `blabla` `blabla` smallint(4) /* or bigint */ DEFAULT '0' NOT NULL;
select * from testtable where `blabla` = 'peter' -- same result

alter table `test`.`testtable` change `blabla` `blabla` char(4) DEFAULT '0' NOT NULL;
select * from testtable where `blabla` = 'peter'  --empty set

Obviously a non-numerical string default to 0 (zero) in a comparison with a numerical column type.  I think I saw it documented like this ... (internally a FLOAT transition takes place) 

.. Anyway Miguel has opened this now I see, so let him detail!

Peter
(non-mysql person)

this is somewhat related:
http://www.bitbybit.dk/carsten/blog/?p=122

Thank you for the bug report. Could you please read:

http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

" The following rules describe how conversion occurs for comparison operations:

    <cut>

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

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

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