MySQL Bugs: #74246: SET: Semantic behavior different for regular execution and function

Bug #74246	SET: Semantic behavior different for regular execution and function
Submitted:	7 Oct 2014 8:04	Modified:	7 Oct 2014 9:10
Reporter:	Roy Lyseng	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.7, 5.6.22, 5.7.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The problem is related to how variables in SET statements containing
multiple expressions are processed.

First, let's see how SELECT and DO treat variables.

SET @a= NULL;
SELECT @a:= 1, @a:= @a+1;
SELECT @a;
+------+
| @a   |
+------+
|    2 |
+------+

SET @a= NULL;
DO @a:= 1, @a:= @a+1;
SELECT @a;
+------+
| @a   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

We see that the value assigned to @a in the first expression is used in
the second expression.

Now let's try a SET command:

SET @a= NULL;
SET @a= 1, @a= @a+1;
SELECT @a;
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

The second expression does not use the value assigned in the first expression.

Let's try the same statement in a function:

delimiter //
CREATE FUNCTION f() RETURNS INTEGER
DETERMINISTIC
BEGIN
  SET @a= 1, @a= @a+1;

  RETURN @a;
END//
delimiter ;

SELECT f();
+------+
| f()  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

If we assume that the behavior of SELECT, DO and SET within a function
is correct, then a SET command with multiple assignments like this:

SET @var1= expr1, @var2= expr2, ...;

is equivalent to the following list of single-assignment SET statements:

SET @var1= expr1;
SET @var2= expr2;
...

Notice however that this is inconsistent with how a SET clause in an UPDATE
statement is processed, where the expressions are always calculated based
on the old values of the target variables.

Example:

UPDATE t SET a= b, b= a WHERE ...;

will effectively swap the values of a and b, it will not update a and b
to become the old value of b.

How to repeat:
SET @a= NULL;
SELECT @a:= 1, @a:= @a+1;
SELECT @a;

SET @a= NULL;
DO @a:= 1, @a:= @a+1;
SELECT @a;

SET @a= NULL;
SET @a= 1, @a= @a+1;
SELECT @a;

delimiter //
CREATE FUNCTION f() RETURNS INTEGER
DETERMINISTIC
BEGIN
  SET @a= 1, @a= @a+1;

  RETURN @a;
END//
delimiter ;

SELECT f();

DROP FUNCTION f;

Hello Roy,

Thank you for the bug report and test case.
Observed that the SET behavior is different for regular execution and function.

Thanks,
Umesh

// 5.6.22

mysql> use test
Database changed
mysql>
mysql> SET @a= NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a:= 1, @a:= @a+1;
+--------+-----------+
| @a:= 1 | @a:= @a+1 |
+--------+-----------+
|      1 |         2 |
+--------+-----------+
1 row in set (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql>
mysql> SET @a= NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> DO @a:= 1, @a:= @a+1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

mysql>
mysql> SET @a= NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a= 1, @a= @a+1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> delimiter //
mysql> CREATE FUNCTION f() RETURNS INTEGER
    -> DETERMINISTIC
    -> BEGIN
    ->   SET @a= 1, @a= @a+1;
    ->
    ->   RETURN @a;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> SELECT f();
+------+
| f()  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

// 5.7.6

mysql> SET @a= NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a:= 1, @a:= @a+1;
+--------+-----------+
| @a:= 1 | @a:= @a+1 |
+--------+-----------+
|      1 |         2 |
+--------+-----------+
1 row in set (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> SET @a= NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> DO @a:= 1, @a:= @a+1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> SET @a= NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @a= 1, @a= @a+1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a
    -> ;
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>
mysql> delimiter //
mysql> CREATE FUNCTION f() RETURNS INTEGER
    -> DETERMINISTIC
    -> BEGIN
    ->   SET @a= 1, @a= @a+1;
    ->
    ->   RETURN @a;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> SELECT f();
+------+
| f()  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)