MySQL Bugs: #20936: STORED-PROCEDURES:SELECT ... WHERE ... IN ( ) statement doesn't work as expected

Bug #20936	STORED-PROCEDURES:SELECT ... WHERE ... IN ( ) statement doesn't work as expected
Submitted:	10 Jul 2006 12:12	Modified:	20 Jul 2006 10:05
Reporter:	Dogus Yildirim	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	in condition, select statement, stored procedure, where condition

Description:
I'm trying to call a stored procedure, but it doesn't work as expected. Here is the procedure's definition:

CREATE PROCEDURE `selectTest`(IN prm1 VARCHAR(50))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
     SELECT a,b  FROM `test_tbl`
     WHERE a IN (prm1);
END;

When I call the procedure with a parameter like 'A', it works fine, but when I call it with a parameter like 'A,B,C', it selects nothing.

call selectTest('A'); --works fine
call selectTest('A,B,C'); --doesn't work!!!

How to repeat:
I can't call a stored procedure which has a syntax like 'select ... where ... in (...)' sending multiple parameters seperated with comma.

Suggested fix:
I should be able to send a parameter like 'A,B,C' for the stored procedures like this.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc. 

Can you run the following select outside of SP successfully,

SELECT a,b  FROM `test_tbl`
WHERE a IN ('A,B,C');

Yes. You'll get all rows WHERE a = 'A,B,C'. It is not the same as:

SELECT a,b  FROM `test_tbl`
WHERE a IN ('A','B',C');

Same in stored procedure...