The query is invalid. When validating B there is no K in scope. This should be
throwing a validation error in all Calcite versions. If it happens to run in
some Calcite versions that is more luck than design.
Can you log a jira case please?
Julian
> On Aug 18, 2022, at 6:00 AM, Egor Ryashin <riashin.e...@gmail.com> wrote:
>
> true, the version was rather old, thank you.
>
>> On 18 Aug 2022, at 02:12, Benchao Li <libenc...@apache.org> wrote:
>>
>> Egor,
>>
>> What's the Calcite version are you using?
>> I tried your code in 1.30.0 and 1.31.0, they both work fine.
>>
>> Egor Ryashin <riashin.e...@gmail.com> 于2022年8月17日周三 22:40写道：
>>
>>> 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.
>>>
>>>
>>
>> --
>>
>> Best,
>> Benchao Li
>
