Jerome Isaac Haltom created CALCITE-7776:
--------------------------------------------
Summary: UNNEST ... WITH ORDINALITY over a column of type ANY
loses the ORDINALITY column
Key: CALCITE-7776
URL: https://issues.apache.org/jira/browse/CALCITE-7776
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Jerome Isaac Haltom
The ANY branch of {{SqlUnnestOperator.inferReturnType}} returns before the
block that adds the ORDINALITY column, so the row type has one column and WITH
ORDINALITY has no effect. {{Uncollect.deriveUncollectRowType}} returns early in
the same way.
Given a table {{s.docs}} with columns {{ID}} INTEGER and {{TAGS}} ANY:
{code:sql}
select "D"."ID", "T"."X", "T"."O"
from "s"."docs" as "D", UNNEST("D"."TAGS") WITH ORDINALITY as "T"("X", "O")
{code}
{noformat}
org.apache.calcite.sql.validate.SqlValidatorException: List of column aliases
must have same degree as table; table has 1 columns ('$unnest'), whereas alias
list has 2 columns
{noformat}
The same query over a declared array gives both columns:
{code:sql}
select * from UNNEST(ARRAY['red', 'green']) WITH ORDINALITY as "T"("X", "O")
{code}
{noformat}
X, O
red|1
green|2
{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
