Here is a query that should work as written, but instead it throws an exception. Is this a parser bug? Is it a well-known one and I just didn't Google for it the right way? (I don't have access to the bug database.)
NOTE: I am not asking how to get a working query - that's trivial; I am asking whether this is a bug or whether my understanding of how things should work is incorrect.
SQL> select 'f' as col from dual union all
2 select 'b' from dual union all
3 select 'z' from dual
4 order by col;
order by col
*
ERROR at line 4:
ORA-00904: "COL": invalid identifier
My expectation:
First, in a UNION ALL the column aliases must be present in the first branch, and any aliases given in the other branches are optional (and they are ignored if they are present).
Second, even without "parentheses" (which here would mean a subquery for the UNION ALL, with ORDER BY in the outer query), ORDER BY placed after UNION ALL will apply to the entire result set of UNION ALL (and not just to the last branch of the UNION ALL operation).
So, this query should compute the UNION ALL first, it should use the column aliases from the first branch, and then do the ordering. But it doesn't. Why not?
It appears that the parser looks at the second-to-last branch of UNION ALL for the column alias. (I tested queries with more than three branches to the UNION ALL, and in all cases the second-to-last branch alias is the one that matters.) As demonstrated below, if I add the column alias to the value 'b' then the query will work. However, if I add it to the LAST branch of ORDER BY but I don't add it to the second-to-last one, the query will fail. It also fails if I add a different column alias to the second-to-last branch (even though without the ORDER BY, the result of UNION ALL is as expected in that case).
Query works OK if the middle branch of UNION ALL has the right alias (Explain Plan shown later):
SQL> select 'f' as col from dual union all
2 select 'b' as col from dual union all
3 select 'z' from dual
4 order by col;
COL
---
b
f
z
3 rows selected.
The alias must really be given to the second-to-last branch; giving it only to the last branch doesn't help:
select 'f' as col from dual union all
select 'b' from dual union all
select 'z' as col from dual
order by col;
order by col
*
ERROR at line 4:
ORA-00904: "COL": invalid identifier
Neither does a different column alias given to the second-to-last branch:
SQL> select 'f' as col from dual union all
2 select 'b' as zzz from dual union all
3 select 'z' as col from dual
4 order by col;
order by col
*
ERROR at line 4:
ORA-00904: "COL": invalid identifier
Query without ORDER BY works as expected:
SQL> select 'f' as col from dual union all
2 select 'b' as zzz from dual union all
3 select 'z' as col from dual
4 ;
COL
---
f
b
z
3 rows selected.
By the way, here is the Explain Plan for the working ORDER BY query - it shows clearly that ORDER BY is done after the complete UNION ALL result is calculated:
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 7 (15)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 6 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
| 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
One more thing - if I have two columns, and in the second-to-last branch of UNION ALL I use the ORDER BY column alias, but I give it to the wrong column, the query will work! Which confirms to me that this must be a parser bug. Indeed, the query SHOULD work (the aliases given in any branches other than the first are ignored - and indeed they are); but the parser actually does look at the aliases in the second-to-last branch, it sees that "col" is present (it doesn't care that it's inconsistent with which column that alias was given to in the first branch), it allows the query to be sent to the execution engine, and the execution is in fact 100% as expected.
SQL> select 'f' as col, 10 as nbr from dual union all
2 select 'b' , 12 as col from dual union all -- ignored by the runtime; here just to fool the parser
3 select 'z' , 20 from dual
4 order by col;
COL NBR
--- --------------
b 12.00
f 10.00
z 20.00
3 rows selected.