User-Defined Session variables using in IN() function returns unexpected result

Question

For example I have a table named TABLE and it has a single field named FIELD with values 1 and 2. Running this statement

SELECT GROUP_CONCAT(`FIELD`)  
INTO @LIST 
FROM `TABLE`;

and

SELECT @LIST;

returns

1,2

then I do an IF() statement

SELECT IF (1 IN(@LIST),"TRUE","FALSE");

returns

TRUE

BUT when I run this one

SELECT IF (2 IN(@LIST),"TRUE","FALSE");

it returns

FALSE

so I try this statement for a little debugging

SELECT IF (2 IN(1,2),"TRUE","FALSE");

and it returns my expected value

TRUE

So is there something that I missed? I need it to return TRUE.

Answer 1

Variable @LIST has one string type value: '1,2'. To test value is in this values list you must use string function: FIND_IN_SET:

SELECT IF (FIND_IN_SET(1, @LIST), "TRUE","FALSE");
SELECT IF (FIND_IN_SET(2, @LIST), "TRUE","FALSE");

Test here: http://sqlfiddle.com/#!2/4fce1d/1

Answer 2

The in statement is looking for comma separated values which can be strings, integers, doubles and floats to name a few. You can use the in statement for most datatypes. What datatype is @LIST? A varchar? Your @LIST is a string that looks like this '1,2' not a comma separated value list like 1,2. Small subtle but very important difference. You cannot use the IN statement like you are trying above.

However if you created the query as a string and then executed the string as dynamic sql I bet it would work.

So try the following:

 SELECT GROUP_CONCAT(`FIELD`)  
 INTO @LIST 
 FROM `TABLE`;

 SET @s = CONCAT('SELECT IF (2 IN(',@LIST,'),"TRUE","FALSE");'); 

 PREPARE stmt1 FROM @s; 
 EXECUTE stmt1; 
 DEALLOCATE PREPARE stmt1; 

Go read about dynamic SQL.