Odd behavior of user-defined variables in MySQL

Question

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?

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
– Martin Smith, Commented May 10, 2011 at 15:59

Cade Roux · Accepted Answer · 2011-05-10 16:01:50Z

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

answered May 10, 2011 at 16:01

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jisaacstone Over a year ago

OK @Martin seems to be correct. I can verify @var does contain the list because SELECT @var LIKE '%6%' retunrs 1.

Kaii · Accepted Answer · 2012-07-21 22:29:18Z

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

edited Jul 21, 2012 at 22:29

answered Jul 9, 2012 at 22:38

Kaii

20.6k3 gold badges40 silver badges63 bronze badges

Collectives™ on Stack Overflow

Odd behavior of user-defined variables in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related