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
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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 |
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: {noformat} select * from ( select 1 x union all select sum(1) y ) t where x>0 {noformat} 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: {noformat} select * from ( select 1 x union all select 2 y ) t where x>0 {noformat} (Note: second branch of UNION has no aggregate function) and yields, as expected: {noformat} +---+ | x | +---+ | 1 | | 2 | +---+ {noformat} 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: {noformat} select * from ( select 1 x union all select sum(1) x ) t where x>0 {noformat} |
| Assignee | Alice Sherepa [ alice ] |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Alice Sherepa [ alice ] | Igor Babaev [ igor ] |
| Assignee | Igor Babaev [ igor ] | Galina Shalygina [ shagalla ] |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Assignee | Galina Shalygina [ shagalla ] | Igor Babaev [ igor ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Affects Version/s | 10.5 [ 23123 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Igor Babaev [ igor ] | Oleksandr Byelkin [ sanja ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Priority | Critical [ 2 ] | Blocker [ 1 ] |
| Priority | Blocker [ 1 ] | Major [ 3 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Igor Babaev [ igor ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Component/s | Optimizer [ 10200 ] | |
| Component/s | Data Manipulation - Subquery [ 10107 ] | |
| Fix Version/s | 10.2.37 [ 25112 ] | |
| Fix Version/s | 10.3.28 [ 25111 ] | |
| Fix Version/s | 10.4.18 [ 25110 ] | |
| Fix Version/s | 10.5.9 [ 25109 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Link |
This issue is duplicated by |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 94262 ] | MariaDB v4 [ 156036 ] |
| Zendesk Related Tickets | 106060 |
Thanks! Repeatable on 10.2-10.4