You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by xiarui <xi...@smail.nju.edu.cn> on 2021/08/25 03:15:12 UTC

Calcite Fail type assertion for “CHAR(1) NOT NULL” when converting validated SqlNode into relational expressions

I am getting start of Calcite. I want to translate a `SqlNode` to `RelNode`, but fails due to assertion error of type mismatch:&nbsp;
```
ref:
CHAR(1) NOT NULL
input:
CHAR(1) NOT NULL
```


The code is simple:
```
&nbsp; &nbsp; private static final String sql = "select u.name as user_name\n" +
&nbsp; &nbsp; &nbsp; &nbsp; "from users u\n" +
&nbsp; &nbsp; &nbsp; &nbsp; "where u.age &gt; 30";
```
and I am trying to parse -&gt; validate -&gt; convert this sql.
```
&nbsp; &nbsp; &nbsp; &nbsp; SchemaPlus rootSchema = Frameworks.createRootSchema(true);
&nbsp; &nbsp; &nbsp; &nbsp; rootSchema.add("USERS", new AbstractTable() { //note: add a table
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @Override
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RelDataTypeFactory.Builder builder = typeFactory.builder();


&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; builder.add("ID", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.INTEGER));
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; builder.add("NAME", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.CHAR));
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; builder.add("AGE", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.INTEGER));
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return builder.build();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; });


&nbsp; &nbsp; &nbsp; &nbsp; rootSchema.add("JOBS", new AbstractTable() {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @Override
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RelDataTypeFactory.Builder builder = typeFactory.builder();


&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; builder.add("ID", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.INTEGER));
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; builder.add("NAME", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.CHAR));
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; builder.add("COMPANY", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.CHAR));
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return builder.build();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; });


&nbsp; &nbsp; &nbsp; &nbsp; FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .defaultSchema(rootSchema)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .build();
&nbsp; &nbsp; &nbsp; &nbsp; final Planner planner = Frameworks.getPlanner(frameworkConfig);
&nbsp; &nbsp; &nbsp; &nbsp; SqlNode parse = planner.parse(sql);
&nbsp; &nbsp; &nbsp; &nbsp; SqlNode validate = planner.validate(parse);
&nbsp; &nbsp; &nbsp; &nbsp; planner.rel(validate);
```


When I running the code, line `planner.rel(validate)` gets error and says:
```
java.lang.AssertionError: type mismatch:
ref:
CHAR(1) NOT NULL
input:
CHAR(1) NOT NULL


	at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
	at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2000)
	at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:125)
	at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:57)
	at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:112)
	at org.apache.calcite.rel.core.Project.isValid(Project.java:192)
	at org.apache.calcite.rel.core.Project.<init&gt;(Project.java:83)
	at org.apache.calcite.rel.logical.LogicalProject.<init&gt;(LogicalProject.java:62)
	at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:112)
	at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:100)
	at org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:158)
	at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1414)
	at org.apache.calcite.tools.RelBuilder.projectNamed(RelBuilder.java:1470)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:3955)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:670)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:627)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3181)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:563)
	at org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:254)
	at org.apache.calcite.test.MyTest.firstTest(MyTest.java:141)
```
Actually, if I do project on `user.id` or `user.age` (`INTEGER` type), `SqlNode` convert works fine. However, when I project on `user.name` (`CHAR` type), the convert throws errors.


I am working on branch-1.21.


Thanks for sharing some ideas.

Re:Calcite Fail type assertion for “CHAR(1) NOT NULL” when converting validated SqlNode into relational expressions

Posted by xiarui <xi...@smail.nju.edu.cn>.

