[MDEV-21350] user variable assignment and if function Created: 2019-12-18  Updated: 2021-02-15

Status: Stalled
Project: MariaDB Server
Component/s: Variables
Affects Version/s: 10.1.40, 10.3.20
Fix Version/s: 10.5

Type: Bug Priority: Minor
Reporter: Thomas Mischke Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None


 Description   

Hello,

we ran into a problem while using user variables and using different collations. It seems that a variable assignment can take place even if the condition to do so is not met.

Out server is set to use utf8mb4_unicode_ci. But some connections are using utf8_unicode_ci, and only those connections show the following problem.

We broke it down to the following example SQL (the real statement is much more complex and not so easy to work around the shown behaviour):

SET NAMES UTF8mb4 COLLATE UTF8MB4_UNICODE_CI;
 
create TABLE t1 (side VARCHAR(10)) COLLATE utf8mb4_unicode_ci;
 
 
 
INSERT INTO t1 (side) VALUES ('L');
 
 
 
SET NAMES UTF8 COLLATE UTF8_unicode_CI;
 
SET @bla = 'foo';
 
SELECT if(@bla = 'bar', @bla := s.side, @bla := 'bar') FROM t1 s;
 
 
 
DROP TABLE t1;

My expectation is, that the result is a single value 'bar', because the if condition should fail and therefore the "else" statement should be executed. But we observe that the result is 'L', which can only be if the if condition succeeds. A quick check shows that @bla is really 'bar'. How can this be?

If I leave out the line "set names utf8 collate utf8_unicode_ci", that is if I continue to use utf8mb4_unicode_ci, I get the expected result and behavior.

Is this a bug, or a somehow intended behavior?

Best regards,

Thomas Mischke

 Comments   
Comment by Alice Sherepa [ 2019-12-18 ]

KB 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." https://mariadb.com/kb/en/library/user-defined-variables/
In the example, when collation is set, then CONVERT function appears, the execution order was
1) convert executed (@bla='bar') 2) if @bla='bar', which is true 3) @bla:=`test`.`s`.`side`

MariaDB [test]> SET NAMES UTF8 COLLATE UTF8_unicode_CI;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> SET @bla := 'foo';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> explain extended SELECT if(@bla = 'bar', @bla := s.side, @bla := 'bar') FROM t1 s;
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
|    1 | SIMPLE      | s     | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 |       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
1 row in set, 1 warning (0.000 sec)
 
 
 
Note (Code 1003): select if(@`bla` = 'bar',@bla:=`test`.`s`.`side`,convert(@bla:='bar' using utf8mb4)) AS `if(@bla = 'bar', @bla := s.side, @bla := 'bar')` from `test`.`t1` `s`

in the other case @bla='foo', 'foo'!= 'bar', so @bla:='bar'

MariaDB [test]> SET NAMES UTF8mb4 COLLATE UTF8MB4_UNICODE_CI;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> create TABLE t1 (side VARCHAR(10)) COLLATE utf8mb4_unicode_ci;
 
Query OK, 0 rows affected (0.030 sec)
 
 
 
MariaDB [test]> INSERT INTO t1 (side) VALUES ('L');
 
Query OK, 1 row affected (0.006 sec)
 
 
 
MariaDB [test]> SET @bla := 'foo';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> explain extended SELECT if(@bla = 'bar', @bla := s.side, @bla := 'bar') FROM t1 s;
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
|    1 | SIMPLE      | s     | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 |       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
1 row in set, 1 warning (0.001 sec)
 
 
 
Note (Code 1003): select if(@`bla` = 'bar',@bla:=`test`.`s`.`side`,@bla:='bar') AS `if(@bla = 'bar', @bla := s.side, @bla := 'bar')` from `test`.`t1` `s`

Comment by Alexander Barkov [ 2019-12-18 ]

During the prepare stage, this query it is internally rewritten, and the third argument of IF() is replaced to:

CONVERT(@bla := 'bar' USING utf8mb4)

which is immediately evaluated. So when the actual execution stage starts, the value of @bla is already set to 'bar'.

As alice mentioned, the manual says that behavior is possible. This is not a bug.

To avoid this effect, please use this query instead:

SELECT @bla:=if(@bla = 'bar', s.side, 'bar') FROM t1 s;

It returns 'bar' as expected, without hidden assignments.

Comment by Alexander Barkov [ 2019-12-19 ]

After discussion with the team, we decided that this behavior is not nice. Evaluation can even happen during EXPLAIN and PREPARE.

Before the actual execution, we should try to avoid any evaluation that may have side effect, like user and system variable assignments, stored function calls, etc.

Comment by Thomas Mischke [ 2019-12-19 ]

Thanks for the workaround, the explanations and especially for classifying this behavior as "not nice"!

Comment by osoda [ 2020-01-03 ]

Thanks for explaining, I was a bit tangled. A separate question, @alice I have seen that in the code you gave as an example, in the end there is a part that says 'Note (Code 1003):', it seems that it shows you more deeply how the query is prepared. What software or setup do you have to show you that part of the code?

#https://jira.mariadb.org/browse/MDEV-21350?focusedCommentId=140908&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-140908

Note (Code 1003): select if(@`bla` = 'bar',@bla:=`test`.`s`.`side`,convert(@bla:='bar' using utf8mb4)) AS `if(@bla = 'bar', @bla := s.side, @bla := 'bar')` from `test`.`t1` `s`

Comment by Alice Sherepa [ 2020-01-07 ]

osoda,

MariaDB [(none)]> \W
 
Show warnings enabled.
 
MariaDB [(none)]> \w
 
Show warnings disabled.

Generated at Thu Feb 08 09:06:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.