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(EOL)
 - 
    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
 
 -