[jira] [Comment Edited] (CALCITE-5126) Implicit column alias for single-column UNNEST should work with any single-column UNNEST’s input

["Dmitry Sysolyatin (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5126?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17555702#comment-17555702 ] 

Dmitry Sysolyatin edited comment on CALCITE-5126 at 6/17/22 4:56 PM:
---------------------------------------------------------------------

[~rubenql] Yes, this feature has been implemented inside  CALCITE-4305 ([c2527cc|https://github.com/apache/calcite/commit/c2527ccf440f7750bfbabd2063c402440c5b32a0]). But for some reason a restriction was added that implicit column alias works only if argument of UNNEST is scalar array or scalar multiset, but it should work with any single column UNNEST argument. I just removed this restriction, please check my PR, it is only few lines of changes.


was (Author: dmsysolyatin):
[~rubenql] Yes, this feature has been implemented inside  CALCITE-4305 ([c2527cc|https://github.com/apache/calcite/commit/c2527ccf440f7750bfbabd2063c402440c5b32a0]). But for some reason a restriction was added that implicit column alias works only if argument of UNNEST scalar array or scalar multiset, but it should work with any single column UNNEST argument. I just removed this restriction, please check my PR, it is only few lines of changes.

> 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
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.31.0
>
>
> 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)