It can't sort multiple UNION sql-CodePudding

I tried this sql by Oracle19c. It was an error. (ORA-00904: "A": invalid identifier) Why is it?

SELECT a FROM t1
UNION
SELECT b FROM t2
UNION
SELECT c FROM t3
ORDER BY a;

This sql worked correctly.

SELECT a FROM t1
UNION
SELECT b FROM t2
ORDER BY a;

SELECT a FROM t1
UNION
SELECT b FROM t2
UNION
SELECT c FROM t3
ORDER BY 1;

SELECT
 A
FROM (
  SELECT a FROM t1
  UNION
  SELECT b FROM t2
  UNION
  SELECT c FROM t3
)
ORDER BY A
;

I tried this sql and checked the result. The column name of this result is "A", so I think it is possible to sort by "a".

SELECT a FROM t1
UNION
SELECT b FROM t2
UNION
SELECT c FROM t3
;

I searched on Internet, but I couldn't find any reason.

CodePudding user response：

I had the same question five years ago, asked on what was then the Oracle Technology Network (the name has changed, the forum software changed a couple of years ago, fatally destroying all formatting...) anyway, here:

https://community.oracle.com/tech/developers/discussion/4025246/parser-bug-order-by-with-union-all-and-column-aliases

This is a known bug, see the answer from Paulzip:

It was logged years ago as bug 14196463 and closed without resolution.

and the further clarification in CarlosDLG's answer (the last one in the thread).

You can see a few weird things you can do with this in my original post (question) in that thread.

Short summary: the result set will use the alias you give for the first branch of UNION [ALL] and ignore all other aliases. In your case you didn't give any alias, in any member of the three-way UNION, so the result set will use the first column name.

However, for reasons known only to Oracle, the ORDER BY is referencing the alias given in the second-to-last member of the multiple UNION. That alias must exist, and it must also match the alias given to the first member of the UNION. So, weirdly (a bug!), you must give an alias to the column, at least in the first and also in the second-to-last members of UNION, and the alias must be the same.

Other than that, you can use the other solutions as workaround (for example wrap the entire UNION as a subquery and put ORDER BY in the outer query). What seems to make the most sense, though - altough it is more typing - is to give the column an alias - the same alias - in every member of the UNION.