SET a variable in SELECT statement - MySQL

Question

I'm using this code which has an error:

SET @rejects = '';

SELECT *
FROM list
WHERE maker = 1
    AND by_ids IN ('10','11')
    AND country LIKE '%I%'
    AND (
        src IS NULL
        || src NOT IN (@rejects)
        AND checkSrc(src) = 'yes'
        AND SET @rejects = CONCAT(@rejects,',',src)
    );

What's causing the issue?

Solution 1

The issue is that you cannot mix select and set in one statement, there'll surely be syntax error:

select*from t where 1 and set@a=1;

^{ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set@a=1' at line 1}

If you want to do set within select, use the colon equals syntax. Change this:

select*from t where 1 and set@a=1;

into:

select*,@a:=1 from t where 1;

Here's how you update the variable upon each row:

create table t(id int); insert t values(1),(2),(3);
set@a=0;
select@a:=id from t;

+--------+
| @a:=id |
+--------+
|      1 |
|      2 |
|      3 |
+--------+

And you can even do concat:

set@a='0';
select @a:=concat(@a,',',id)from t;

+-----------------------+
| @a:=concat(@a,',',id) |
+-----------------------+
| 0,1                   |
| 0,1,2                 |
| 0,1,2,3               |
+-----------------------+

Or concat without the leading 0:

set@a='';
select @a:=concat(@a,if(@a='','',','),id)from t;

+------------------------------------+
| @a:=concat(@a,if(@a='','',','),id) |
+------------------------------------+
| 1                                  |
| 1,2                                |
| 1,2,3                              |
+------------------------------------+

However, the manual explicitly states that this is dangerous:

...you should never assign a value to a user variable and read the value within the same statement...

...you might get the results you expect, but this is not guaranteed.

...the order of evaluation for expressions involving user variables is undefined.

This has also been mentioned on Xaprb.

Lastly, if you're doing quirky things like assigning differing value types to the variable and etc, checkout the manual to be sure you understand the intricate mechanisms.

Solution 2

Then you might write your query like this.

SET @rejects = '';
SELECT @rejects = CONCAT(@rejects,',',src) FROM list WHERE maker = 1 AND by_ids IN ('10','11') AND country LIKE '%I%' AND 
(src IS NULL OR src NOT IN (@rejects) AND checkSrc(src) = 'yes');
SELECT @rejects;

Comments

[fedorqui]

Could be by_ids INT('10','11') to by_ids IN('10','11') ?

[Ravindra Gullapalli]

Why AND SET @rejects = CONCAT(@rejects,',',src) in WHERE clause?

[Andrew Eisenberg]

Thats what i want, i want to concat the value of each searched src into @rejects variable.

[mko]

|| should not be used as an or.

[Admin]

You can see examples when searching for "MySQL group by greatest/top n" or similar - this is because MySQL solutions require some interesting hacks (usually just incrementing a counter).

[Ravindra Gullapalli]

What is your expected output? Also if src is null then there could be a problem. Can you post your table structure and expected output?

[Philipp]

"...you should never assign a value to a user variable and read the value within the same statement..." that saved me a lot of trial-and-error.