MySQL Bugs: #78125: union all with order by not working in mysql5.7

Bug #78125	union all with order by not working in mysql5.7
Submitted:	18 Aug 2015 11:31	Modified:	20 Aug 2015 13:00
Reporter:	jijo ac	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S1 (Critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql5.7, UNION ALL

Description:
I want to union 10 identical tables and apply order by cluse. Each table has around 500000 data,its working fine with MySql5.6.
Sample query

SELECT CURRENT_TIME, VAL FROM ( 

SELECT ID,CURRENT_TIME,VAL FROM TBLE_1 UNION ALL 

SELECT ID,CURRENT_TIME,VAL FROM TBLE_2 UNION ALL 

SELECT ID,CURRENT_TIME,VAL FROM TBLE_3 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_4 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_5 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_6 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_7 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_8 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_9 ) 

ALLROWS WHERE ALLROWS.ID=? ORDER BY ALLROWS.CURRENT_TIME

How to repeat:
Sample query

SELECT CURRENT_TIME, VAL FROM ( 

SELECT ID,CURRENT_TIME,VAL FROM TBLE_1 UNION ALL 

SELECT ID,CURRENT_TIME,VAL FROM TBLE_2 UNION ALL 

SELECT ID,CURRENT_TIME,VAL FROM TBLE_3 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_4 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_5 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_6 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_7 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_8 UNION ALL  

SELECT ID,CURRENT_TIME,VAL FROM TBLE_9 ) 

ALLROWS WHERE ALLROWS.ID=? ORDER BY ALLROWS.CURRENT_TIME

Hi!

Thank you for the report.
I could not repeat this issue with 5.7.8 with dummy data.
Could you please provide more details such as exact 5.7 version you tried, and repeatable test case (Table Schema, and subset of data and may mark it as private if you prefer) to reproduce this issue at our end?

Thanks,
Umesh

Thank you for your reply

I am using MySql5.7.8

Steps for reproduce the issue

Create two tables 

CREATE TABLE TBLE_1(ID BIGINT NOT NULL,CURRENTTIME DATETIME(6) NOT NULL,VAL DOUBLE) ENGINE = MYISAM;

CREATE TABLE TBLE_2(ID BIGINT NOT NULL,CURRENTTIME DATETIME(6) NOT NULL,VAL DOUBLE) ENGINE = MYISAM;

Create Procedure for insert data

CREATE PROCEDURE INSERTTESTDATA()
BEGIN

   DECLARE i INT;

   SET i = 0;

   label1: LOOP
     SET i = i + 1;
     IF i < 500000 THEN
       INSERT INTO tble_1(ID,CURRENTTIME,VAL) VALUES(1234,now(),2222.3333);
       INSERT INTO tble_2(ID,CURRENTTIME,VAL) VALUES(1234,now(),2222.3333);
       ITERATE label1;
     END IF;
     LEAVE label1;
   END LOOP label1;
END; //
DELIMITER ;

Call Procedure

CALL INSERTTESTDATA();

test union query

SELECT CURRENTTIME, VAL FROM (
SELECT ID,CURRENTTIME,VAL FROM TBLE_1 UNION ALL
SELECT ID,CURRENTTIME,VAL FROM TBLE_2 ) ALLROWS WHERE ALLROWS.ID='1234' ORDER BY ALLROWS.CURRENTTIME;

I am using MySQL 5.7.8
The above query return error 2013, Lost Connection during query

It's your test case 100% repeatable on your environment? . On mys side with current source build isn't repeatable:

| 2015-08-19 08:25:13.000000 | 2222.3333 |
| 2015-08-19 08:25:13.000000 | 2222.3333 |
| 2015-08-19 08:25:13.000000 | 2222.3333 |
| 2015-08-19 08:25:13.000000 | 2222.3333 |
| 2015-08-19 08:25:13.000000 | 2222.3333 |
+----------------------------+-----------+
999998 rows in set (5.13 sec)

mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+-------------------------------------+
| Variable_name           | Value                               |
+-------------------------+-------------------------------------+
| innodb_version          | 5.7.9                               |
| protocol_version        | 10                                  |
| slave_type_conversions  |                                     |
| version                 | 5.7.9                               |
| version_comment         | Source distribution PULL 2015/08/14 |
| version_compile_machine | x86_64                              |
| version_compile_os      | Win64                               |
+-------------------------+-------------------------------------+
7 rows in set, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.7.8-rc-log                 |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set, 1 warning (0.00 sec)

Also not repeatable with current released version. Please provide your my.ini file and description of hardware. Thanks.

| 2015-08-19 09:52:05.000000 | 2222.3333 |
| 2015-08-19 09:52:05.000000 | 2222.3333 |
| 2015-08-19 09:52:05.000000 | 2222.3333 |
| 2015-08-19 09:52:05.000000 | 2222.3333 |
+----------------------------+-----------+
999998 rows in set (5.12 sec)

mysql> SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.7.8-rc                     |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set, 1 warning (0.00 sec)

here is our my.ini file

Attachment: my.ini (application/octet-stream, text), 1.31 KiB.

We replicated this in different machine
OS : windows 10, windows 8.1 
RAM : 32 GB
Processor : intel core i7 3.5GHz

We download and installed : MySQL Community Server 5.7.8 rc

I couldn't repeat even using 8 tables with UNION ALL query. Anyway please attach here you err log file. Thanks.

| 2015-08-19 17:36:39.000000 | 2222.3333 |
| 2015-08-19 17:36:39.000000 | 2222.3333 |
| 2015-08-19 17:36:39.000000 | 2222.3333 |
| 2015-08-19 17:36:39.000000 | 2222.3333 |
| 2015-08-19 17:36:39.000000 | 2222.3333 |
| 2015-08-19 17:36:39.000000 | 2222.3333 |
+----------------------------+-----------+
3999992 rows in set (20.50 sec)

mysql> SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.7.8-rc                     |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set, 1 warning (0.00 sec)

mysql> SHOW TABLES;
+--------------+
| Tables_in_d3 |
+--------------+
| tble_1       |
| tble_2       |
| tble_3       |
| tble_4       |
| tble_5       |
| tble_6       |
| tble_7       |
| tble_8       |
+--------------+
8 rows in set (0.00 sec)

My computer is an Intel i7 16GB RAM.

screenShot

Attachment: ji.png (image/png, text), 17.12 KiB.

Thanks for your kind consideration

UNION ALL queries like

SELECT ID,CURRENTTIME,VAL FROM TBLE_1 UNION ALL
SELECT ID,CURRENTTIME,VAL FROM TBLE_2 

works fine here 

BUT

SELECT CURRENTTIME, VAL FROM (
SELECT ID,CURRENTTIME,VAL FROM TBLE_1 UNION ALL
SELECT ID,CURRENTTIME,VAL FROM TBLE_2 ) ALLROWS WHERE ALLROWS.ID='1234' ORDER BY ALLROWS.CURRENTTIME;

is not working

I tested with:

SELECT CURRENTTIME, VAL FROM (
SELECT ID,CURRENTTIME,VAL FROM TBLE_1 UNION ALL
SELECT ID,CURRENTTIME,VAL FROM TBLE_2 ) ALLROWS WHERE ALLROWS.ID='1234' ORDER BY ALLROWS.CURRENTTIME;

I got the same issue in a different machine

Steps I followed

download - mysql-installer-web-community-5.7.8.0-rc.msi
installed in a different machine mysql server 5.7.8 and workbench 6.3.4
open workbench, 
create new schema, 
create tables, 
load data using procedure
run select query
got same issue again.

result

Attachment: union_all.png (image/png, text), 432.95 KiB.