IndexOutOfBoundException, WITH and JOIN

[Egor Ryashin]

Hi all,

I’m trying to figure out why this conversion fails:

WITH `A` AS (SELECT `TABLE_NAME`
FROM `main`.`TABLES`), `B` AS (SELECT 'k' AS `K`) (SELECT *
FROM `A`
INNER JOIN `B` ON `A`.`TABLE_NAME` = `B`.`K`)
Exception in thread "main" java.lang.IndexOutOfBoundsException: Index 1 out of 
bounds for length 1
        at 
java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
        at 
java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
        at 
java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:266)
        at java.base/java.util.Objects.checkIndex(Objects.java:359)
        at java.base/java.util.ArrayList.get(ArrayList.java:427)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter$LookupContext.findRel(SqlToRelConverter.java:5212)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.lookup(SqlToRelConverter.java:4345)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.lookupExp(SqlToRelConverter.java:4290)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:3637)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.access$2100(SqlToRelConverter.java:217)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4717)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4013)
        at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:334)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4577)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:3856)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:670)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:627)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3100)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:563)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertWith(SqlToRelConverter.java:3927)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3114)
        at 
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:563)
        at org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:235)

The source code:
Connection connection = DriverManager.getConnection("jdbc:calcite:");
CalciteConnection calciteConnection = 
connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();
final DataSource ds = JdbcSchema.dataSource(
    "jdbc:hsqldb:mem:db",
    "org.hsqldb.jdbc.JDBCDriver",
    "SA",
    "");
rootSchema.add("main", JdbcSchema.create(rootSchema, "main", ds, null, 
"INFORMATION_SCHEMA"));
FrameworkConfig config = Frameworks.newConfigBuilder()
                                   .defaultSchema(rootSchema)
                                   .build();

Planner planner = Frameworks.getPlanner(config);

SqlNode sqlNode = planner.parse("with a as (select TABLE_NAME from 
\"main\".\"TABLES\"), b as (select 'k' as k) select * from a join b on 
a.TABLE_NAME = b.K");
System.out.println(sqlNode.toString());

sqlNode = planner.validate(sqlNode);

RelRoot relRoot = planner.rel(sqlNode);
System.out.println(relRoot.toString());

This one (without join) works fine:
with a as (select TABLE_NAME from \"main\".\"TABLES\"), b as (select 'k' as k) 
select * from b
It doesn’t depend on the database, I tried another schema with the same result.