mysql variable assignment : how to force assignment order?

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 开发者_C百科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 ?

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).

As explain in the answers to your other question, nothing is guaranteed to work.

In your specific case, moving the assignments to the SELECT portion of the query seems to work:

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

SELECT (@v1:=@v2), (@v2:=2) 
FROM DUAL

About your example.

Presumably operations with constants has the highest priority in logical operatorin in WHERE clause.

SET @v1=10;
SET @v2=20;
SELECT @v1, @v2 
FROM dual 
WHERE (@v2:=2) is not NULL AND (@v1:=@v2) is not NULL;
=======
@v1 @v2
2   2

SET @v1=30;
SET @v2=40;
SELECT @v1, @v2
FROM dual 
WHERE (@v1:=@v2) is not NULL AND (@v2:=2) is not NULL;
========
@v1 @v2
2   2

The following example clearly proves this. The OR statement is not executed from left to right, as suggested. The OR operator performed the constant assignment operation first.

SET @v1=10;
SET @v2=20;
SELECT @v1, @v2 
FROM dual 
WHERE (@v2:=3) is not NULL OR (@v1:=@v2+1) is not NULL;
=========
@v1 @v2
10  3

SET @v1=30;
SET @v2=40;
SELECT @v1, @v2
FROM dual 
WHERE (@v1:=@v2+2) is not NULL OR (@v2:=4) is not NULL;
=========
@v1 @v2
30  4

About Assignment Operator and about your question

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 ?

The assignment operator ":=" in WHERE clause was worked out for each row of the data sets that were used in query! And vice versa, in the SELECT clause, the assignment operator was worked out for the resulting rows, that positively verified checks in JOIN and WHERE clause.

Recommendation. Try not use assignment operator ":=" in WHERE clause. Use assignment operator in SELECT clause.