Sorry, my mistake. The query is valid.
I read the quotes around ‘k’ as back-ticks, `k`. In which case `k` would be a
an identifier rather than a character literal, and there is no object in scope
called `k`.
Julian
> On Aug 18, 2022, at 6:41 PM, Benchao Li <libenc...@apache.org> wrote:
>
> Julian,
>
> The query is invalid. When validating B there is no K in scope.
>
> I'm not sure that I understand this statement, could you kindly elaborate a
> little more?
>
> Julian Hyde <jhyde.apa...@gmail.com> 于2022年8月18日周四 23:32写道：
>
>> 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
>>>
>>
>
>
> --
>
> Best,
> Benchao Li
