Dmitry Sysolyatin created CALCITE-5126:
------------------------------------------
Summary: Implicit column alias for single-column UNNEST should
work with any single-column UNNEST’s input
Key: CALCITE-5126
URL: https://issues.apache.org/jira/browse/CALCITE-5126
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Dmitry Sysolyatin
Assignee: Dmitry Sysolyatin
Implicit column alias works only in case with scalar array, but it should work
with any single-column UNNEST’s input
There are the following lines in PostgresSQL documentation(
[https://www.postgresql.org/docs/current/queries-table-expressions.html])
regarding table functions like “UNNEST”:
{quote}If column aliases are not supplied, then for a function returning a base
data type, the column name is also the same as the function name.
{quote}
>From BigQuery documentation
>([https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#implicit_aliases):]
{quote}For input ARRAYs of most element types, the output of UNNEST generally
has one column. This single column has an optional alias, which you can use to
refer to the column elsewhere in the query
{quote}
Examples:
{code:java}
psql => SELECT * FROM UNNEST(array(select 1 as x)) y;
y
---
1
(1 row)
{code}
{code:java}
CREATE TABLE testtable (
strings text[]
);
INSERT INTO testtable VALUES(ARRAY['1', '2', '3'])
SELECT array(SELECT 'toast' || x FROM unnest(tt.strings) x) FROM testtable as
tt;
array
------------------------
{toast1,toast2,toast3}
(1 row)
{code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
