[
https://issues.apache.org/jira/browse/CALCITE-5799?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18040838#comment-18040838
]
Zhen Chen edited comment on CALCITE-5799 at 11/26/25 2:38 PM:
--------------------------------------------------------------
I tested this test case on the current main branch, and it works. In the
description in JIRA, they used {{{}UNNEST(n2.d){}}}, but when changed to
{{{}UNNEST((n2).d){}}}, the result becomes correct. This behavior is also
consistent with PostgreSQL.
Quidem test:
{code:java}
!use blank
!set outputformat mysql
# Create a table whose column `n2` is a ROW with a field `d` that is an ARRAY
create table MyTable (
a int,
n2 row(d integer array),
e int
);
(0 rows modified)
!update
insert into MyTable values
(1, row(array[10, 20, 30]), 100),
(2, row(array[40, 50]), 200),
(3, row(array[60]), 300);
(3 rows modified)
!update
# Cross join table with UNNEST on the nested field `n2.d` and show results
select a, x
from MyTable, UNNEST((n2).d) as x;
+---+----+
| A | X |
+---+----+
| 1 | 10 |
| 1 | 20 |
| 1 | 30 |
| 2 | 40 |
| 2 | 50 |
| 3 | 60 |
+---+----+
(6 rows)
!ok {code}
pgsql test:
{code:java}
CREATE TYPE nested_type AS (
d INTEGER[]
);
CREATE TABLE MyTable (
a INT,
n2 nested_type,
e INT
);
INSERT INTO MyTable (a, n2, e) VALUES
(1, ROW(ARRAY[10, 20, 30]), 100),
(2, ROW(ARRAY[40, 50]), 200),
(3, ROW(ARRAY[60]), 300);
SELECT a, x
FROM MyTable,
UNNEST((n2).d) AS x;
# result
a | x
---+----
1 | 10
1 | 20
1 | 30
2 | 40
2 | 50
3 | 60
(6 rows){code}
I consider this a usage error, so I'm now marking it as resolved.
was (Author: jensen):
I tested this test case on the current main branch, and it works. In the
description in JIRA, they used {{{}UNNEST(n2.d){}}}, but when changed to
{{{}UNNEST((n2).d){}}}, the result becomes correct. This behavior is also
consistent with PostgreSQL.
Quidem test:
{code:java}
!use blank
!set outputformat mysql
# Create a table whose column `n2` is a ROW with a field `d` that is an ARRAY
create table MyTable (
a int,
n2 row(d integer array),
e int
);
(0 rows modified)
!update
insert into MyTable values
(1, row(array[10, 20, 30]), 100),
(2, row(array[40, 50]), 200),
(3, row(array[60]), 300);
(3 rows modified)
!update
# Cross join table with UNNEST on the nested field `n2.d` and show results
select a, x
from MyTable, UNNEST((n2).d) as x;
+---+----+
| A | X |
+---+----+
| 1 | 10 |
| 1 | 20 |
| 1 | 30 |
| 2 | 40 |
| 2 | 50 |
| 3 | 60 |
+---+----+
(6 rows)
!ok {code}
pgsql test:
{code:java}
CREATE TYPE nested_type AS (
d INTEGER[]
);
CREATE TABLE MyTable (
a INT,
n2 nested_type,
e INT
);
INSERT INTO MyTable (a, n2, e) VALUES
(1, ROW(ARRAY[10, 20, 30]), 100),
(2, ROW(ARRAY[40, 50]), 200),
(3, ROW(ARRAY[60]), 300);
SELECT a, x
FROM MyTable,
UNNEST((n2).d) AS x;
# result
a | x
---+----
1 | 10
1 | 20
1 | 30
2 | 40
2 | 50
3 | 60
(6 rows){code}
I will also submit a PR for reference.
> unnest(a) is wrong if a is structKind.PEEK_FIELDS_NO_EXPAND
> ------------------------------------------------------------
>
> Key: CALCITE-5799
> URL: https://issues.apache.org/jira/browse/CALCITE-5799
> Project: Calcite
> Issue Type: Bug
> Reporter: Haojin Wang
> Priority: Critical
>
> when I validate sql "select * from MyTable,UNNEST(N2.D)", throw
> "org.apache.calcite.runtime.CalciteContextException: From line 0, column 0 to
> line 1, column 31: Column 'N2.N2' not found in table 'MYTABLE'" I think this
> is weird.
>
> myTable(
> * a: BIGINT,
> * n1: STRUCT<
> * n11: STRUCT<b: BIGINT>,
> * n12: STRUCT<c: BIGINT>
> * >,
> * n2: STRUCT<d: Array>,
> * e: BIGINT)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
