[
https://issues.apache.org/jira/browse/CALCITE-7583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18086233#comment-18086233
]
Weihua Zhang edited comment on CALCITE-7583 at 6/5/26 4:05 AM:
---------------------------------------------------------------
[~julianhyde]
I believe the current behavior of multi-argument `UNNEST` may be incorrect.
The implementation introduced by commit
`070ceb0acfcf42a4817823320048219803537e8e` appears to support multiple `UNNEST`
arguments by reusing `product(...)`, which is backed by
`CartesianProductEnumerator`. As a result, `UNNEST(a, b)` produces the
Cartesian product of the inputs.
However, PostgreSQL and the SQL standard semantics for multi-argument `UNNEST`
are zip-longest: rows are matched by position, the number of output rows is the
maximum cardinality of the input collections, and shorter inputs are padded
with `NULL`. `WITH ORDINALITY` should number those zipped rows.
For example:
{code:sql}
SELECT * FROM UNNEST(MAP['a', 1, 'b', 2], ARRAY[5, 6, 7]);
{code}
should produce:
{code:java}
KEY=a; VALUE=1; EXPR$1=5
KEY=b; VALUE=2; EXPR$1=6
KEY=null; VALUE=null; EXPR$1=7
{code}
rather than 6 Cartesian-product rows.
was (Author: JIRAUSER312348):
[~julianhyde]
> UNNEST with multiple array arguments returns wrong result
> ----------------------------------------------------------
>
> Key: CALCITE-7583
> URL: https://issues.apache.org/jira/browse/CALCITE-7583
> Project: Calcite
> Issue Type: Bug
> Reporter: Weihua Zhang
> Priority: Major
>
> core/src/test/java/org/apache/calcite/test/JdbcTest.java
> {code:java}
> @Test void testUnnestArrayScalarArray() {
> CalciteAssert.hr()
> .query("select d.\"name\", e.*\n"
> + "from \"hr\".\"depts\" as d,\n"
> + " UNNEST(d.\"employees\", array[1, 2]) as e")
> .returnsUnordered(
> "name=HR; empid=200; deptno=20; name0=Eric; salary=8000.0;
> commission=500; EXPR$1=1",
> "name=HR; empid=200; deptno=20; name0=Eric; salary=8000.0;
> commission=500; EXPR$1=2",
> "name=Sales; empid=100; deptno=10; name0=Bill; salary=10000.0;
> commission=1000; EXPR$1=1",
> "name=Sales; empid=100; deptno=10; name0=Bill; salary=10000.0;
> commission=1000; EXPR$1=2",
> "name=Sales; empid=150; deptno=10; name0=Sebastian;
> salary=7000.0; commission=null; EXPR$1=1",
> "name=Sales; empid=150; deptno=10; name0=Sebastian;
> salary=7000.0; commission=null; EXPR$1=2");
> }
> /*
> name | empid | deptno | name | salary | commission | unnest
> -----------+-------+--------+-----------+--------+------------+--------
> Sales | 100 | 10 | Bill | 10000 | 1000 | 1
> Sales | 150 | 10 | Sebastian | 7000 | | 2
> Marketing | | | | | | 1
> Marketing | | | | | | 2
> HR | 200 | 20 | Eric | 8000 | 500 | 1
> HR | | | | | | 2
> (6 rows)
> */
> @Test void testUnnestArrayScalarArrayAliased() {
> CalciteAssert.hr()
> .query("select d.\"name\", e.*\n"
> + "from \"hr\".\"depts\" as d,\n"
> + " UNNEST(d.\"employees\", array[1, 2]) as e (ei, d, n, s, c,
> i)\n"
> + "where ei + i > 151")
> .returnsUnordered(
> "name=HR; EI=200; D=20; N=Eric; S=8000.0; C=500; I=1",
> "name=HR; EI=200; D=20; N=Eric; S=8000.0; C=500; I=2",
> "name=Sales; EI=150; D=10; N=Sebastian; S=7000.0; C=null; I=2");
> }
> /*
> name | ei | d | n | s | c | i
> -------+-----+----+-----------+------+-----+---
> Sales | 150 | 10 | Sebastian | 7000 | | 2
> HR | 200 | 20 | Eric | 8000 | 500 | 1
> (2 rows)
> */
> @Test void testUnnestArrayScalarArrayWithOrdinal() {
> CalciteAssert.hr()
> .query("select d.\"name\", e.*\n"
> + "from \"hr\".\"depts\" as d,\n"
> + " UNNEST(d.\"employees\", array[1, 2]) with ordinality as e
> (ei, d, n, s, c, i, o)\n"
> + "where ei + i > 151")
> .returnsUnordered(
> "name=HR; EI=200; D=20; N=Eric; S=8000.0; C=500; I=1; O=1",
> "name=HR; EI=200; D=20; N=Eric; S=8000.0; C=500; I=2; O=2",
> "name=Sales; EI=150; D=10; N=Sebastian; S=7000.0; C=null; I=2;
> O=4");
> }
> /*
> name | ei | d | n | s | c | i | o
> -------+-----+----+-----------+------+-----+---+---
> Sales | 150 | 10 | Sebastian | 7000 | | 2 | 2
> HR | 200 | 20 | Eric | 8000 | 500 | 1 | 1
> (2 rows)
> */
> @Test void testUnnestItemsInMapWithNoAliasAndAdditionalArgument()
> throws SQLException {
> Connection connection = DriverManager.getConnection("jdbc:calcite:");
> final String sql =
> "select * from unnest(MAP['a', 1, 'b', 2], array[5, 6, 7])";
> ResultSet resultSet = connection.createStatement().executeQuery(sql);
> List<String> map = FlatLists.of("KEY=a; VALUE=1", "KEY=b; VALUE=2");
> List<String> array = FlatLists.of(" EXPR$1=5", " EXPR$1=6", " EXPR$1=7");
> final StringBuilder b = new StringBuilder();
> for (List<String> row : Linq4j.product(FlatLists.of(map, array))) {
> b.append(row.get(0)).append(";").append(row.get(1)).append("\n");
> }
> final String expected = b.toString();
> assertThat(CalciteAssert.toString(resultSet), is(expected));
> connection.close();
> }
> // right res:
> // expected = "KEY=a; VALUE=1; EXPR$1=5\n"
> // + "KEY=b; VALUE=2; EXPR$1=6\n"
> // + "KEY=null; VALUE=null; EXPR$1=7\n";
> {code}
> ref: https://onecompiler.com/postgresql/44rcd9y73
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
