[
https://issues.apache.org/jira/browse/CALCITE-7583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18089553#comment-18089553
]
Mihai Budiu commented on CALCITE-7583:
--------------------------------------
Yes, apparently zip is the SQL standard definition. According to these clever
agents, the standard says:
>From {_}ISO/IEC 9075‑2:2011 (SQL/Foundation){_}, §6.27 {{{}<collection derived
>table>{}}}:
{quote}*“If more than one collection expression is specified, then the result
is formed by pairing the elements of the collections positionally.” “If the
collections do not have the same cardinality, then the shorter collections are
extended with null values.”*
{quote}
And apparently SQL 2023 keeps the same definition.
Also, apparently few SQL engines support the multi-argument UNNEST.
> 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)
