Is there a generic workaround to express a derived column list in Oracle (and MySQL)?

Question

Many SQL databases support what the SQL standard calls a <derived column list>. Such databases include at least CUBRID, Derby, Firebird, HSQLDB, Postgres, SQL Server, and Sybase SQL Anywhere. A (simplified) extract from the SQL:2008 specification

7.6 <table reference>

Format
<table reference> ::=
    <table or query name> [ [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ] ]
  | <derived table> [ AS ] <correlation name>
      [ <left paren> <derived column list> <right paren> ]

This means, I can express things like these (e.g. in Postgres, which is pretty standards-compliant)

-- Rename a <table or query name> to u(b)
with t(a) as (select 1)
select * from t as u(b)

-- Rename a <derived table> to u(b)
select * from (select 1) as u(b)

Now, according to the Oracle documentation, I can't rename columns using a <derived column list> specification. I could of course rename tables and columns separately, like this:

-- Rename a <table or query name> to u(b)
with t(a) as (select 1 from dual)
select u.a b from t u;

-- Rename a <derived table> to u(b)
select u.a b from (select 1 a from dual) u;

But this requires more knowledge about the derived table (actual column names) than the previous syntax. Also, the renamed columns would only be available after the projection (e.g. in the ORDER BY clause), not in any other clauses, including the projection itself.

Is there a more generic way to rename tables AND columns the way the SQL standard suggests, in Oracle (and also MySQL)? In particular, this could be useful for things like array unnesting, pivot/unpivot table renaming, inlining complex subqueries, renaming results from table functions, etc.

N.B: Please do not focus on the above examples too much. They're really just here to illustrate the problem. Real-world queries are much more complex, so I'm looking for a very general way to implement renaming to u(b)

NOTE: I'm still looking for a solution that works on a database like MySQL. A related question:
How to select an unaliased numeric literal from a sub-select

Answer 1

For a MySQL solution, you could use a UNION to set the names of all the columns in a zero-row query term, and then subsequently query something more complex:

SELECT null AS a, null AS b, null AS c FROM dual WHERE false
UNION ALL
SELECT <expr>, <expr>, <expr>
FROM <realtable>...

Only the first query term of a UNION defines the column names of the whole query. Column names (or lack thereof) in subsequent query terms don't affect the ultimate column names.

You do need to know the number of columns, but it should be pretty easy to keep the two query terms separate. As far as I know, it works in both Oracle and MySQL (however, I have only tested it in MySQL, not in Oracle).

Answer 2

Since you MUST know the number of columns, but not necessarily the column names, you can use the WITH clause to rename these columns as you wish. For example (WITH works in Oracle and SQL Server, don't have MySQL instance handy):

WITH t(x,y,z) as (select * from TABLE(fn_returning_xcols(3)))
select * from t;

Here we don't know the column names in the inner select, but we can rename them in outer WITH clause.

Another example using a PIVOT in Oracle:

WITH t(a,b,c,d,e) as 
(
 select * from 
 (
  select level as levl from dual connect by level <= 5
 )
 PIVOT(max(levl) as l for levl in (1,2,3,4,5))
)
select * from t;

Again, we don't care what the inner select column names are (the inner pivot creates somewhat odd column names), we just need to know how many columns and we can rename.