Bug #72189 SELECT with alias not working in 5.7

• Submitted: 1 Apr 2014 15:41
• Modified: 11 Jun 2014 16:21
• Reporter: Jan Rusch
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.7.4
• OS: Any (MS Windows, Linux)
• CPU Architecture: Any
• Tags: regression

[1 Apr 2014 15:41] Jan Rusch

Description:
A SELECT statement using a subselect with UNION and ORDER BY does not allow to use an alias in the outer statement. This works in 5.6.17 and older.

How to repeat:
Execute the following script in 5.7.4 and 5.6.17:

CREATE DATABASE `test`;

USE `test`;

CREATE TABLE `a` (
  `ida` int(11) NOT NULL,
  PRIMARY KEY (`ida`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
  `idb` int(11) NOT NULL,
  PRIMARY KEY (`idb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `c` (
  `idc` int(11) NOT NULL,
  PRIMARY KEY (`idc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT * FROM
((SELECT ida AS d
FROM a
WHERE ida = 1)
UNION
(SELECT idb AS d
FROM b
WHERE idb = 1)
ORDER BY d
) AS RES, c
WHERE RES.d = c.idc
GROUP BY RES.d

[1 Apr 2014 16:56] Sveta Smirnova

Thank you for the report.

Verified as described.

[2 Apr 2014 6:42] Øystein Grøvlen

A work-around is to remove the ORDER BY in the subquery.  
ORDER BY in subqueries has anyway no guaranteed effect on the order of the final result.

[2 Apr 2014 8:38] Jan Rusch

Thanks for the workaround, but leaving out the ORDER BY is not an option, if you have a running application and dont want to change a lot of statements.

[11 Jun 2014 16:21] Paul DuBois

Noted in 5.7.5 changelog.

A SELECT statement using a subquery with UNION and ORDER BY did not
permit use of an alias in the outer statement.