1

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?

jisaacstone
  • 4,234
  • 2
  • 25
  • 39
  • 3
    I'd imagine it casts `3,6,9` to a number and ends up disregarding anything from the first non numeric character onwards and so ends up with 3. – Martin Smith May 10 '11 at 15:59

2 Answers2

2

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

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • OK @Martin seems to be correct. I can verify @var does contain the list because `SELECT @var LIKE '%6%'` retunrs `1`. – jisaacstone May 10 '11 at 16:27
1

You cannot use IN () with a string variable - but you can use FIND_IN_SET() instead, which serves exactly this purpose:

SELECT FIND_IN_SET(6, @var)

returns 2 - second position

SELECT FIND_IN_SET(7, @var)

returns NULL - no match

Kaii
  • 20,122
  • 3
  • 38
  • 60