CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-order-byunion-all
Lukas Eder
Lukas Eder

Reputation: 220762

Curious issue with Oracle UNION and ORDER BY

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?

Upvotes: 31

Views: 9704

Answers (2)

tbone
tbone

Reputation: 15473

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.

Upvotes: 8

Alex Poole
Alex Poole

Reputation: 191245

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.

Upvotes: 19

Related Questions