Curious issue with Oracle UNION and ORDER BY

Question

The following query is perfectly valid in pretty much every database (give or take a dual dummy table), including Oracle:

select 'A' as x from dual union all select 'B'      from dual order by x asc 

Returning:

| X | |---| | A | | B | 

Now this query is still quite standard SQL, but doesn't work on Oracle

select 'A' as x from dual union all select 'B'      from dual union all select 'C'      from dual order by x asc 

I'm getting

ORA-00904: "X": invalid identifier 

This, however, works:

select 'A' as x from dual union all select 'B' as x from dual union all select 'C'      from dual order by x asc 

I've been playing around with this issue and figured out that apparently, at least the first subselect and the second-last (??) subselect need to have a column called x. In the first example, the two subselects seemed to simply coincide. Working example:

select 'A' as x from dual union all select 'B'      from dual union all select 'C'      from dual union all select 'D'      from dual union all select 'E'      from dual union all select 'F' as x from dual union all select 'G'      from dual order by x asc 

As you may have guessed, this wouldn't work:

select 'A' as x from dual union all select 'B'      from dual union all select 'C'      from dual union all select 'D'      from dual union all select 'E' as x from dual union all select 'F'      from dual union all select 'G'      from dual order by x asc 

Interesting side-note:

Derived tables seem not to suffer from this limitation. This works:

select * from (   select 'A' as x from dual union all   select 'B'      from dual union all   select 'C'      from dual ) order by x asc 

Question:

Is this a (known?) bug in the Oracle SQL parser, or is there any very subtle detail in the language syntax that absolutely requires the first and the second-last subselect to hold a column of the name as referenced from the ORDER BY clause?

Answer 1

This doesn't really answer the question, but it seems to be a parser bug (or 'feature') rather than a language requirement.

According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It's also mentioned in community thread 3561546. You need a MOS account, or at least an Oracle account, to see either of those though.

It's also been discussed in an OTN thread which requires a basic Oracle login rather than a MOS account, as far as I can tell. That also doesn't have much information but repeats your findings, and also suggests the behaviour has existed back at least to 9.2.0.8 and perhaps much earlier.

The documentation is a bit vague but doesn't indicate this is expected to be a problem:

For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

You are aliasing your expression and using that, and it doesn't say you have to alias particular components (although of course it doesn't say you don't have to either).

The behaviour seems inconsistent with the alias being valid for the final projection, and the usual rule about the alias only being valid in the order by clause - this seems to be falling down somewhere in between.

Answer 2

This doesn't answer why you are getting inconsistent behavior from your current query, but in Oracle you can easily rewrite as the following (which should never fail with an invalid identifier error):

with t(x) as (   select 'A' from dual   union all   select 'B' from dual   union all   select 'C' from dual ) select * from t order by x asc 

With the added bonus that you only specify the column alias (x) once.