MySQL Bugs: #64349: SELECT ... WHERE column IN (S.P. Parameter List) returns incomplete result

Bug #64349	SELECT ... WHERE column IN (S.P. Parameter List) returns incomplete result
Submitted:	16 Feb 2012 8:22	Modified:	17 Feb 2012 5:01
Reporter:	David Berg	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.5.20 and earlier	OS:	Windows (XP Pro SP3)
Assigned to:		CPU Architecture:	Any

Description:
In a stored procedure, SELECT ... WHERE Column IN (Value List) 
- returns only row(s) that match the first value in Value List when Column is an INTEGER;
- returns no rows when Column is CHAR 
where Value List is the quoted, comma-separated parameter passed to the stored procedure in the CALL statement. 

The expected behavior is that all rows that match the list of values in the IN clause are returned.

How to repeat:
# Create and populate a table of INTEGER and corresponding CHAR values.
DROP TABLE IF EXISTS Tab1;
CREATE TEMPORARY TABLE Tab1 (IntVal INTEGER, CharVal CHAR(5));
INSERT INTO Tab1 VALUES (1,"One"),(2,"Two"),(3,"Three"),(4,"Four"),(5,"Five");

# Validate the contents of Tab1.
SELECT * FROM Tab1;

# Create a similar table for testing INTEGER values.
DROP TABLE IF EXISTS Tab2;
CREATE TEMPORARY TABLE Tab2 LIKE Tab1;

# Create a similar table for testing CHAR values.
DROP TABLE IF EXISTS Tab3;
CREATE TEMPORARY TABLE Tab3 LIKE Tab1;

# Create a stored procedure to test the hypothesis.
DELIMITER $$
DROP PROCEDURE IF EXISTS Test $$
CREATE PROCEDURE Test (ParmList CHAR(20))
BEGIN

SELECT ParmList;

# Select rows that match the parameter list of INTEGER values. 
INSERT INTO Tab2 
       SELECT IntVal, CharVal 
         FROM Tab1

        /* The quotes surrounding the value list must be removed for the
           INTEGER test, else Error 1292 (Truncated Double) results.
        */
        WHERE IntVal IN (REPLACE(ParmList,"\"",""));
        
# Select rows that match the parameter list of CHAR values. 
INSERT INTO Tab3 
       SELECT IntVal, CharVal 
         FROM Tab1
        WHERE CharVal IN (ParmList);         
END $$

DELIMITER ;

/* Test with INTEGER values. Note that only rows that match the first
   value in the value list are selected.
*/
TRUNCATE TABLE Tab2;
CALL Test ("1,2,3");
SELECT * FROM Tab2;

/* Test with CHAR values. Note that no rows are selected.
*/
TRUNCATE TABLE Tab3;
CALL Test("\"One\",\"Two\",\"Three\"");
SELECT * FROM Tab3;

Suggested fix:
Enable comma-separated values in a stored procedure parameter to be used as the value list in an IN clause in a SELECT statement.

If you need to build (parts of) SQL statements dynamically, why not to use prepared statements in your stored procedure? Check http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html for details.

Valeriy, Thank you for your suggestion. I definitely would have done that, except the values returned by the particular SELECT in my procedure are used in a cursor to iterate through a block of code for each value ... and cursors may not contain prepared statements.

My workaround in this case was to use a prepared statement to insert the values into a temporary table and declare a cursor to select from the temporary table.

Yes, you need some other approach/workaround, as IN ('1,2,3') condition will never evaluate the way you expect. You pass single string value with commas inside this way, not 3 numbers.