MySQL Bugs: #9578: WHERE 'str' IN(col1, col2) not working in 5.0.4

Bug #9578	WHERE 'str' IN(col1, col2) not working in 5.0.4
Submitted:	2 Apr 2005 5:49	Modified:	23 Nov 2005 17:37
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.4 bk-tree	OS:	FreeBSD (FreeBSD/Windows)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The syntax WHERE 'str' IN (col1, col2) has stopped working properly.

4.0.10 and 5.0.2:

mysql> SELECT VERSION();
+---------------+
| VERSION()     |
+---------------+
| 4.1.10-nt-log |
+---------------+
1 row in set (0.01 sec)

mysql> SELECT iid, item_name FROM loot WHERE 'Glowing Muramite Rune' IN(iid, item_name);
+------+-----------------------+
| iid  | item_name             |
+------+-----------------------+
| 2257 | Glowing Muramite Rune |
+------+-----------------------+
1 row in set (0.05 sec)

in 5.0.4:

mysql> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 5.0.4-beta-log |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT iid, item_name FROM loot WHERE 'Glowing Muramite Rune' IN(iid, item_name);
+------+-----------------------+
| iid  | item_name             |
+------+-----------------------+
..... (Every row in the table) ...
+------+-----------------------+
2 rows in set (0.00 sec)

How to repeat:
apply mysqldump associated with this bugreport then run the following query:

SELECT iid, item_name FROM loot WHERE 'Glowing Muramite Rune' IN(iid, item_name);

Tested on Windows latest BK source:

mysql> SELECT iid, item_name FROM loot WHERE 'Glowing Muramite Rune' IN(iid,
    -> item_name);
+------+-----------------------+
| iid  | item_name             |
+------+-----------------------+
|    2 | Boots of the Storm    |
| 2257 | Glowing Muramite Rune |
+------+-----------------------+
2 rows in set, 2 warnings (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Glowing Muramite Rune' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Glowing Muramite Rune' |
+---------+------+-----------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.4-beta-debug |
+------------------+
1 row in set (0.01 sec)

The bug is in the 4.1, not in 5.0.
5.0 correctly aggregates the types according to http://dev.mysql.com/doc/mysql/en/comparison-operators.html
and compares everything as floating point numbers - thus the result.

4.1 was aggregating the types too, but later it was forgetting this and using the type of the first argument instead.

Tobias: try, e.g.

SELECT iid, item_name FROM loot WHERE 0 IN (iid, item_name);

Besides, the manual describes type aggreation for IN completely wrong, we have to fix it.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25013

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've updated the description for the IN operator.