[
https://issues.apache.org/jira/browse/CALCITE-4305?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4305:
---------------------------------
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:
{code}
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;
{code}
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:
{code}
SELECT fruit.*
FROM (SELECT * FROM UNNEST(array ['apple', 'banana', 'pear']) as x) as fruit;
x
======
apple
banana
pear
{code}
BigQuery has [similar
behavior|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#any_value].
was:
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:
{code}
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;
{code}
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:
{code}
SELECT fruit.*
FROM (SELECT * FROM UNNEST(array ['apple', 'banana', 'pear']) as x) as fruit;
x
======
apple
banana
pear
{code}
> Implicit column alias for single-column UNNEST
> ----------------------------------------------
>
> Key: CALCITE-4305
> URL: https://issues.apache.org/jira/browse/CALCITE-4305
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> 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:
> {code}
> 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;
> {code}
> 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:
> {code}
> SELECT fruit.*
> FROM (SELECT * FROM UNNEST(array ['apple', 'banana', 'pear']) as x) as fruit;
> x
> ======
> apple
> banana
> pear
> {code}
> BigQuery has [similar
> behavior|https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#any_value].
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
