Bug #82559 a bug about union with subquery

• Submitted: 12 Aug 2016 3:08
• Modified: 12 Aug 2016 8:07
• Reporter: 帅 Bang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.6, 5.7.14, 5.6.32
• OS: Linux
• CPU Architecture: Any
• Tags: subquery partition union

[12 Aug 2016 3:08] 帅 Bang

Description:
mysql> drop table if exists t1,t4,t5;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE t1 ( c1 int primary key, c2 int, c3 int);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (2,2,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (3,3,3);
Query OK, 1 row affected (0.00 sec)

mysql> create table t4 ( c1 int primary key, c2 int, c3 int );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t4 VALUES (1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t4 VALUES (2,2,2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t4 VALUES (3,3,3);
Query OK, 1 row affected (0.00 sec)

mysql> create table t5(c1 int , c2 int, c3 int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t5 values(1,1,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t5 values(2,2,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(3,3,3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1 order by c1,(select c1 from t5 order by c1,(select c1 from t4));
ERROR 1242 (21000): Subquery returns more than 1 row

Yeah, it is reasonable up to now. But ,when we type this , things get weird :

mysql> (select * from t1 order by c1,(select c1 from t5 order by c1,(select c1 from t4))) union all (select * from t1 order by c1,(select c1 from t5 order by c1,(select c1 from t4)));
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
+----+------+------+
6 rows in set (0.00 sec)

Why we can get 6 rows ?  It should have failed,  IMHO.

Thanks in advance.

How to repeat:
 drop table if exists t1,t4,t5;
 CREATE TABLE t1 ( c1 int primary key, c2 int, c3 int);
 INSERT INTO t1 VALUES (1,1,1);
 INSERT INTO t1 VALUES (2,2,2);
 INSERT INTO t1 VALUES (3,3,3);
 create table t4 ( c1 int primary key, c2 int, c3 int );
 INSERT INTO t4 VALUES (1,1,1);
 INSERT INTO t4 VALUES (2,2,2);
 INSERT INTO t4 VALUES (3,3,3);
 create table t5(c1 int , c2 int, c3 int);
 insert into t5 values(1,1,1);
 insert into t5 values(2,2,2);
 insert into t5 values(3,3,3);

 select * from t1 order by c1,(select c1 from t5 order by c1,(select c1 from t4));
 (select * from t1 order by c1,(select c1 from t5 order by c1,(select c1 from t4))) union all (select * from t1 order by c1,(select c1 from t5 order by c1,(select c1 from t4)));

Suggested fix:
 select * from t1 order by c1,(select c1 from t5 order by c1,(select c1 from t4));

 (select * from t1 order by c1,(select c1 from t5 order by c1,(select c1 from t4))) union all (select * from t1 order by c1,(select c1 from t5 order by c1,(select c1 from t4)));

both failed

[12 Aug 2016 8:07] MySQL Verification Team

Hello Bang,

Thank you for the report and test case.
Observed this with 5.6.32/5.7.14 builds.

Thanks,
Umesh

[16 Aug 2016 20:12] Roy Lyseng

Hi,

in some cases, we optimize away ordering expressions that do not make a difference to the overall query processing. When doing that, we also skip subqueries in such expressions, so error messages like these are not issued. We think that the optimizer has liberty to do that.

That said, we should eliminate ordering expressions only for UNION DISTINCT queries and not UNION ALL (like this one). Although no guarantee is given by SQL for the final ordering, we should adhere to the ORDER BY clauses and output the union components in partial order.

In my opinion, this is the true error here.