[SOLVED] Odd behavior of user-defined variables in MySQL

Odd behavior of user-defined variables in MySQL

I've noticed something odd about user-defined variables:

Lets say I have this table:

 num_table
+---------+
| numbers |
|    3    |
|    6    |
|    9    |
+---------+

I can create a comma-separated list and store it in a user-defined variable like so:

SELECT @var := GROUP_CONCAT `numbers` from num_table;

Which will assign the value 3,6,9 to @var.

And here is the odd part. Running

SELECT 3 IN (@var)

returns 1, but running

SELECT 6 IN (@var)

returns 0.

It seems to me it should either work or not work. Any idea why it works only with the first number in the list?

Solution

You cannot use IN () with a variable and have that variable be treated as a list - only actual lists (perhaps of variables) can be used - i.e. IN (1, 2, 3) or IN (@var1, @var2, @var3)

Neither should work if @var contains '3, 6, 9' - so I suspect @var contains '3', though - can you verify its contents?

Martin might be on to something with the casting - I'd bet '3' IN (@var) returns nothing