[CALCITE-5126] Implicit column alias for single-column UNNEST should work with any single-column UNNEST’s input

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.32.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Dmitry Sysolyatin
• Reporter: Dmitry Sysolyatin
• Created: 02/May/22 09:27
• Updated: 10/Sep/22 08:40
• Resolved: 24/Aug/22 03:45

Description

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”:

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.

From BigQuery documentation (https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#implicit_aliases):

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

Examples:

psql => SELECT * FROM UNNEST(array(select 1 as x)) y;
 y
---
 1
(1 row)

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)

Issue Links

• is related to: CALCITE-4999 ARRAY, MULTISET functions should return a collection of scalars if a sub-query returns 1 column
• links to: GitHub Pull Request #2789
• links to: PR 2789