{"id":1315,"date":"2009-09-17T07:42:53","date_gmt":"2009-09-17T05:42:53","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1315"},"modified":"2009-09-17T07:42:53","modified_gmt":"2009-09-17T05:42:53","slug":"on-user-variables-evaluation-order","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/on-user-variables-evaluation-order","title":{"rendered":"On user variables evaluation order"},"content":{"rendered":"

There is something very unclear about what is defined and is undefined with regard to the order by which user variables are evaluated, even within the MySQL documentation<\/a> itself.<\/p>\n

I wish to present some examples and draw a conclusion. Since I will following state there’s missing information, I would greatly appreciate any educated comments.<\/p>\n

The trivial “reordering problem” case<\/h4>\n

Let’s look at the following query:<\/p>\n

\n
mysql> SET @a := 0;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> SET @b := 0;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> SELECT @a:=@b+1, @b:=@a FROM mysql.user;\r\n+----------+--------+\r\n| @a:=@b+1 | @b:=@a |\r\n+----------+--------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |\u00a0\u00a0\u00a0\u00a0\u00a0 3 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 |\u00a0\u00a0\u00a0\u00a0\u00a0 4 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 |\u00a0\u00a0\u00a0\u00a0\u00a0 5 |\r\n+----------+--------+\r\n5 rows in set (0.04 sec)<\/pre>\n<\/blockquote>\n
According to the docs, I can’t rely on the fact that the @b:=@a<\/strong> part reads the value of @a<\/strong> after is has incremented, So I may eventually end up with:<\/p>\n
\n
+----------+--------+\r\n| @a:=@b+1 | @b:=@a |\r\n+----------+--------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 |\u00a0\u00a0\u00a0\u00a0\u00a0 3 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 |\u00a0\u00a0\u00a0\u00a0\u00a0 4 |\r\n+----------+--------+<\/pre>\n<\/blockquote>\n
I wish to follow the documentation and see about other cases.<\/p>\n
The trivial “trivial” case<\/h4>\n
Quoting the MySQL documentation (8.4. User-Defined Variables<\/a>):<\/p>\n
…The general rule is never to assign a value to a user variable in       one part of a statement and<\/em><\/span> use the same       variable in some other part<\/strong> of the same statement. …<\/p><\/blockquote>\n
How does the above quote (focus on bold<\/strong> text) from the manual apply to the trivial:<\/p>\n
\n
SET @a := 0;\r\nSELECT *, @a := @a+1 FROM mysql.user;<\/pre>\n<\/blockquote>\n
We’ve just used\/read the value of @a<\/strong>, and have assigned it a new value (@a+1)<\/strong> at the same statement. What’s an other part<\/strong>? Is this considered to be the same part<\/strong> just because it’s the same column in the result set?<\/p>\n
Let’s complicate this further:<\/p>\n
A complicated case<\/h4>\n
Quoting the manual again, we see:<\/p>\n
\n
mysql> SET @t1=0, @t2=0, @t3=0;<\/code><\/strong>\r\nmysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;<\/code><\/strong>\r\n+----------------------+------+------+------+\r\n| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |\r\n+----------------------+------+------+------+\r\n|                    5 |    5 |    1 |    4 |\r\n+----------------------+------+------+------+<\/pre>\n<\/blockquote>\n
What’s going on here? Haven’t we been told not to assign and use the same variable in the same statement? @t1<\/strong> has been assigned in the first part, then has been used in the second (according to the documentation itself, we could have got ‘0’<\/em> for @t1<\/strong>, second column). But I’m not keen on ranting, look further:<\/p>\n
Looking at the first part of the statement: @t1:=(@t2:=1)+@t3:=4<\/strong>, what promises do I get here?<\/p>\n