Gavin Ray created CALCITE-5016: ---------------------------------- Summary: NPE: "variable $cor0 is not found", "Unable to implement EnumerableCorrelate" in nested ARRAY() query Key: CALCITE-5016 URL: https://issues.apache.org/jira/browse/CALCITE-5016 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.30.0 Reporter: Gavin Ray
If you remove the WHERE clause in the inner part, it will run: {code:sql} -- Remove this where clause to fix the bug WHERE "users"."house_id" = "houses"."id" {code} Here is a reproduction: {code:java} import org.apache.calcite.adapter.jdbc.JdbcSchema; import org.apache.calcite.jdbc.CalciteConnection; import org.apache.calcite.jdbc.Driver; import org.apache.calcite.rel.RelNode; import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.parser.SqlParseException; import org.apache.calcite.sql.parser.SqlParser; import org.apache.calcite.tools.*; import org.hsqldb.jdbc.JDBCDataSource; import java.sql.*; public class HouseUserTodoSchemaTest { static String ddl = """ CREATE TABLE "houses" ( "id" INTEGER NOT NULL, "address" VARCHAR(255) NOT NULL, ); CREATE TABLE "users" ( "id" INTEGER NOT NULL, "name" VARCHAR(255) NOT NULL, "house_id" INTEGER NOT NULL ); CREATE TABLE "todos" ( "id" INTEGER NOT NULL, "description" VARCHAR(255) NOT NULL, "user_id" INTEGER NOT NULL ); """; static String dml = """ INSERT INTO "houses" VALUES (1, '123 Main St'); INSERT INTO "houses" VALUES (2, '456 Ocean Ave'); INSERT INTO "users" VALUES (1, 'Alice', 1); INSERT INTO "users" VALUES (2, 'Bob', 1); INSERT INTO "users" VALUES (3, 'Carol', 2); INSERT INTO "todos" VALUES (1, 'Buy milk', 1); INSERT INTO "todos" VALUES (2, 'Buy eggs', 1); INSERT INTO "todos" VALUES (3, 'Buy bread', 2); INSERT INTO "todos" VALUES (4, 'Vacuum', 3); """; static String query = """ SELECT "houses"."id", "houses"."address", ARRAY( SELECT "users"."id", "users"."name", ARRAY( SELECT "todos"."id", "todos"."description" FROM "todos" WHERE "todos"."user_id" = "users"."id" ) AS "todos" FROM "users" -- Remove this where clause to fix the bug WHERE "users"."house_id" = "houses"."id" ) AS "users" FROM "houses" """; public static void main(String[] args) throws SqlParseException, ValidationException, RelConversionException, ClassNotFoundException, SQLException { JDBCDataSource hsqldb = new org.hsqldb.jdbc.JDBCDataSource(); hsqldb.setDatabase("jdbc:hsqldb:mem:house_user_todo"); try (Connection connection = hsqldb.getConnection()) { try (Statement statement = connection.createStatement()) { statement.execute(ddl); statement.execute(dml); } } SchemaPlus rootSchema = Frameworks.createRootSchema(false); rootSchema.add("house_user_todo", JdbcSchema.create(rootSchema, "house_user_todo", hsqldb, null, null)); FrameworkConfig config = Frameworks.newConfigBuilder() .parserConfig(SqlParser.config().withCaseSensitive(false)) .defaultSchema(rootSchema.getSubSchema("house_user_todo")) .build(); Planner planner = Frameworks.getPlanner(config); SqlNode sqlNode = planner.parse(query); SqlNode validated = planner.validate(sqlNode); RelNode relNode = planner.rel(validated).project(); Class.forName(Driver.class.getName()); Connection connection = DriverManager.getConnection(Driver.CONNECT_STRING_PREFIX); CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class); RelRunner runner = calciteConnection.unwrap(RelRunner.class); try (PreparedStatement ps = runner.prepareStatement(relNode)) { ResultSet rs = ps.executeQuery(); while (rs.next()) { ResultSetMetaData md = rs.getMetaData(); for (int i = 1; i <= md.getColumnCount(); i++) { System.out.print(md.getColumnName(i) + ": "); System.out.println(rs.getObject(i)); } System.out.println(); } } } } {code} And the stack trace: {code:java} Exception in thread "main" java.sql.SQLException: Error while preparing plan [LogicalProject(id=[$0], address=[$1], users=[$2]) LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}]) JdbcTableScan(table=[[house_user_todo, houses]]) Collect(field=[EXPR$0]) LogicalProject(id=[$0], name=[$1], todos=[$3]) LogicalCorrelate(correlation=[$cor1], joinType=[inner], requiredColumns=[{0}]) LogicalFilter(condition=[=($2, $cor0.id)]) JdbcTableScan(table=[[house_user_todo, users]]) Collect(field=[EXPR$0]) LogicalProject(id=[$0], description=[$1]) LogicalFilter(condition=[=($2, $cor1.id)]) JdbcTableScan(table=[[house_user_todo, todos]]) ] at org.apache.calcite.avatica.Helper.createException(Helper.java:56) at org.apache.calcite.avatica.Helper.createException(Helper.java:41) at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:239) at org.apache.calcite.jdbc.CalciteConnectionImpl.access$100(CalciteConnectionImpl.java:101) at org.apache.calcite.jdbc.CalciteConnectionImpl$2.prepareStatement(CalciteConnectionImpl.java:188) at com.example.HouseUserTodoSchemaTest.main(HouseUserTodoSchemaTest.java:105) Caused by: java.lang.IllegalStateException: Unable to implement EnumerableCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}]): rowcount = 22500.0, cumulative cost = {318610.0 rows, 562611.0 cpu, 0.0 io}, id = 252 Caused by: java.lang.IllegalStateException: Unable to implement EnumerableCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}]): rowcount = 22500.0, cumulative cost = {318610.0 rows, 562611.0 cpu, 0.0 io}, id = 252 JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {110.0 rows, 111.0 cpu, 0.0 io}, id = 230 JdbcTableScan(table=[[house_user_todo, houses]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 15 EnumerableCollect(field=[EXPR$0]): rowcount = 225.0, cumulative cost = {2959.0 rows, 5625.0 cpu, 0.0 io}, id = 250 EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}], todos=[$t3]): rowcount = 225.0, cumulative cost = {2734.0 rows, 5400.0 cpu, 0.0 io}, id = 254 EnumerableCorrelate(correlation=[$cor1], joinType=[inner], requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2509.0 rows, 3825.0 cpu, 0.0 io}, id = 246 JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost = {116.5 rows, 202.5 cpu, 0.0 io}, id = 235 JdbcFilter(condition=[=($2, $cor0.id)]): rowcount = 15.0, cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 233 JdbcTableScan(table=[[house_user_todo, users]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 16 EnumerableCollect(field=[EXPR$0]): rowcount = 15.0, cumulative cost = {143.5 rows, 241.5 cpu, 0.0 io}, id = 244 JdbcToEnumerableConverter: rowcount = 15.0, cumulative cost = {128.5 rows, 226.5 cpu, 0.0 io}, id = 242 JdbcProject(id=[$0], description=[$1]): rowcount = 15.0, cumulative cost = {127.0 rows, 225.0 cpu, 0.0 io}, id = 240 JdbcFilter(condition=[=($2, $cor1.id)]): rowcount = 15.0, cumulative cost = {115.0 rows, 201.0 cpu, 0.0 io}, id = 238 JdbcTableScan(table=[[house_user_todo, todos]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 18 at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:114) at org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:114) at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1130) at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1032) at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:988) at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:668) at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513) at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483) at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249) at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:229) ... 3 more Suppressed: java.lang.NullPointerException: variable $cor0 is not found at java.base/java.util.Objects.requireNonNull(Objects.java:334) at org.apache.calcite.rel.rel2sql.SqlImplementor$BaseContext.getAliasContext(SqlImplementor.java:1429) at org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:628) at org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:1069) at org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:776) at org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:750) at org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:427) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531) at org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:139) at org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:147) at org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:201) at org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:189) at org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.generateSql(JdbcToEnumerableConverter.java:351) at org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:107) at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104) at org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:113) at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104) at org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:118) at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104) at org.apache.calcite.adapter.enumerable.EnumerableCollect.implement(EnumerableCollect.java:81) at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:104) at org.apache.calcite.adapter.enumerable.EnumerableCorrelate.implement(EnumerableCorrelate.java:139) at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:111) ... 12 more {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)