MySQL Bugs: #62078: Inconsistent casting behavior between IN clause and straight equality

Bug #62078	Inconsistent casting behavior between IN clause and straight equality
Submitted:	3 Aug 2011 21:47	Modified:	5 Aug 2011 8:17
Reporter:	Mitch Lindgren	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.51a-community-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cast, equality, IN

Description:
When using the equality operator to query for an integer in a varchar field, MySQL implicitly casts the integer to a string, so the query works fine.

mysql> select * from gadget_required_gadgets where group_id = 1;
+----------+----------------------------------+-------+------------+---------------+
| group_id | instance_id                      | title | column_num | display_order |
+----------+----------------------------------+-------+------------+---------------+
| 1        | 8b357c4878a6102e8699000475c37287 | x     |          1 |             9 |
+----------+----------------------------------+-------+------------+---------------+
1 row in set (0.00 sec)

This makes sense and works as it should.  However, if you try to limit the results using an IN clause, you get unexpected behavior when mixing types:

mysql> SELECT * FROM gadget_required_gadgets WHERE instance_id = '315d8a6278a6102e8699000475c37287' AND group_id IN ('external', 1090452225);
+----------+----------------------------------+---------------------------+------------+---------------+
| group_id | instance_id                      | title                     | column_num | display_order |
+----------+----------------------------------+---------------------------+------------+---------------+
| fieldmen | 315d8a6278a6102e8699000475c37287 | ------------              |          1 |             3 |
+----------+----------------------------------+---------------------------+------------+---------------+
1 row in set, 1 warning (0.00 sec)

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

This does not make sense.  I would expect that MySQL would either implicitly cast the integer to a string as it does when using =, or throw an error, rather than simply dropping part of the WHERE clause and returning results which are not within the parameters of the query.

Quoting the integer in the IN clause (so as to turn it into a string) fixes this behavior, but casting should be consistent regardless of context.  More importantly, if MySQL is unable to properly interpret the query it should throw an error rather than modifying the query in unpredictable ways.

How to repeat:
1. Create a table which has a varchar field
2. Use an IN clause containing an int and a string to query the field
3. Receive unexpected results

(See description for more detail.)

Suggested fix:
MySQL should either:
- Cast integers in an IN clause to chars where applicable OR
- Generate an ERROR (rather than a warning) when IN is used with mixed types

I think this manual page, http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html, explains what happens:

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

But to be sure I need the results of SHOW CREATE TABLE for that gadget_required_gadgets table and the results of:

explain extanded select * from gadget_required_gadgets where group_id = 1;
show warnings\G
explain extended SELECT * FROM gadget_required_gadgets WHERE instance_id =
'315d8a6278a6102e8699000475c37287' AND group_id IN ('external', 1090452225);
show warnings\G

Here is the information you requested:

CREATE TABLE `gadget_required_gadgets` (
  `group_id` varchar(15) NOT NULL default '',
  `instance_id` char(32) NOT NULL default '',
  `title` varchar(64) NOT NULL default '',
  `column_num` int(2) NOT NULL default '0',
  `display_order` int(3) NOT NULL default '0',
  PRIMARY KEY  (`group_id`,`instance_id`),
  KEY `required_gadgets_fk_1` (`instance_id`),
  CONSTRAINT `required_gadgets_fk_1` FOREIGN KEY (`instance_id`) REFERENCES `gadget_instances` (`instance_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> explain extended select * from gadget_required_gadgets where group_id = 1;
+----+-------------+-------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table                   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | gadget_required_gadgets | ALL  | PRIMARY       | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+-------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `ops`.`gadget_required_gadgets`.`group_id` AS `group_id`,`ops`.`gadget_required_gadgets`.`instance_id` AS `instance_id`,`ops`.`gadget_required_gadgets`.`title` AS `title`,`ops`.`gadget_required_gadgets`.`column_num` AS `column_num`,`ops`.`gadget_required_gadgets`.`display_order` AS `display_order` from `ops`.`gadget_required_gadgets` where (`ops`.`gadget_required_gadgets`.`group_id` = 1)
1 row in set (0.00 sec)

mysql> explain extended SELECT * FROM gadget_required_gadgets WHERE instance_id = '315d8a6278a6102e8699000475c37287' AND group_id IN ('external', 1090452225);
+----+-------------+-------------------------+------+-------------------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table                   | type | possible_keys                 | key                   | key_len | ref   | rows | Extra       |
+----+-------------+-------------------------+------+-------------------------------+-----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | gadget_required_gadgets | ref  | PRIMARY,required_gadgets_fk_1 | required_gadgets_fk_1 | 96      | const |    1 | Using where |
+----+-------------+-------------------------+------+-------------------------------+-----------------------+---------+-------+------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'external'
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select `ops`.`gadget_required_gadgets`.`group_id` AS `group_id`,`ops`.`gadget_required_gadgets`.`instance_id` AS `instance_id`,`ops`.`gadget_required_gadgets`.`title` AS `title`,`ops`.`gadget_required_gadgets`.`column_num` AS `column_num`,`ops`.`gadget_required_gadgets`.`display_order` AS `display_order` from `ops`.`gadget_required_gadgets` where ((`ops`.`gadget_required_gadgets`.`instance_id` = _utf8'315d8a6278a6102e8699000475c37287') and (`ops`.`gadget_required_gadgets`.`group_id` in (_latin1'external',1090452225)))
2 rows in set (0.00 sec)

OK, this is easy to repeat even on recent 5.0.x versions:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `gadget_required_gadgets` (
    ->   `group_id` varchar(15) NOT NULL default '',
    ->   `instance_id` char(32) NOT NULL default '',
    ->   `title` varchar(64) NOT NULL default '',
    ->   `column_num` int(2) NOT NULL default '0',
    ->   `display_order` int(3) NOT NULL default '0',
    ->   PRIMARY KEY  (`group_id`,`instance_id`),
    ->   KEY `required_gadgets_fk_1` (`instance_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.88 sec)

mysql> insert into gadget_required_gadgets values (1, 1, 'title', 1, 1);
Query OK, 1 row affected (0.09 sec)

mysql> select * from gadget_required_gadgets where group_id = 1;
+----------+-------------+-------+------------+---------------+
| group_id | instance_id | title | column_num | display_order |
+----------+-------------+-------+------------+---------------+
| 1        | 1           | title |          1 |             1 |
+----------+-------------+-------+------------+---------------+
1 row in set (0.05 sec)

mysql> select * from gadget_required_gadgets where instance_id = 1 and group_id
IN ('external', 123);
Empty set, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'external'
1 row in set (0.00 sec)

But this is not a bug formally. It is documented that when string is compared to number they are both converted to float (in both = and IN cases). If string can not be properly converted to float number, you get warning and value 0 is used. Compare to the following:

mysql> select * from gadget_required_gadgets where instance_id = 1 and group_id
IN (124, 123);
Empty set (0.00 sec)

mysql> select * from gadget_required_gadgets where instance_id = 1 and group_id
IN ('124', '123');
Empty set (0.00 sec)

No problems as you can see.

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