Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.7, 11.3.2, 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2, 11.3(EOL)
-
None
Description
User-defined variable assignment is sometimes ignored and sometimes not:
This is somewhat weird:
> select @n:=0
|
-> ,case when @n=0 then concat_ws('~',@n:=+1,'test-1') end
|
-> ,@n
|
-> ,case when @n=0 then concat_ws('~',@n:=@n+1,'test-2') end
|
-> ,@n
|
-> ;
|
+-------+--------------------------------------------------------+------+----------------------------------------------------------+------+
|
| @n:=0 | case when @n=0 then concat_ws('~',@n:=+1,'test-1') end | @n | case when @n=0 then concat_ws('~',@n:=@n+1,'test-2') end | @n |
|
+-------+--------------------------------------------------------+------+----------------------------------------------------------+------+
|
| 0 | 1~test-1 | 0 | 1~test-2 | 1 |
|
+-------+--------------------------------------------------------+------+----------------------------------------------------------+------+
|
1 row in set (0.00 sec)
|
And workaround is weirder:
> select @n:=0
|
-> ,case when @n=0 then concat_ws('~',@n:=@n^@n+1,'test-1') end
|
-> ,@n
|
-> ,case when @n=0 then concat_ws('~',@n:=@n+1,'test-2') end
|
-> ,@n
|
-> ;
|
+-------+-------------------------------------------------------------+------+----------------------------------------------------------+------+
|
| @n:=0 | case when @n=0 then concat_ws('~',@n:=@n^@n+1,'test-1') end | @n | case when @n=0 then concat_ws('~',@n:=@n+1,'test-2') end | @n |
|
+-------+-------------------------------------------------------------+------+----------------------------------------------------------+------+
|
| 0 | 1~test-1 | 1 | NULL | 1 |
|
+-------+-------------------------------------------------------------+------+----------------------------------------------------------+------+
|
1 row in set (0.00 sec)
|
Documentation says:
"It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined."
Keeping that in mind, at least this case is still very wrong:
> select @n:=0;
|
+-------+
|
| @n:=0 |
|
+-------+
|
| 0 |
|
+-------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> select @n
|
-> ,case when @n=0 then concat_ws('~',@n:=+1,'test-1') end
|
-> ,@n
|
-> ;
|
+------+--------------------------------------------------------+------+
|
| @n | case when @n=0 then concat_ws('~',@n:=+1,'test-1') end | @n |
|
+------+--------------------------------------------------------+------+
|
| 1 | NULL | 1 |
|
+------+--------------------------------------------------------+------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> select @n;
|
+------+
|
| @n |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> select version();
|
+---------------------------------------+
|
| version() |
|
+---------------------------------------+
|
| 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 |
|
+---------------------------------------+
|
1 row in set (0.00 sec)
|
Order of columns is not an issue within case-when. How does "order of actions" imply that the case-when condition apparently evaluates twice?
Also, when optimizing queries, should we no longer expect "then" part to evaluate only if "when" is true? Same is often true for "or" and "and" in expressions elsewhere.
Constant expression assignment `@n:=+1` should not work differently than variable expression assignment `@n:=@n+1` in same context, engine optimization went a step too far here.
Expected:
> select @n:=0;
|
+-------+
|
| @n:=0 |
|
+-------+
|
| 0 |
|
+-------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> select @n
|
-> ,case when @n=0 then concat_ws('~',@n:=+1,'test-1') end
|
-> ,@n
|
-> ;
|
+------+--------------------------------------------------------+------+
|
| @n | case when @n=0 then concat_ws('~',@n:=+1,'test-1') end | @n |
|
+------+--------------------------------------------------------+------+
|
| 0 | 1~test-1 | 1 |
|
+------+--------------------------------------------------------+------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> select @n;
|
+------+
|
| @n |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.00 sec)
|