[CALCITE-4305] Implicit column alias for single-column VALUES, and UNNEST of ARRAY and MULTISET constructors

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.27.0
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 03/Oct/20 18:20
• Updated: 03/Jun/21 22:31
• Resolved: 24/Oct/20 19:58

Description

Single-column UNNEST with a single alias should assign that alias to both a table and the unique column. For example, PostgreSQL returns a column called 'unnest' in the first, and 'fruit' in all of the rest:

select * from UNNEST(array ['apple', 'banana', 'pear']);
select * from UNNEST(array ['apple', 'banana', 'pear']) as fruit;
select * from UNNEST(array ['apple', 'banana', 'pear']) as t(fruit);
select t.* from UNNEST(array ['apple', 'banana', 'pear']) as t(fruit);
select fruit.* from UNNEST(array ['apple', 'banana', 'pear']) as fruit;

Thus FROM UNNEST(...) AS x is creating a table alias x and a column alias x.

This only happens for UNNEST. When aliasing other single-column relations the column name is retained, such as a SELECT-FROM-UNNEST sub-query as follows:

SELECT fruit.*
FROM (SELECT * FROM UNNEST(array ['apple', 'banana', 'pear']) as x) as fruit;

x
======
apple
banana
pear

BigQuery has similar behavior.

As an extension to PostgreSQL and BigQuery, we also derive a column alias for UNNEST of single-column MULTISET and single-column VALUES. Examples:

SELECT f FROM UNNEST(MULTISET ['apple', 'banana', 'pear']) AS f;

SELECT f FROM (VALUES 'apple', 'banana', 'pear') AS f;