bug: UNION NULL value returns incorrect result set

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values (120), (0), (111);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select NULL union select a+0 from t2;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Expected behavior

mysql> select NULL union select a+0 from t2;
+------+
| NULL |
+------+
| NULL |
|  120 |
|    0 |
|  111 |
+------+
4 rows in set (0.00 sec)

How To Reproduce

create table t2 (a int);
insert into t2 values (120), (0), (111);
select NULL union select a+0 from t2;

Environment

[root@localhost ~]# /opt/stonedb57/install/bin/mysqld --version
/opt/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:HEAD
        Branch name: HEAD
        Last commit ID: b44a51ce8
        Last commit time: Date:   Fri Dec 2 16:11:06 2022 +0000
        Build time: Date: Mon Dec  5 06:12:44 UTC 2022
[root@localhost ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[lujiashun]

ACK

[lujiashun]

In function Engine::Execute, to check the "// 1. all tables are derived and derived tables has no from table,// 2. fields of derived tables has sp", it seems we should use lex->all_selects_list instead of lex->select_lex;

[lujiashun]

Check Engine::Execute, the logic to decide the way to mysql or to tianmu, (condition 1 or condition 2)
condition 1. if all the table is derived and all the tables has not from table;
condition 2. if any table has sp item;

In the two scenario,
scenario 1. table 1 is derived table and has sp, table 2 is not derived
scenario 2. talbe 1 is not derived, table 2 is derived table and has sp;

for scenario 1, the return value is "to mysql" according to the code logic;
for scenario 2, the return values is "to tianmu" according to the code logic;

the table order decides the return values, is the logic correct? @adofsauron

[adofsauron]

That should be correct. That's what the comment says

  // 1. all tables are derived and derived tables has no from table
  // 2. fields of derived tables has sp

[lujiashun]

crash in this sql:
select * from (select NULL union select a+0 from t2)t3;

[lujiashun]

mysql> select a +0 from t2 union select NULL from t2 union select null from t2;
+------+
| a +0 |
+------+
|  120 |
|    0 |
|  111 |
| NULL |
+------+
4 rows in set (0.01 sec)

[lujiashun]

carsh in this SQL:
select "a" from t2 limit 0;