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

Tue, 24 Aug 2021 20:23:37 -0700 

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 -> validate -> convert to relational expressions final Planner 
planner = Frameworks.getPlanner(frameworkConfig);
SqlNode parse = planner.parse(sql);
SqlNode validate = planner.validate(parse);
planner.rel(validate);
```


 
 
------------------ Original ------------------
From: &nbsp;"xiarui"<[email protected]&gt;;
Date: &nbsp;Wed, Aug 25, 2021 11:15 AM
To: &nbsp;"dev"<[email protected]&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.

Reply via email to