mysql variable assignment : how to force assignment order?

Question

Due to the fact that mysql is a declarative language, I can't find a way to force assignment variable order.

Take 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;

The result is:

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

This is because @v2 is parsed before @v1 by the mysql engine.

How can I force order of assignment to have this result :

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

EDIT: this is not the same question as : Variable assignment with mysql

Here is a question about to force assignment order not why the result is not the expected one.

UPDATE : when you use left outer join, the result is also strange:

SET @v1=0;

SELECT @v1
FROM Account other 
LEFT OUTER JOIN SimpleValue f_PC ON f_PC.accountId=other.id AND f_PC.refShortcut='PC'  
WHERE CASE WHEN (other.context='44') THEN (@v1:=@v1+1) ELSE null END
ORDER BY @v1 ASC

In this case, the query returns 60 results but @v1 value is 120.

If I remove the left outer join, v1 value is 60. Why ?

Answer 1

In a select statement, the clause goes from LEFT to RIGHT, TOP to BOTTOM, so it works out as expected (MS Access uses the same strategy). In a WHERE clause however, all bets are off and the best filter is chosen. You can force it by taking advantage of the fact that the CASE statement requires left to right evaluation (preserving the presentation order).

WHERE CASE WHEN (@v1:=@v2) is not null THEN (@v2:=2) ELSE (@v2:=2) = 0 END

This force is to evaluate (and assign 2 to @v2) in either branch, but for the 1st run ONLY, @v1:=@v2 returns null, and @v2 becomes 2 which is compared against 0 for overall FALSE. The 2nd time around, @v1 := @v2 [= 2], and (@v2:=2) results in 2 (which is true).