MySQL Bugs: #57986: ORDER BY clause is not used after a UNION, if embedded in a SELECT

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

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

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 >

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.

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.

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

(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.

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.

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.

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.

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)

Bug is not present in any released 5.6.x version. Setting to Need Merge pending push to 5.5.x.

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)

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)

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)

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)

Noted in 5.5.9 changelog.

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