[
https://issues.apache.org/jira/browse/CALCITE-5016?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Alessandro Solimando resolved CALCITE-5016.
-------------------------------------------
Resolution: Duplicate
> 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
> Priority: Major
>
> 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)
