MySQL Bugs: #39832: Inconsistents when doing SELECTS using session variables in IN() clause

Bug #39832	Inconsistents when doing SELECTS using session variables in IN() clause
Submitted:	3 Oct 2008 8:05	Modified:	3 Oct 2008 8:43
Reporter:	Marc Buchmann	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.24a	OS:	Linux (Redhat ES$)
Assigned to:		CPU Architecture:	Any

Description:
Using session variables to store parameters for use in a SELECT ... WHERE ... IN(@var) clause only returns the first matching row of what would otherwise be a multi-row result set

How to repeat:
mysql> create table 'bugtest' (x int default null);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bugtest (x) values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select @bugtest:='2,3';
+-----------------+
| @bugtest:='2,3' |
+-----------------+
| 2,3             | 
+-----------------+
1 row in set (0.00 sec)

/** BUG OCCURS HERE **/
mysql> select x from bugtest where x in (@bugtest);
+------+
| x    |
+------+
|    2 | 
+------+
1 row in set (0.00 sec)

/** EXPECTED RESULTS HERE **/
mysql> select x from bugtest where x in (2,3);
+------+
| x    |
+------+
|    2 | 
|    3 | 
+------+
2 rows in set (0.00 sec)

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.0.24a-standard-log | 
+----------------------+
1 row in set (0.00 sec)

This is not a bug. You may use user variable instead of single expression. So, in the IN(@var) @var's value is just substituted and you get IN('2,3') as a result. This is NOT the same as IN(2,3), where we have 2 expressions in a comma-separated list.