Should order-by-clause be able to deduce implicit column names?

Question

Question:
Can order by clause obtain column names when we specify asterisk * instead of column names in a select query?

The following code works, so the answer seems to be "yes":

select * from dual
order by dummy

The following code does not work, so the answer seems to be "no":

select * from dual
union all
select * from dual
order by dummy

Is this behaviour documented?

Answer 1

In the SQL Reference Manual, under "Sorting Query Results":

• 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.

So in your case

select * from dual
union all
select * from dual
order by 1

works as expected, as does

select dummy from dual
union all
select dummy from dual
order by dummy