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
