Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.9, 10.3.10, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
Windows 64bit
Description
A SELECT from a subquery containing a UNION with inconsistent result column names fails with an ill-fitting error message, but only when used with a WHERE clause.
This is a heavily striped down version of a query that fails:
select * from (
|
select 1 x
|
union all
|
select sum(1) y
|
) t where x>0
|
yields: ERROR 1054 (42S22): Unknown column 'x' in 'order clause'
which is strange, because there is no order clause.
This slightly different statement, however, works fine:
select * from (
|
select 1 x
|
union all
|
select 2 y
|
) t where x>0
|
(Note: second branch of UNION has no aggregate function)
and yields, as expected:
+---+
|
| x |
|
+---+
|
| 1 |
|
| 2 |
|
+---+
|
Omitting the WHERE clause from the first query allows it to succeed, too.
This seems to be a regression. My original query (much longer) used to work with an older 10.x version, but I cannot reconstruct any more which version that was.
On an old MySQL server 5.7.9, the problem does NOT come up. SQLite also has NO problems with the query, so I assume the query is legal as such, even given the inconsistent naming of columns.
Of course, a workaround is easy: use consistent aliases:
select * from (
|
select 1 x
|
union all
|
select sum(1) x
|
) t where x>0
|
Attachments
Issue Links
- is duplicated by
-
MDEV-24771 Column alias in UNION with GROUP BY query
- Closed