[CALCITE-4999] ARRAY, MULTISET functions should return a collection of scalars if a sub-query returns 1 column

• 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: 04/Feb/22 16:19
• Updated: 10/Sep/22 08:40
• Resolved: 30/Aug/22 07:04

Description

At the moment `array` function returns [RecordType ARRAY] if subquery is passed like an argument.

SELECT array(select 'toast.' || x from unnest(ARRAY['1','2']) x)

But Sql standard says:

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.

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.

Issue Links

• relates to: CALCITE-5126 Implicit column alias for single-column UNNEST should work with any single-column UNNEST’s input
• links to: GitHub Pull Request #2712