Hi,
To answer your question:
"what do you want to do with the resulting data structure?"
Answer:
I am writing an application which allows data modellers to specify a
snowflake schema using yaml files. The center of the snowflake is the
"from" clause in SQL. This fact table is pushed on to the relBuilder.
Potential table join paths are defined in the yaml file. When building a
with scans are incrementally pushed on top on the stack in the relBuilder.
Then a join expression is input from the yaml file (including the "on"
condition) and pushed onto the top of the stack in the relBuilder. This
process repeats incrementally to build a complete snowflake.
But to simplify the problem statement, I would simply say:
I want to parse:
JOIN depts ON emps.deptno = depts.deptno
From,
Hugh Pearse
On Wed, 25 Jun 2025, 22:36 Mihai Budiu, <mbu...@gmail.com> wrote:
> 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
> >
>
