jinfengni commented on code in PR #3640: URL: https://github.com/apache/calcite/pull/3640#discussion_r1499859569
########## core/src/test/java/org/apache/calcite/test/JdbcTest.java: ########## @@ -8270,6 +8270,75 @@ private void checkGetTimestamp(Connection con) throws SQLException { .returns("EXPR$0=[1, 1.1]\n"); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-6032">[CALCITE-6032] + * NullPointerException in Reldecorrelator for a Multi level correlated subquery</a>. */ + @Test void testMultiLevelDecorrelation() throws Exception { + String hsqldbMemUrl = "jdbc:hsqldb:mem:."; + Connection baseConnection = DriverManager.getConnection(hsqldbMemUrl); + Statement baseStmt = baseConnection.createStatement(); + baseStmt.execute("create table invoice (inv_id integer, col1\n" + + "integer, inv_amt integer)"); + baseStmt.execute("create table item(item_id integer, item_amt\n" + + "integer, item_col1 integer, item_col2 integer, item_col3\n" + + "integer,item_col4 integer )"); + baseStmt.execute("INSERT INTO invoice VALUES (1, 1, 1)"); + baseStmt.execute("INSERT INTO invoice VALUES (2, 2, 2)"); + baseStmt.execute("INSERT INTO invoice VALUES (3, 3, 3)"); + baseStmt.execute("INSERT INTO item values (1, 1, 1, 1, 1, 1)"); + baseStmt.execute("INSERT INTO item values (2, 2, 2, 2, 2, 2)"); + baseStmt.close(); + baseConnection.commit(); + + Properties info = new Properties(); + info.put("model", + "inline:" + + "{\n" + + " version: '1.0',\n" + + " defaultSchema: 'BASEJDBC',\n" + + " schemas: [\n" + + " {\n" + + " type: 'jdbc',\n" + + " name: 'BASEJDBC',\n" + + " jdbcDriver: '" + jdbcDriver.class.getName() + "',\n" + + " jdbcUrl: '" + hsqldbMemUrl + "',\n" + + " jdbcCatalog: null,\n" + + " jdbcSchema: null\n" + + " }\n" + + " ]\n" + + "}"); + + Connection calciteConnection = + DriverManager.getConnection("jdbc:calcite:", info); + + String statement = "SELECT Sum(invoice.inv_amt * (\n" Review Comment: My view is it would be better to use a "minimal" query to reproduce the issue, and verify the fix, in particular in unit testcases. Adding more components in the query will make it hard to understand what kind of features this query uses that actually hit the issue. For instance, the following simplified query would hit the same stack trace: ``` SELECT SUM(p."min_scale" * ( select max(e."salary") from FOODMART."employee" e where e."position_id" = p."position_id" AND e."supervisor_id" = (select MAX(e2."supervisor_id") from FOODMART."employee" e2 WHERE e."position_id" <= p."position_id") )) from FOODMART."position" p; Caused by: java.lang.NullPointerException: cm.mapCorToCorRel.get($cor2) at java.base/java.util.Objects.requireNonNull(Objects.java:336) at org.apache.calcite.sql2rel.RelDecorrelator.getCorRel(RelDecorrelator.java:929) at org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:818) at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1029) at org.apache.calcite.sql2rel.RelDecorrelator.maybeAddValueGenerator(RelDecorrelator.java:948) at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1154) at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1120) ``` So, the issue would raise when mult-level nested subquery is referring to a column in outer query block. This may explain why the issue may be different from CALCITE-5390, where it uses a one-level nested subquery. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@calcite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org