Setting and using a variable in a MySQL WHERE

Question

If I do the following query on MySQL 5.7.16, then the resultset contains one row with the value 2, which is expected:

SELECT *
FROM (SELECT 1 as x UNION SELECT 2 UNION SELECT 3) AS t
WHERE ( 2 IS NULL OR t.x = 2 )
;
-- Resultset: 1 row, x = 2

Now, I would like to use this logic in a prepared statement, like ? IS NULL OR t.x = ?. So you see that the same parameter appears twice. Hence, I applied an advice I found on SO (I don't remember the exact location): put the parameter in a MySQL session variable, and use it:

SELECT *
FROM (SELECT 1 as x UNION SELECT 2 UNION SELECT 3) AS t
WHERE ( (@x := 2) IS NULL OR t.x = @x )
;
-- Resultset: 0 row

But this fails: no row is returned. When I also select the @x variable, in order to see what's going on, I get a NULL value for @x:

SELECT @x
FROM (SELECT 1 as x UNION SELECT 2 UNION SELECT 3) AS t
WHERE ( (@x := 2) IS NULL OR t.x = 2 )
;
-- Resultset: 1 row, @x = NULL

So it seems that the variable is not set when put in the WHERE? What is going on?

I could make an INNER JOIN (SELECT 2 AS x) AS params and use params.x inside the WHERE, but I would like to understand what's happening in that WHERE ( (@x = 2) IS NULL OR t.x = @x ).

Giorgos Betsos · Accepted Answer · 2017-05-17 09:30:12Z

1

What is happening here is that the order of execution inside WHERE is arbitrary. This means that using

(@x := 2) IS NULL OR t.x = 2

there is no way to guarantee that @x := 2 will be executed first.

To correctly initialize the variable use a CROSS JOIN:

SELECT *
FROM (SELECT 1 as x UNION SELECT 2 UNION SELECT 3) AS t
CROSS JOIN (SELECT @x := 2) AS v
WHERE ( @x IS NULL OR t.x = @x )

answered May 17, 2017 at 9:30

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

Collectives™ on Stack Overflow

Setting and using a variable in a MySQL WHERE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related