CODEWITHSUNDEEP
sqlmysqlvariables
Jerome Cance
Jerome Cance

Reputation: 8183

Variable assignment with mysql

I use variable assignment with mysql and I found a strange behavior.

See this query :

SET @v1=0;
SET @v2=0;

SELECT @v1, @v2 
FROM MyTable table 
WHERE (@v1:=@v2) is not null 
  AND (@v2:=2) is not null;

I was thinking that conditions are parsed in this order : first (@v1:=@v2) is not null and after (@v2:=2) is not null

and so the result must be : 

@v1 | @v2
---------
 0  |  2

But this is not the case. try this query and you will have:

@v1 | @v2
---------
 2  |  2

Why ?

Upvotes: 0

Views: 535

Answers (3)

btilly
btilly

Reputation: 46389

The query optimizer is free to rewrite and execute your code in any way it wishes. DO NOT depend on it working in any way that you expect.

Upvotes: 0

DVK
DVK

Reputation: 129363

The optimizer does not necessarily keep the order of expressions in WHERE clause the same as your query. You can not depend on any specific order of evaluation

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

SQL is a declarative language, so you cannot assume that your conditions are evaluated in the order you write them. You tell the engine what you want and it is free to determine how to get it.

Upvotes: 1

Related Questions