Weihua Zhang created CALCITE-7583:
-------------------------------------
Summary: 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
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)
