[MDEV-17388] Variables in Subquery gives wrong result Created: 2018-10-08  Updated: 2018-10-09  Resolved: 2018-10-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.1.26, 10.3.10, 10.2, 10.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Dominic Blattmann Assignee: Alice Sherepa
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Debian stretch 9.5

Attachments: File 50-mysqld_safe.cnf     File 50-server.cnf     File mariadb.cnf     File my.cnf    

 Description   

Following problem i've encountered after upgrading from MySql 5.1.73 (Where it does not happen) to 10.1.26. I've done the upgrade to 10.3.10 where it still behaves unexpected.

CREATE TABLE test (Id INT);
 
 
 
# Insert some rows
 
INSERT INTO test VALUES(1);
 
INSERT INTO test VALUES(2);
 
INSERT INTO test VALUES(3);
 
 
 
# The Following query is ok:
 
SELECT
 
   SUM(VariableA),
 
   SUM(VariableB)
 
FROM (
 
   SELECT 
      (SELECT @varA := 20) AS VariableA,
 
      @varA AS VariableB
 
   FROM test) as A;
 
 
 
+----------------+----------------+
 
| SUM(VariableA) | SUM(VariableB) |
 
+----------------+----------------+
 
|             60 |             60 |
 
+----------------+----------------+
 
 
 
# The same query, but remove SUM(VariableA) in first select. Should still give 60 for VariableB, but it returns NULL.
 
SELECT
 
   SUM(VariableB)
 
FROM (
 
   SELECT 
      (SELECT @varA := 20) AS VariableA,
 
      @varA AS VariableB
 
   FROM test) as A;
 
+----------------+
 
| SUM(VariableB) |
 
+----------------+
 
|           NULL |
 
+----------------+

I assume there is some optimization for the subquery going wrong.

 Comments   
Comment by Alice Sherepa [ 2018-10-08 ]

Could you please add your .cnf file(s), I can not reproduce this behavior with the delault configuration (tried 10.1.26, 10.3)

MariaDB [test]> SELECT
 
    ->    SUM(VariableA),
 
    ->    SUM(VariableB)
 
    -> FROM (
 
    ->    SELECT 
    ->       (SELECT @varA := 20) AS VariableA,
 
    ->       @varA AS VariableB
 
    ->    FROM test) as A;
 
+----------------+----------------+
 
| SUM(VariableA) | SUM(VariableB) |
 
+----------------+----------------+
 
|             60 |             60 |
 
+----------------+----------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]>  
MariaDB [test]> SELECT
 
    ->    SUM(VariableB)
 
    -> FROM (
 
    ->    SELECT 
    ->       (SELECT @varA := 20) AS VariableA,
 
    ->       @varA AS VariableB
 
    ->    FROM test) as A;
 
+----------------+
 
| SUM(VariableB) |
 
+----------------+
 
|             60 |
 
+----------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.1.26-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Comment by Dominic Blattmann [ 2018-10-08 ]

Attached, but it is complete clean fresh installation, only change made to enable remote access.
Tested with MyISAM and InnoDB

Comment by Dominic Blattmann [ 2018-10-08 ] 

MariaDB [test]> select version();
 
+--------------------------+
 
| version()                |
 
+--------------------------+
 
| 10.1.26-MariaDB-0+deb9u1 |
 
+--------------------------+

Comment by Alice Sherepa [ 2018-10-08 ]

Thanks a lot, now I can reproduce it, on MariaDB 5.5-10.3
The reason is that during optimization assignment operator disappeared and value of variable remained undefined.

CREATE TABLE t1 (Id INT);
 
INSERT INTO t1 VALUES(1),(2),(3);
 
 
 
MariaDB [test]> SELECT @varA FROM (SELECT (SELECT @varA := 20) FROM t1) as A;
 
+-------+
 
| @varA |
 
+-------+
 
| NULL  |
 
| NULL  |
 
| NULL  |
 
+-------+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> explain extended SELECT @varA FROM (SELECT (SELECT @varA := 20) FROM t1) as A;
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
 
1 row in set, 2 warnings (0.00 sec)
 
 
 
Note (Code 1249): Select 3 was reduced during optimization
 
Note (Code 1003): select (@`varA`) AS `@varA` from `test`.`t1`

Comment by Alice Sherepa [ 2018-10-09 ]

According to KB "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/, so it is not considered as a bug.

Generated at Thu Feb 08 08:36:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.