The simultaneous_assignment mode in MariaDB 10.3.5
Starting with MariaDB 10.3.5, if you say sql_mode = ‘simultaneous_assignment’, then
UPDATE t SET a = b, b = a;
will swap b and a because a gets what’s originally in b, while b gets what’s originally in a (as of the start of the SET for a given row), instead of what was going on before, which was “Assignments are evaluated in left-to-right order”.
I will list things that I like or don’t like, and behaviour-change cases to watch for.
I like it that this is standard SQL at last
I don’t know any other vendor who ever did left-to-right assignments, and they had a good reason.
Way back in SQL-86, section 8.11
SET <object column> = <value expression> | NULL [, <object column> = <value expression> | NULL …
“If the <value expression> contains a reference to a column of T, the reference is to the value of that column in the object row before any value of the object row is updated.”
In case anyone still didn’t get it, SQL-92 added the sentence:
“The <value expression>s are effectively evaluated before updating the object row.”
Unfortunately later versions of the standard were slightly denser, such as:
“11) Each <update source> contained in SCL is effectively evaluated for the current row before any of the current row’s object rows is updated.”
The abbreviation “SCL” is the set clause list. The word “rows” is not a typo, but it’s for a special situation, we can still interpret as “before the object row is updated”.
Thus what we’re talking about is optionally following the standard.
So hurrah, let’s put equestrian statues of the MariaDB seal in main squares of our cities.
I don’t like the name
I’ll admit that the effect can be the same, and I’ll admit that some SQL Server commenters like to refer to this as doing things “all at once”. (See here and here.) But the standard description isn’t really demanding that all the assignments be simultaneous.
And the term “simultaneous assignment” can make some programmers
think of “parallel assignment”, as in this example (from Wikipedia)
a, b := 0, 1
A name like “evaluate_sources_first_during_updates” would have been better.
I don’t like that it’s a mode
We all know the problems of setting a global variable that people don’t see or think about when they create SQL statements.
So perhaps it would have been better to make a change to the UPDATE syntax, e.g.
UPDATE /*! new-style-update */ NEW STYLE t SET …;
It doesn’t solve the problem cleanly — now every statement has non-standard syntax in order to perform in a standard way — but it means you don’t get the behaviour change in every statement, only in the ones where you ask.
Or perhaps it would have been better to produce a warning: “You are using an sql_mode value that changes the behaviour of this particular UPDATE statement because evaluation is no longer left to right.” I don’t know if it would be hard. I don’t know if it’s too late.
Setup for examples
Following examples are done with a one-row table, made thus:
CREATE TABLE t (col1 INT, col2 INT, PRIMARY KEY (col1, col2)); INSERT INTO t VALUES (1, 2);
The MariaDB version is 10.3.5 downloaded on 2018-03-20.
Assigning with swaps and variables
SET @@sql_mode='simultaneous_assignment'; UPDATE t SET col1 = 1, col2 = 2; UPDATE t SET col1 = col2, col2 = col1; SELECT * FROM t; -- Result: col1 is 2, col2 is 1.
This is different from what happens with @@sql_mode=”. This is the best example that shows the new mode works.
SET @@sql_mode='simultaneous_assignment'; SET @x = 100; UPDATE t SET col1 = @x, col2 = @x := 200; SELECT * FROM t; -- Result: col1 is 100, col2 is 200.
This is not different from what happens with @@sql_mode=”. Conclusion: if you use the non-standard variable-assignment trick, the value is not updated in advance.
What about the SET statement?
DROP PROCEDURE p; SET @@sql_mode='simultaneous_assignment'; CREATE PROCEDURE p() BEGIN DECLARE v1 INT DEFAULT 1; DECLARE v2 INT DEFAULT 2; SET v1 = v2, v2 = v1; SELECT v1, v2; END; CALL p(); -- Result: v1 is 2, v2 is 2.
This is not different from what happens with @@sql_mode=”. Conclusion ‘simultaneous_assignment’ does not affect assignments with the SET statement.
Perhaps people will expect otherwise because SET statements look so similar to SET clauses, but this is actually correct. The way the standard defines things, for syntax that is similar to (though not exactly the same as) “SET v1 = v2, v2 = v1;”, is: the statement should be treated as equivalent to
SET v1 = v2; SET v2 = v1;
Good. But come to think of it, this is another reason to dislike the misleading mode name ‘simultaneous_assignment’. SET statements are assignments but @@sql_mode won’t affect them.
What about the INSERT statement?
SET @@sql_mode='simultaneous_assignment'; TRUNCATE TABLE t; INSERT INTO t SET col1 = col2, col2 = 99; SELECT * FROM t; -- Result: col1 is 0, col2 is 99.
Conclusion: ‘simultaneous_assignment’ does not affect INSERT … SET statements, processing is left to right. This looks inconsistent to me, but it’s not wrong.
What about the INSERT ON DUPLICATE KEY UPDATE statement?
SET @@sql_mode='simultaneous_assignment'; UPDATE t SET col1 = 0, col2 = 1; INSERT INTO t VALUES (0, 1) ON DUPLICATE KEY UPDATE col1 = col2, col2 = col1; SELECT * FROM t; -- Result: col1 is 1, col2 is 0.
Conclusion: ‘simultaneous_assignment does affect INSERT ON DUPLICATE KEY UPDATE. Nobody said that it has to (this syntax is a MySQL/MariaDB extension), but surely this is what everyone would expect. Good.
Triggers and constraints
I could not come up with a case where ‘simultaneous_assignment’ affected the order in which triggers or constraints are processed. Good.
Assigning twice to the same column
SET @@sql_mode='simultaneous_assignment'; UPDATE t SET col1 = 3, col2 = 4, col1 = col2; -- Result: error.
Good. There’s supposed to be an error, because (quoting the standard document again) “Equivalent <object column>s shall not appear more than once in a <set clause list>.”
Actually this doesn’t tell us something new about ‘simultaneous_assignment’, I only tried it because I thought that MariaDB would not return an error. Probably I was remembering some old version before this was fixed.
From the bugs.mysql.com site quoting the MySQL manual:
“Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.”
I don’t see an equivalent statement in the MariaDB manual. So I conclude that even for multiple-table updates the assignments will be done in a standard way, but couldn’t think of a good way to test.
There are other things
The really big things in MariaDB 10.3.5 are PL/SQL and the myrocks engine. So, just because I’ve looked at a small thing, don’t get the impression that 10.3.5 is going to be a minor enhancement.
There have been no significant updates to ocelotgui since my last blog post.