SQL & PL/SQL

Good question, it does look a little like a bug.

However, I would argue that if you're going to order a result of a union it should be wrapped in an outer query so it's clear that the ordering is being done on the overall results and not attempting to order just the last query (which is probably where it's getting confused internally, though not sure why it doesn't get confused with just the two unioned queries)

Edit to show my test in the same version...

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


SQL> select 1 a from dual
  2  union
  3  select 2 from dual
  4  union
  5  select 3 from dual
  6  order by a
  7  ;
order by a
         *
ERROR at line 6:
ORA-00904: "A": invalid identifier


SQL> ed
Wrote file afiedt.buf

  1  select 1 a from dual
  2  union
  3  select 2 a from dual
  4  union
  5  select 3 a from dual
  6* order by a
SQL> /

         A
----------
         1
         2
         3

SQL> ed
Wrote file afiedt.buf

  1  select a from (
  2  select 1 a from dual
  3  union
  4  select 2 from dual
  5  union
  6  select 3 from dual
  7  )
  8* order by a
SQL> /

         A
----------
         1
         2
         3

SQL> ed
Wrote file afiedt.buf

  1  select 1 a from dual
  2  union
  3  select 2 from dual
  4* order by a
SQL> /

         A
----------
         1
         2

Hi,

If you want to use then you can use it like :-

SQL> select a from (
  2  select 1 a from dual
  3  union
  4  select 2 from dual
  5  union
  6  select 3 from dual) result
  7  order by a
  8  /

         A
----------
         1
         2
         3

Yes, but that's not the OP's question. The question is why Oracle is being inconsistent. If it has an issue with 3 unioned queries, why does it not have an issue with 2 unioned queries?

"interestingly" it seems to require the the first and penultimate set of columns are aliased (if there are no subqueries):

select * from v$version;

BANNER
_______________________________________________________________________________
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

  1  select 1 a from dual
  2  union all
  3  select 2   from dual
  4  union all
  5  select 3   from dual
  6  union all
  7  select 4    from dual
  8  union all
  9  select 5   from dual
 10  union all
 11  select 6  from dual
 12  union all
 13  select 7  from dual
 14  union all
 15  select 8 a from dual
 16  union all
 17  select 9  from dual
 18* order by a;

         A
__________
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

  1  select 1 a from dual
  2  union all
  3  select 2   from dual
  4  union all
  5  select 3   from dual
  6  union all
  7  select 4    from dual
  8  union all
  9  select 5   from dual
 10  union all
 11  select 6  from dual
 12  union all
 13  select 7 a from dual
 14  union all
 15  select 8  from dual
 16  union all
 17  select 9  from dual
 18* order by a;
order by a
         *
ERROR at line 18:
ORA-00904: "A": invalid identifier

  1  select 1 a from dual
  2  union all
  3  select 2   from dual
  4  union all
  5  select 3   from dual
  6  union all
  7  select 4    from dual
  8  union all
  9  select 5   from dual
 10  union all
 11  select 6  from dual
 12  union all
 13  select 7  from dual
 14  union all
 15  select 8  from dual
 16  union all
 17  select 9 a from dual
 18* order by a;
order by a
         *
ERROR at line 18:
ORA-00904: "A": invalid identifier

  1  select 1 a from dual
  2  union all
  3  select 2   from dual
  4  union all
  5  select 3   from dual
  6  union all
  7  select 4    from dual
  8  union all
  9  select 5   from dual
 10  union all
 11  select 6  from dual
 12  union all
 13  select 7  from dual
 14  union all
 15  select 8 a from dual
 16  union all
 17  select 9 a from dual
 18* order by a;

         A
__________
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

Well that's completely illogical, and I would say a fault in the query parser if it cannot provide consistency (apart from being consistently the first and penultimate ones)

Hi,

Consider the code, Oracle doesn't know if the order by is part of your second query, or the last line of the entire query. Neither does anyone else looking at it. The result is ambiguous code. If a human can't decide, a parser certainly can't.

PL/SQL is a great tool but it doesn't like ambiguity, this is probably why PL/SQL works so well.

Rewrite your query like this and you may see what I mean by ambiguity....

SELECT field1 FROM table1 
UNION 
SELECT field1 FROM table2 ORDER BY field1;  

Some would say that Oracle should determine that the Order by is useless within a union such as this, some would disagree.

Try this instead....

SELECT * from 
   ( 
   SELECT field1 FROM table1 
   UNION 
   SELECT field1 FROM table2  
   ) 
ORDER BY field1;  

Yes, but as already shown above, there is an inconsistency in the way it handles it. It should either throw an error if you include an order by clause when there is queries unioned together, or it should make the assumption that the order by has to apply to the overall results, but instead it's doing half of the examples one way and half the other way.

Yes, personally, I'd wrap my unions in a query and order in that outer query, to remove any ambiguity in reading the code, but that doesn't negate the fact that oracle is not acting consistently.

That's what gurus are saying:
If you combine a series of three or more SELECT statements with set operators, your ORDER BY clause must be the final clause, and can only specify columns by name if it uses the column names from the very first SELECT statement, regardless of how many SELECT statements might be connected with set operators.
The reality is different.:)

Edited by: 973689 on Nov 28, 2012 4:38 AM

One thing that is consistent is that it does exactly the same on 10.2.0.5 (just tested it myself)

Hi,

this works in my SQLNavigator

select 1 a from dual
union 
select 2 from dual
union 
select 3 from dual
order by 1; 

Regards

stratmo

No, that's not the original query. You are not ordering by the alias "a" which is the issue being discussed.

Blu:

As far as I can recall, it has always behaved that way. The oldest I have available now is 9.2.0.8, and it behaves the same way. I know that 8.1 did also, and I am fairly sure that 7 did as well.

I dimly recall a thread here many many years ago around the issue. As I recall, the consensus was that it is because of the way the statement is parsed, and when names are assigned to the columns in the projection.

John

Maybe it has been there all the time. Just seems odd that it's the first and penultimate queries that require an alias to make it work. Certainly it's related to the way it's parsed, but I know when I wrote a language parser myself, that would have been seen as a fault. It's certainly an interesting one, though fortunately it's also something that can be worked around easily enough. :)

Hi, Almost; as Bencol pointed out, the column(s) named in the ORDER BY clause must have the same alias(es) in the next-to-last branch, that is, the sub-query immediately before the last set operator. As Blushadow said, that seems to be a bug. Aside from Bencol's point, do you still think so? Do you have an example that doesn't follow what you said above, and isn't explained by Bencol?

Well, the first query needs an alias because that is ultimately where the column name for the projection comes from.

SQL> select 1 a from dual
  2  union
  3  select 2 b from dual
  4  union
  5  select 3 c from dual;

         A
----------
         1
         2
         3

As to why it works when the penultimate one has the same alias, I have no idea, except that the parsing of the statment seems to be done sorta kinda from the bottom up.

SQL> select a
  2  from (select 1 a from dual
  3        union
  4        select 2 from dual
  5        union
  6        select 3 from dual)
  7  order by a, b, c;
order by a, b, c
               *
ERROR at line 7:
ORA-00904: "C": invalid identifier

SQL> select a
  2  from (select 1 a from dual
  3        union
  4        select 2 from dual
  5        union
  6        select 3 from dual)
  7  order by a, c, b;
order by a, c, b
               *
ERROR at line 7:
ORA-00904: "B": invalid identifier

But, as you say "It's certainly an interesting one" :-)

John