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)
