Bug #57986 ORDER BY clause is not used after a UNION, if embedded in a SELECT
Submitted: 4 Nov 2010 16:46 Modified: 11 Jan 2011 16:51
Reporter: Jan Rusch Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.5.6 OS:Any
Assigned to: Magne Mæhre CPU Architecture:Any
Tags: regression
Triage: Triaged: D2 (Serious)

[4 Nov 2010 16:46] Jan Rusch
Description:
In MySQL 5.1 a SELECT like:

SELECT d1, sort FROM (
(SELECT d1, sort
FROM t1
WHERE d2 = "12345")
UNION
(SELECT d1, sort
FROM t1
WHERE d1 = "12345")
ORDER BY sort)
AS RES

returned a result set ordered by "Sort"

This does not work in 5.5.6. The result is unsorted, the order clause is not executed.

The order clause is honored when used without the embracing select.

How to repeat:
CREATE DATABASE `test`;

DROP TABLE IF EXISTS `test`.`t1`;
CREATE TABLE  `test`.`t1` (
  `d1` varchar(10) NOT NULL DEFAULT '',
  `sort` int(10) unsigned NOT NULL DEFAULT '1',
  `d2` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`d1`,`sort`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test.t1 (d1,sort,d2) VALUES
("12345",1,"12345"),
("12345",2,"12345"),
("12345",3,"12345"),
("123456",1,"123456"),
("123456",2,"123456");

SELECT d1, sort FROM (
(SELECT d1, sort
FROM t1
WHERE d2 LIKE "%12345%")
UNION
(SELECT d1, sort
FROM t1
WHERE d1 LIKE "%12345%")
ORDER BY Sort)
AS RES

5.5.6:

mysql> SELECT d1, sort FROM (
    -> (SELECT d1, sort
    -> FROM t1
    -> WHERE d2 LIKE "%12345%")
    -> UNION
    -> (SELECT d1, sort
    -> FROM t1
    -> WHERE d1 LIKE "%12345%")
    -> ORDER BY Sort)
    -> AS RES;
+--------+------+
| d1     | sort |
+--------+------+
| 12345  |    1 |
| 12345  |    2 |
| 12345  |    3 |
| 123456 |    1 |
| 123456 |    2 |
+--------+------+
5 rows in set (0.00 sec)

5.1.11:

mysql> SELECT d1, sort FROM (
    -> (SELECT d1, sort
    -> FROM t1
    -> WHERE d2 LIKE "%12345%")
    -> UNION
    -> (SELECT d1, sort
    -> FROM t1
    -> WHERE d1 LIKE "%12345%")
    -> ORDER BY Sort)
    -> AS RES;
+--------+------+
| d1     | sort |
+--------+------+
| 123456 |    1 |
| 12345  |    1 |
| 123456 |    2 |
| 12345  |    2 |
| 12345  |    3 |
+--------+------+
5 rows in set (0.00 sec)

Suggested fix:
Make it compatible to 5.1
[4 Nov 2010 17:04] Miguel Solorzano
Thank you for the bug report.

mysql 5.1 >SELECT d1, sort FROM (
    -> (SELECT d1, sort
    -> FROM t1
    -> WHERE d2 LIKE "%12345%")
    -> UNION
    -> (SELECT d1, sort
    -> FROM t1
    -> WHERE d1 LIKE "%12345%")
    -> ORDER BY Sort)
    -> AS RES
    -> ;
+--------+------+
| d1     | sort |
+--------+------+
| 123456 |    1 |
| 12345  |    1 |
| 123456 |    2 |
| 12345  |    2 |
| 12345  |    3 |
+--------+------+
5 rows in set (0.01 sec)

mysql 5.1 >show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.0.12              |
| protocol_version        | 10                  |
| version                 | 5.1.53-Win X64      |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
6 rows in set (0.00 sec)
mysql 5.5 >SELECT d1, sort FROM (
    -> (SELECT d1, sort
    -> FROM t1
    -> WHERE d2 LIKE "%12345%")
    -> UNION
    -> (SELECT d1, sort
    -> FROM t1
    -> WHERE d1 LIKE "%12345%")
    -> ORDER BY Sort)
    -> AS RES
    -> ;
+--------+------+
| d1     | sort |
+--------+------+
| 12345  |    1 |
| 12345  |    2 |
| 12345  |    3 |
| 123456 |    1 |
| 123456 |    2 |
+--------+------+
5 rows in set (0.08 sec)

mysql 5.5 >show variables like "%version%";
+-------------------------+------------------------+
| Variable_name           | Value                  |
+-------------------------+------------------------+
| innodb_version          | 1.1.2                  |
| protocol_version        | 10                     |
| slave_type_conversions  |                        |
| version                 | 5.5.7-rc-Win X64-debug |
| version_comment         | Source distribution    |
| version_compile_machine | x86                    |
| version_compile_os      | Win64                  |
+-------------------------+------------------------+
7 rows in set (0.03 sec)

mysql 5.5 >
[9 Nov 2010 21:56] Øystein Grøvlen
T

revid:martin.hansson@sun.com-20091110125246-wom2bbxt5koahstl
------------------------------------------------------------
revno: 2875.58.1
committer: Martin Hansson <martin.hansson@sun.com>
branch nick: n-mr-bf
timestamp: Tue 2009-11-10 13:52:46 +0100
message:
  Backport of Bug#33204 from mysql-pe to
  mysql-next-mr-bugfixing.
  
  Bug no 32858 was fixed in two different ways in what was
  then called mysql 5.1 and 6.0. The fix in 6.0 was very
  different since bugfix no 33204 was present.  Furthermore,
  the two fixes were not compatible. Hence in order to
  backport Bug#33204 to the 5.1-based mysql-next-mr-bugfixing,
  it was necessary to remove the 5.1 fix of 32858 and apply
  the 6.0 version of the fix.
[9 Nov 2010 22:04] Øystein Grøvlen
The above comment was sent a bit early.  The intention was to say that this regression was introduced by the above mentioned patch with revid martin.hansson@sun.com-20091110125246-wom2bbxt5koahstl.
[12 Nov 2010 9:42] Magne Mæhre
While the reported scenario is not technically a bug, it points to a serious bug.

The example case mentioned in the bug report takes a UNION (DISTINCT) of two SELECTs and sorts the result.   The result is then selected upon.   The SQL standard does not guarantee the sort order of such an operation.  To guarantee the sort order of the final result, an ORDER BY clause would need to be appended to the outer SELECT clause.

However, there is no reason why it shouldn't have the same ordering in this case, and this points to a bug.  The bug is exposed when using it together with a LIMIT clause:

  SELECT d1, sort FROM (
    (SELECT d1, sort FROM t1)
    UNION
    (SELECT d1,  sort FROM t1)
    ORDER BY Sort DESC limit 1)
  AS RES;

  d1	sort
  12345         1
  12345         2
  12345         3
  1234561
  123456	2
[12 Nov 2010 9:45] Magne Mæhre
(The above comment was submitted before I was finished)

With the LIMIT clause, the UNION should have produced only a single row, namely the one with Sort=3.

The bug seems to be that the ORDER BY clause is bound to the second SELECT, instead of to the UNION.
[23 Nov 2010 12:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124734

3387 Magne Mahre	2010-11-23
      Bug#57986 ORDER BY clause is not used after a UNION, 
                if embedded in a SELECT
      
      An ORDER BY clause was bound to the incorrect
      (sub-)statement when used in a UNION context.
      
      In a query like:
       SELECT * FROM a UNION SELECT * FROM b ORDER BY c
      the result of SELECT * FROM b is sorted, and then
      combined with a.  The correct behaviour is that
      the ORDER BY clause should be applied on the
      final set.
      
      In a UNION statement, there will be a select_lex
      object for each of the two selects, and a 
      select_lex_unit object that describes the UNION
      itself.
      
      The bug was caused by using a grammar rule for
      ORDER BY (and LIMIT) that bound these elements
      to thd->lex->current_select, which points to the
      last of the two selects, instead of to the 
      fake_select_lex member of the master select_lex_unit
      object.
[3 Dec 2010 8:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125896

3413 Magne Mahre	2010-12-03
      Bug#57986 ORDER BY clause is not used after a UNION, 
                if embedded in a SELECT
      
      An ORDER BY clause was bound to the incorrect
      (sub-)statement when used in a UNION context.
      
      In a query like:
       SELECT * FROM a UNION SELECT * FROM b ORDER BY c
      the result of SELECT * FROM b is sorted, and then
      combined with a.  The correct behaviour is that
      the ORDER BY clause should be applied on the
      final set.
      
      In a UNION statement, there will be a select_lex
      object for each of the two selects, and a 
      select_lex_unit object that describes the UNION
      itself.
      
      The bug was caused by using a grammar rule for
      ORDER BY (and LIMIT) that bound these elements
      to thd->lex->current_select, which points to the
      last of the two selects, instead of to the 
      fake_select_lex member of the master select_lex_unit
      object.
     @ sql/sql_yacc.yy
        Need to use (opt_)union_order_or_limit to
        bind to the correct select_lex object.
[5 Dec 2010 22:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126084

3413 Magne Mahre	2010-12-05
      Bug#57986 ORDER BY clause is not used after a UNION, 
                if embedded in a SELECT
            
      An ORDER BY clause was bound to the incorrect
      (sub-)statement when used in a UNION context.
            
      In a query like:
      SELECT * FROM a UNION SELECT * FROM b ORDER BY c
      the result of SELECT * FROM b is sorted, and then
      combined with a.  The correct behaviour is that
      the ORDER BY clause should be applied on the
      final set.   Similar behaviour was seen on LIMIT
      clauses as well.
            
      In a UNION statement, there will be a select_lex
      object for each of the two selects, and a 
      select_lex_unit object that describes the UNION
      itself.  Similarly, the same behaviour was also
      seen on derived tables.
            
      The bug was caused by using a grammar rule for
      ORDER BY and LIMIT that bound these elements
      to thd->lex->current_select, which points to the
      last of the two selects, instead of to the 
      fake_select_lex member of the master select_lex_unit
      object.
     @ sql/sql_yacc.yy
        Need to use (opt_)union_order_or_limit to
        bind to the correct select_lex object.
[17 Dec 2010 12:55] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:magne.mahre@oracle.com-20101205222353-ddd5kmoakemedw07) (merge vers: 5.6.1) (pib:24)
[6 Jan 2011 3:00] Paul Dubois
Bug is not present in any released 5.6.x version. Setting to Need Merge pending push to 5.5.x.
[10 Jan 2011 12:19] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:magne.mahre@oracle.com-20110110121816-ilut6pt0ku25i29p) (version source revid:magne.mahre@oracle.com-20110110121816-ilut6pt0ku25i29p) (merge vers: 5.6.2) (pib:24)
[10 Jan 2011 12:20] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:magne.mahre@oracle.com-20110110121650-6t2jzlc4lyesemxk) (version source revid:magne.mahre@oracle.com-20110110121650-6t2jzlc4lyesemxk) (merge vers: 5.5.9) (pib:24)
[10 Jan 2011 12:47] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:magne.mahre@oracle.com-20110110124553-cehvirmcokxx3hsb) (version source revid:magne.mahre@oracle.com-20110110124553-cehvirmcokxx3hsb) (merge vers: 5.6.2) (pib:24)
[10 Jan 2011 12:47] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:magne.mahre@oracle.com-20110110124312-awlhreokzgvyt6ow) (version source revid:magne.mahre@oracle.com-20110110124312-awlhreokzgvyt6ow) (merge vers: 5.5.9) (pib:24)
[11 Jan 2011 16:51] Paul Dubois
Noted in 5.5.9 changelog.

An ORDER BY clause was bound to the incorrect substatement when used
in UNION context.