[MDEV-24771] Column alias in UNION with GROUP BY query Created: 2021-02-02  Updated: 2021-02-03  Resolved: 2021-02-03

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.3.25
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elias Villiger Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: alias, group, union
Environment:

Ubuntu 20.04.1

Issue Links:
Duplicate
duplicates MDEV-19179 Regression: SELECT ... UNION ... with... Closed

 Description   

Simplified sample query
SELECT greet FROM (SELECT 'hi' AS greet
UNION SELECT 'hello'
FROM (SELECT '') AS _ GROUP BY NULL) AS tbl WHERE greet != ''

Actual result
[mysqli: 1054] Unknown column 'greet' in 'order clause'

Expected result
Query passes (returns 'hihello')

Additional info
The following changes each make the query pass:

  • Change line 2 to UNION SELECT 'hello' AS greet
  • Remove the GROUP BY NULL clause

 Comments   
Comment by Alice Sherepa [ 2021-02-03 ]

Thank you for the report!
It was fixed by commit 1248c654c494df6d (MDEV-19179, the patch is in 10.3.28), on current 10.2-10.5 results are as expected. A temporary workaround for the issue:

MariaDB [test]> SELECT greet FROM (SELECT 'hi' AS greet
 
    -> UNION SELECT 'hello'
 
    -> FROM (SELECT '') AS _ GROUP BY NULL) AS tbl WHERE greet != ''
 
    -> ;
 
ERROR 1054 (42S22): Unknown column 'greet' in 'order clause'
 
MariaDB [test]> set optimizer_switch='condition_pushdown_for_derived=off';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> SELECT greet FROM (SELECT 'hi' AS greet UNION SELECT 'hello' FROM (SELECT '') AS _ GROUP BY NULL) AS tbl WHERE greet != '';
 
+-------+
 
| greet |
 
+-------+
 
| hi    |
 
| hello |
 
+-------+
 
2 rows in set (0.000 sec)

Generated at Thu Feb 08 09:32:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.