This compatibility issue was originally reported by Jerome B. on the maria-developers list on 2017-07-31.
This script returns 1 in MariaDB:
A similar script with sql_mode=ORACLE in 10.3 also returns 1:
A similar script returns 10 in Oracle:
- MariaDB evaluates assigned values from left to right
- Oracle evaluates assigned values before any assignment is done
The SQL standars says in the section <select statement: single row>:
1) Let Q be the result of <query specification> S.
a) If the cardinality of Q is greater than one ...
b) If Q is empty ...
c) Otherwise, values in the row of Q are assigned to their corresponding targets.
a) If the <select target list> contains a single <target specification> TS
iv) For each <target specification> TS that is an <embedded variable specification>, the General
Rules of Subclause 9.1, “Retrieval assignment”, are applied with TS as TARGET and the corre-
sponding value in the row of Q as VALUE. The assignment of values to targets in the <select
target list> is in an implementation-dependent order.
Although the Standard does not explicitly says so, it seems clear that the above rules assume that the result row is calculated BEFORE any assignment is done:
1. The query S is executed and its result Q is evaluated
2. The result Q is tested for cardinality and if cardinality is not one, an error is returned
3. Otherwise, (i.e. if the cardinality is one), assignments are done.
Therefore, in the above SQL scrips, the fact that 1 is assigned to b1 must not affect the value of res. The expression cast(b1 as varchar(10)) must be evaluated using the original b1 value of 10.
MariaDB's results seem to contradict the SQL standard.
Note, the phrase The assignment of values... is in an implementation-dependent order should not confuse the reader: it only says that copying from Q to targets (e.g. b1 and res) is done in arbitrary order (it does not change the fact that Q itself is evaluated before any assignment is done).
In any cases, at least when running with sql_mode=ORACLE, MariaDB should reproduce Oracle-style behavior and return 10 in the above script.