Generally left to right?
On IRC the other day someone asked about the order in which set operations are performed. I replied something to the effect of it not being guaranteed (“Single-table UPDATE assignments are generally evaluated from left to right… “) to be left to right, but that using User Defined Variables could guarantee deterministic behavior. The fellow ended a bit confused (my fault) and using two queries… However, he could have done something like the following:
mysql> INSERT INTO t (c1,c2)
-> VALUES(0,0),(1,1);
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> UPDATE t
-> SET c1=@var+1,
-> c2=@var
-> WHERE
-> IFNULL(
-> IF(
-> @var :=c1
-> ,1,1)
-> ,1);
Query OK, 2 rows affected (0.22 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM t;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 0 |
| 2 | 1 |
+------+------+
2 rows in set (0.00 sec)One might ask what is with all the ugliness surrounding the udv assignment? Well if the where clause does not return something that can be converted to a non-zero integer for all expressions/whatever you get nota for updates or selects. This is problematic if the assignment is to null, 0, or a fully numeric string. So we really want the assignment to return 1 always.
mysql> SELECT 1 FROM dual WHERE 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> SELECT 1 FROM dual WHERE null;
Empty set (0.00 sec)
mysql> SELECT 1 FROM dual WHERE 0;
Empty set (0.00 sec)
mysql> select 1 from dual where 'asdf1';
Empty set, 1 warning (0.00 sec)
mysql> select 1 from dual where '1asdf';
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)
(it’s too bad I have to use dual here, I feel dirty in an expensive Oracleish way)
mysql> SELECT 1 WHERE 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'WHERE 1' at line 1