Sorry about the chaos in the codes. I re-submit the code part.
```
// create schema for tables
SchemaPlus rootSchema = Frameworks.createRootSchema(true);
rootSchema.add("USERS", new AbstractTable() { //note: add a table
@Override
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
RelDataTypeFactory.Builder builder = typeFactory.builder();

builder.add("ID", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.INTEGER));
builder.add("NAME", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.CHAR));
builder.add("AGE", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.INTEGER));
return builder.build();
}
});

rootSchema.add("JOBS", new AbstractTable() {
@Override
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
RelDataTypeFactory.Builder builder = typeFactory.builder();

builder.add("ID", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.INTEGER));
builder.add("NAME", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.CHAR));
builder.add("COMPANY", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.CHAR));
return builder.build();
}
});

FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
.defaultSchema(rootSchema)
.build();
// parse -&gt; validate -&gt; convert to relational expressions final Planner planner = Frameworks.getPlanner(frameworkConfig);
SqlNode parse = planner.parse(sql);
SqlNode validate = planner.validate(parse);
planner.rel(validate);
```


&nbsp;
&nbsp;
------------------&nbsp;Original&nbsp;------------------
From: &nbsp;"xiarui"<xiarui@smail.nju.edu.cn&gt;;
Date: &nbsp;Wed, Aug 25, 2021 11:15 AM
To: &nbsp;"dev"<dev@calcite.apache.org&gt;; 

Subject: &nbsp;Calcite Fail type assertion for “CHAR(1) NOT NULL” when converting validated SqlNode into relational expressions

&nbsp;

I am getting start of Calcite. I want to translate a `SqlNode` to `RelNode`, but fails due to assertion error of type mismatch:&amp;nbsp;
```
ref:
CHAR(1) NOT NULL
input:
CHAR(1) NOT NULL
```


The code is simple:
```
&amp;nbsp; &amp;nbsp; private static final String sql = "select u.name as user_name\n" +
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; "from users u\n" +
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; "where u.age &amp;gt; 30";
```
and I am trying to parse -&amp;gt; validate -&amp;gt; convert this sql.
```
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; SchemaPlus rootSchema = Frameworks.createRootSchema(true);
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; rootSchema.add("USERS", new AbstractTable() { //note: add a table
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; @Override
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; RelDataTypeFactory.Builder builder = typeFactory.builder();


&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; builder.add("ID", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.INTEGER));
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; builder.add("NAME", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.CHAR));
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; builder.add("AGE", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.INTEGER));
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; return builder.build();
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; }
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; });


&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; rootSchema.add("JOBS", new AbstractTable() {
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; @Override
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; RelDataTypeFactory.Builder builder = typeFactory.builder();


&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; builder.add("ID", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.INTEGER));
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; builder.add("NAME", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.CHAR));
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; builder.add("COMPANY", new BasicSqlType(new RelDataTypeSystemImpl() {}, SqlTypeName.CHAR));
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; return builder.build();
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; }
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; });


&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; .defaultSchema(rootSchema)
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; .build();
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; final Planner planner = Frameworks.getPlanner(frameworkConfig);
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; SqlNode parse = planner.parse(sql);
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; SqlNode validate = planner.validate(parse);
&amp;nbsp; &amp;nbsp; &amp;nbsp; &amp;nbsp; planner.rel(validate);
```


When I running the code, line `planner.rel(validate)` gets error and says:
```
java.lang.AssertionError: type mismatch:
ref:
CHAR(1) NOT NULL
input:
CHAR(1) NOT NULL


	at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
	at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2000)
	at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:125)
	at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:57)
	at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:112)
	at org.apache.calcite.rel.core.Project.isValid(Project.java:192)
	at org.apache.calcite.rel.core.Project.<init&amp;gt;(Project.java:83)
	at org.apache.calcite.rel.logical.LogicalProject.<init&amp;gt;(LogicalProject.java:62)
	at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:112)
	at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:100)
	at org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:158)
	at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1414)
	at org.apache.calcite.tools.RelBuilder.projectNamed(RelBuilder.java:1470)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:3955)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:670)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:627)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3181)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:563)
	at org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:254)
	at org.apache.calcite.test.MyTest.firstTest(MyTest.java:141)
```
Actually, if I do project on `user.id` or `user.age` (`INTEGER` type), `SqlNode` convert works fine. However, when I project on `user.name` (`CHAR` type), the convert throws errors.


I am working on branch-1.21.


Thanks for sharing some ideas.