Hi, thank you so much for reviewing my question 🙇♂️
I thought maybe I was ignorant, and maybe there was a secret way to inject
context using Contexts.of(Object...). Judging from your response this is
not possible.
I am determined to solve this puzzle. As an alternative do you think it
would be possible to parse a statement like this:
SELECT * FROM emps
JOIN depts ON emps.deptno = depts.deptno
And then extract this portion
JOIN depts ON emps.deptno = depts.deptno
And add it on top of an existing relBuilder?
Thank you so much for your help
Kindest regards
From,
Hugh Pearse
On Wed, 25 Jun 2025, 19:46 Mihai Budiu, <[email protected]> wrote:
> In general you pass to the validator a SQL query; the validator will make
> sure the query is fine, and it will rewrite it to make it more explicit.
> This entails for example resolving identifiers: what is "deptno" - a table,
> a column, and if it's a column, what table does it belong to?
>
> For your expression with "free" variables the validator does not have
> enough information to understand what these identifiers refer to.
>
> Even if the validator was enhanced to "understand" such expressions, the
> validator cannot give them a "meaning". When you say "SELECT deptno FROM
> emps", deptno implicitly iterates over all rows in the emps table, and the
> meaning of this statement is to produce a table from all results produced
> by iterating.
>
> What is the meaning of your expression? What would you expect the
> validator to produce if it worked?
>
> Mihai
>
> ________________________________
> From: Hugh Pearse <[email protected]>
> Sent: Wednesday, June 25, 2025 11:30 AM
> To: [email protected] <[email protected]>
> Subject: SqlParser.parseExpression()
>
> Hi team,
> I am trying to join 2 tables, but want to provide the join condition (join
> ON x=y) as a text input which is parsed.
>
> Example scenario:
>
> 1. user provides input:
> emps.deptno = depts.deptno
> 2. calcite parses join expression and creates join
>
> Expected result
> SELECT * FROM emps JOIN depts ON emps.deptno = depts.deptno
>
> Actual result:
> I am facing error:
> Table 'emps' not found
> at
>
> java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
> at
>
> java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
> at
>
> java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at
>
> java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
> at
> java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:948)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:933)
> at
>
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5643)
> at
>
> org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:364)
> at
>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateIdentifier(SqlValidatorImpl.java:3348)
> at
> org.apache.calcite.sql.SqlIdentifier.validateExpr(SqlIdentifier.java:307)
> at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:475)
> at
>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6371)
> at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:143)
> at
>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1101)
> at
>
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:807)
>
>
> Code:
>
> ```java
> import com.google.common.collect.ImmutableList;
> import org.apache.calcite.adapter.java.ReflectiveSchema;
> import org.apache.calcite.config.Lex;
> import org.apache.calcite.jdbc.CalciteConnection;
> import org.apache.calcite.jdbc.CalciteSchema;
> import org.apache.calcite.plan.RelOptCluster;
> import org.apache.calcite.plan.ViewExpanders;
> import org.apache.calcite.prepare.CalciteCatalogReader;
> import org.apache.calcite.rel.type.RelDataTypeFactory;
> import org.apache.calcite.rex.RexBuilder;
> import org.apache.calcite.rex.RexNode;
> import org.apache.calcite.schema.SchemaPlus;
> import org.apache.calcite.sql.SqlNode;
> import org.apache.calcite.sql.fun.SqlStdOperatorTable;
> import org.apache.calcite.sql.parser.SqlParser;
> import org.apache.calcite.sql.validate.SqlValidator;
> import org.apache.calcite.sql.validate.SqlValidatorUtil;
> import org.apache.calcite.sql2rel.SqlToRelConverter;
> import org.apache.calcite.tools.FrameworkConfig;
> import org.apache.calcite.tools.Frameworks;
> import org.apache.calcite.tools.RelBuilder;
> import org.junit.jupiter.api.Test;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.util.Properties;
>
> public class JoinTest {
>
> public static class HrSchema {
> public final Employee[] emps = {
> new Employee(100, "Alice", 10),
> new Employee(200, "Bob", 20)
> };
> public final Department[] depts = {
> new Department(10, "Sales"),
> new Department(20, "Engineering")
> };
> }
>
> public static class Employee {
> public final int empid;
> public final String name;
> public final int deptno;
>
> public Employee(int empid, String name, int deptno) {
> this.empid = empid;
> this.name = name;
> this.deptno = deptno;
> }
> }
>
> public static class Department {
> public final int deptno;
> public final String name;
>
> public Department(int deptno, String name) {
> this.deptno = deptno;
> this.name = name;
> }
> }
>
> @Test
> public void testJoinToRexNode() throws Exception {
> Properties props = new Properties();
> Connection connection =
> DriverManager.getConnection("jdbc:calcite:", props);
> CalciteConnection calciteConn =
> connection.unwrap(CalciteConnection.class);
> SchemaPlus rootSchema = calciteConn.getRootSchema();
> rootSchema.add("hr", new ReflectiveSchema(new HrSchema()));
> calciteConn.setSchema("hr");
>
> FrameworkConfig config = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.config().withLex(Lex.MYSQL))
> .defaultSchema(rootSchema.getSubSchema("hr"))
> .build();
>
> String sql = "SELECT * FROM emps JOIN depts ON emps.deptno =
> depts.deptno";
>
> SqlValidator.Config sqlValidatorConfig =
> config.getSqlValidatorConfig();
> SqlParser.Config parserConfig = config.getParserConfig();
>
> ImmutableList<String> defaultSchemaPath = ImmutableList.of();
> RelBuilder relBuilder = RelBuilder.create(config);
> RelOptCluster cluster = relBuilder.getCluster();
> RexBuilder rexBuilder = relBuilder.getRexBuilder();
> RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
> CalciteSchema calciteSchema =
> CalciteSchema.from(rootSchema.getSubSchema("hr"));
> CalciteCatalogReader calciteCatalogReader = new
> CalciteCatalogReader(
> calciteSchema,
> defaultSchemaPath,
> typeFactory,
> calciteConn.config()
> );
>
> SqlValidator sqlValidator = SqlValidatorUtil.newValidator(
> SqlStdOperatorTable.instance(),
> calciteCatalogReader,
> typeFactory,
> sqlValidatorConfig
> );
>
> SqlToRelConverter sqlToRelConverter = new SqlToRelConverter(
> ViewExpanders.simpleContext(cluster),
> sqlValidator,
> calciteCatalogReader,
> cluster,
> config.getConvertletTable(),
> config.getSqlToRelConverterConfig()
> );
>
> // First test (works)
> SqlParser firstParser = SqlParser.create(sql, parserConfig);
> SqlNode firstSqlNode = firstParser.parseQuery();
> SqlNode firstValidatedSqlNode =
> sqlValidator.validate(firstSqlNode);
> RexNode firstRexNode =
> sqlToRelConverter.convertExpression(firstValidatedSqlNode);
> System.out.println(firstRexNode);
>
> // Second test (fails)
> sql = "emps.deptno = depts.deptno";
> SqlParser secondParser = SqlParser.create(sql, parserConfig);
> SqlNode secondSqlNode = secondParser.parseExpression();
> SqlNode secondValidatedSqlNode =
> sqlValidator.validate(secondSqlNode);
> RexNode secondRexNode =
> sqlToRelConverter.convertExpression(secondValidatedSqlNode);
> }
> }
> ```
>
> error is raised at this line:
> SqlNode secondValidatedSqlNode = sqlValidator.validate(secondSqlNode);
>
> my questions are,
> 1. is this type of expression even supported?
> 2. its a bug with calcite or a bug with my code?
>
> From,
> Hugh Pearse
>
