I don't know if relBuilder has an API to add a complete expression to a partial
tree under construction, but if it does, I don't see why this wouldn't be
possible.
The next question is "what do you want to do with the resulting data structure?"
Calcite will be able to use the existing data structure if it conforms to its
own invariants.
To give you a concrete example, in our compiler we have implemented support for
user-defined functions written in SQL. You can write:
CREATE FUNCTION F(x INTEGER) RETURNS INTEGER AS x % 2;
This is not something that Calcite understands. But we modified the parser to
accept this SQL; that's easy.
After parsing, we convert this to the following SQL:
CREATE TABLE T(x INTEGER);
SELECT x % 2 FROM T;
we use calcite to compile this using a regular flow, including validation and
conversion to Rel.
Then from the produced Rel data structure we extract the implementation of the
query and use it to implement the body of the function, using a standard Rel
visitor we wrote (almost exactly the same visitor we used to convert Rel to our
own IR).
This sounds similar to the stuff you want to do.
So, in summary, these are in the end just data structures. Calcite will
manipulate successfully data structures that respect some invariants. If the
operations you create will build data structures that conform to these
invariants, then probably you can use tools from Calcite to manipulate them.
Mihai
________________________________
From: Hugh Pearse <hughpea...@gmail.com>
Sent: Wednesday, June 25, 2025 2:05 PM
To: dev@calcite.apache.org <dev@calcite.apache.org>
Subject: Re: SqlParser.parseExpression()
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, <mbu...@gmail.com> 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 <hughpea...@gmail.com>
> Sent: Wednesday, June 25, 2025 11:30 AM
> To: dev@calcite.apache.org <dev@calcite.apache.org>
> 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
>
