Bug #37730 select from where in returns incorrect results

• Submitted: 29 Jun 2008 22:13
• Modified: 14 Jul 2008 21:05
• Reporter: Omry Yadan
• Status: Won't fix
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.62
• OS: Any
• CPU Architecture: Any

[29 Jun 2008 22:13] Omry Yadan

Description:
if a number gets into the 'in' section in a select, unexpected results are returned.

How to repeat:
create table t(a varchar(10));
insert into t(a) values('aaa'),('bbb'),('ccc'),('ddd');
select * from t where a in (666,'a');
+------+
| a    |
+------+
| aaa  |
| bbb  |
| ccc  |
| ddd  |
+------+

[30 Jun 2008 4:53] Valeriy Kravchuk

Thank you for a bug report. Verified just as described with 5.0.62:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.62-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t(a varchar(10));
Query OK, 0 rows affected (0.22 sec)

mysql> insert into t(a) values('aaa'),('bbb'),('ccc'),('ddd');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t where a in (666);
Empty set (0.00 sec)

mysql> select * from t where a in ('a');
Empty set (0.00 sec)

mysql> select * from t where a in (666,'a');
+------+
| a    |
+------+
| aaa  |
| bbb  |
| ccc  |
| ddd  |
+------+
4 rows in set, 1 warning (0.00 sec)

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

Bug is NOT repeatable in 5.1.25:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t;
Query OK, 0 rows affected (0.11 sec)

mysql> create table t(a varchar(10));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t(a) values('aaa'),('bbb'),('ccc'),('ddd');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t where a in (666,'a');
Empty set (0.09 sec)

[11 Jul 2008 9:54] Georgi Kodinov

This is fixed in 5.1 by Bug#18630.