Compatibility: The order of evaluation of SELECT..INTO assignments

This compatibility issue was originally reported by Jerome B. on the maria-developers list on 2017-07-31.

This script returns 1 in MariaDB:

DROP PROCEDURE IF EXISTS p1;

DELIMITER $$

CREATE PROCEDURE p1(OUT res VARCHAR(10))

BEGIN

  DECLARE b1 INT DEFAULT 10;

  SELECT  1,CAST(b1 AS CHAR(10)) INTO b1, res FROM dual;  

END;

$$

DELIMITER ;

CALL p1(@res);

SELECT @res;

+------+

| @res |

+------+

| 1    |

+------+

A similar script with sql_mode=ORACLE in 10.3 also returns 1:

SET sql_mode=ORACLE;

DROP PROCEDURE IF EXISTS p1;

DELIMITER $$

CREATE PROCEDURE p1(res OUT VARCHAR)

AS

  b1 INT:=10;

BEGIN

  SELECT  1,CAST(b1 AS VARCHAR(10)) INTO b1, res FROM dual;  

END;

$$

DELIMITER ;

CALL p1(@res);

SELECT @res;

+------+

| @res |

+------+

| 1    |

+------+

A similar script returns 10 in Oracle:

SET SERVEROUTPUT ON;

SET SQLNUMBER OFF;

CREATE OR REPLACE PROCEDURE p1(res OUT VARCHAR)

AS

  b1 INT;

BEGIN

  b1:=10;

  SELECT 1,CAST(b1 AS VARCHAR(10)) INTO b1, res FROM dual;  

END;

/

DECLARE

  res INT;

BEGIN

  p1(res);

  DBMS_OUTPUT.PUT_LINE('res='||res);

END;

/

res=10

Notice:

• 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.
2) Case:
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.
...
4) CASE:
a) If the <select target list> contains a single <target specification> TS
b) Otherwise
...
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.