[ https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17578918#comment-17578918 ]
Dmitry Sysolyatin commented on CALCITE-4999: -------------------------------------------- Can we merge PR ? I have also fixed alias problem described by you two comments ago inside CALCITE-5126 > ARRAY, MULTISET functions should return a collection of scalars if a > sub-query returns 1 column > ----------------------------------------------------------------------------------------------- > > Key: CALCITE-4999 > URL: https://issues.apache.org/jira/browse/CALCITE-4999 > Project: Calcite > Issue Type: Bug > Components: core > Reporter: Dmitry Sysolyatin > Assignee: Dmitry Sysolyatin > Priority: Major > Labels: pull-request-available > Time Spent: 1h 10m > Remaining Estimate: 0h > > At the moment `array` function returns [RecordType ARRAY] if subquery is > passed like an argument. > {code:java} > SELECT array(select 'toast.' || x from unnest(ARRAY['1','2']) x){code} > But Sql standard says: > {code:java} > 6.38 <array value constructor> > Function > Specify construction of an array. > Format > <array value constructor> ::= > <array value constructor by enumeration> > | <array value constructor by query> > [...] > <array value constructor by query> ::= > ARRAY <table subquery> > Syntax Rules > [...] > 3) If <array value constructor by query> is specified, then > a) The <query expression> QE simply contained in the <table subquery> shall > be of degree 1 (one). Let ET be the declared type of the column in the result > of <query expression>. > b) The declared type of the <array value constructor by query> is array with > element type ET and maximum cardinality equal to the implementation-defined > maximum cardinality IMDC for such array types. > {code} > Proposed solution is to return an array of scalars (e.g. INTEGER ARRAY) when > the query returns 1 column, and continue to return an array of ROW when the > query has 2 or more columns. And do the same for MULTISET. -- This message was sent by Atlassian Jira (v8.20.10#820010)