Bug #97648 Bug in order by clause in union clause
Submitted: 14 Nov 2019 20:25 Modified: 18 Nov 2019 21:12
Reporter: Andrei Mart Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: union sorting bug

[14 Nov 2019 20:25] Andrei Mart
Description:
I have two sql clauses in union, read the documentation

https://dev.mysql.com/doc/refman/5.7/en/union.html

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

this worked in version 5.6 but not worked in 5.7 and above.
My SQL is - 
( select c.id, c.title, c.price, c.hits from catalog_items c 
inner join catalog_items_category cic on cic.catalog_items_id = c.id where c.is_active = 1 and c.price > 0 and c.quantity > 0 and
 cic.catalog_category_id = 51  order by c.price ASC )
 union  
 ( select c.id, c.title, c.price, c.hits from catalog_items c inner join catalog_items_category cic on cic.catalog_items_id = 
 c.id where c.is_active = 1 and c.quantity <= 0 and cic.catalog_category_id = 51 order by c.price ASC )

As result - two select*s not ordering , but in version 5.6 - it worked

How to repeat:
Crate tables as 
CREATE TABLE `catalog_items` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL, 
  `quantity` INTEGER(11) DEFAULT 0, 
  `is_active` TINYINT(1) UNSIGNED ZEROFILL DEFAULT 0, 
  `hits` INTEGER(11) DEFAULT 0,
  `price` DECIMAL(11,2) DEFAULT 0.00, 
  PRIMARY KEY USING BTREE (`id`)  

) ENGINE=InnoDB
AUTO_INCREMENT=8940 ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

CREATE TABLE `catalog_items_category` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `catalog_items_id` INTEGER(11) DEFAULT NULL,
  `catalog_category_id` INTEGER(11) DEFAULT NULL,
  PRIMARY KEY USING BTREE (`id`),
  KEY `catalog_items_id` USING BTREE (`catalog_items_id`),
 
  CONSTRAINT `catalog_items_category_fk1` FOREIGN KEY (`catalog_items_id`) REFERENCES `catalog_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB
AUTO_INCREMENT=28968 ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

and try this select

( select c.id, c.title, c.price, c.hits from catalog_items c 
inner join catalog_items_category cic on cic.catalog_items_id = c.id where c.is_active = 1 and c.price > 0 and c.quantity > 0 and
 cic.catalog_category_id = 51  order by c.price ASC )
 union  
 ( select c.id, c.title, c.price, c.hits from catalog_items c inner join catalog_items_category cic on cic.catalog_items_id = 
 c.id where c.is_active = 1 and c.quantity <= 0 and cic.catalog_category_id = 51 order by c.price ASC )

As result - two select*s not ordering , but in version 5.6 - it worked
[15 Nov 2019 12:23] MySQL Verification Team
Hello Mr. Mart,

Thank you for your bug report.

However, we do need some additional information in order to process your report.

First of all, you have described two very different queries. Which one is the one that you are reporting. First or second one ???

Next, SQL standard has changed between 5.6 and 5.7. With the UNION as you have written it, each SELECT node is sorted totally separately and then both results are merged through the distinct filter

Next, try UNION ALL and if you get two separately sorted results, displayed one after the other, then this is simply not a bug.

Last, if wish that the entire result is ordered, you should use a different syntax, as described in our Reference Manual.
[15 Nov 2019 16:07] Andrei Mart
Data table file

Attachment: new_table.sql (application/octet-stream, text), 1.63 KiB.

[15 Nov 2019 16:08] Andrei Mart
sql strategment union, result us bug

Attachment: union_strategment.sql (application/octet-stream, text), 286 bytes.

[15 Nov 2019 16:14] Andrei Mart
Get files from attachment's and see result - 
documentation https://dev.mysql.com/doc/refman/5.7/en/union.html
(SELECT id, value FROM new_table WHERE id < 5 ORDER BY value asc)
UNION
(SELECT id, value FROM new_table WHERE id >= 5 ORDER BY value desc);
----------------------
"id"	"value"
"1"	"202"
"2"	"205"
"3"	"100"
"4"	"125"
"5"	"80"
"6"	"210"
"7"	"10"
"8"	"12"
"9"	"18"
"10"	"20"
----------------------
The first set must be sorted in ascending order by value, and the second in descending order by value, as you can see, is incorrect.
For reference - in version 5.6 it's worked. Please fix this bug.
[18 Nov 2019 12:49] MySQL Verification Team
Hi,

You have not answered all of my questions, nor have you followed any of my directions.

Hence, try first UNION ALL , instead of just UNION ..........
[18 Nov 2019 13:28] MySQL Verification Team
Hi,

I am repeating once again. According to SQL standard, if you use UNION (without ALL) then, after each SELECT node has performed its sorting, then UNION will pass both results through DISTINCT filter and destroy all sorting.

Also, you are using node sorting, instead of the sorting for the entire query. Our Reference Manual, contains all of the info.

Last, but not least, please do mind your language.
[18 Nov 2019 14:05] Andrei Mart
(SELECT id, value FROM new_table WHERE id < 5 ORDER BY value asc)
UNION ALL
(SELECT id, value FROM new_table WHERE id >= 5 ORDER BY value desc);

--------------------------------
"id"	"value"
"1"	"202"
"2"	"205"
"3"	"100"
"4"	"125"
"5"	"80"
"6"	"210"
"7"	"10"
"8"	"12"
"9"	"18"
"10"	"20"
--------------------------
USE - UNION ALL
Sorting error anywhere
[18 Nov 2019 14:11] MySQL Verification Team
This truly does not look good.
Can you upload a dump of that table ...

Use "Files" tab ....
[18 Nov 2019 14:38] Andrei Mart
Dump data file is already loaded - title is Data table file
[18 Nov 2019 16:26] MySQL Verification Team
Hi,

Unfortunately, I have repeated your results with both, latest 5.7 and latest 8.0.

Verified as reported.
[18 Nov 2019 18:03] Andrei Mart
Как я понял вы получили также неверный результат сортировки?
What do I do next?
[18 Nov 2019 19:19] Andrei Mart
what I have to do next?
[18 Nov 2019 21:12] Andrei Mart
Please note that according to the reference manual  document 5.7  https://dev.mysql.com/doc/refman/5.7/en/union.html using the following type of structure  (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)

UNION

(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);  is pointless according to your email dated 18 november,13:28 because as you have stated the sorting results are destroyed by the "distinct" filter. What is the point of sorting nodes the way it is described in the document if without using "UNION ALL" the sorting results will be destroyed by the "distinct" filter. But as you can see even when "UNION ALL" is used the nodes sorting still does not work.
[19 Nov 2019 13:26] MySQL Verification Team
What do you have to do next ???? 

Nothing !!!

When bug is verified, it is transferred to the Development team, which will make decision on the scheduling of the bug fix. We are not involved in the process, so we can not tell you when is scheduling done and which version / release will get a fix. We simply do not have access to that process, as it is internal matter within Development.