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
