Tiangang Chen created CALCITE-3507:
--------------------------------------
Summary: Mismatched type AssertionError or incorrect logical plan
on correlated query
Key: CALCITE-3507
URL: https://issues.apache.org/jira/browse/CALCITE-3507
Project: Calcite
Issue Type: Bug
Affects Versions: 1.21.0
Reporter: Tiangang Chen
Calcite fails to convert correlated SQL query to logical plan because of
AssertionError (type mismatch) during decorrelateQuery. Forcing types to match
results in incorrect logical plan.
Stack trace:
{code:java}
Exception in thread "main" java.lang.AssertionError: mismatched type $1 VARCHAR
at
org.apache.calcite.rex.RexUtil$FixNullabilityShuttle.visitInputRef(RexUtil.java:2540)
at
org.apache.calcite.rex.RexUtil$FixNullabilityShuttle.visitInputRef(RexUtil.java:2518)
at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:112)
at org.apache.calcite.rex.RexShuttle.visitList(RexShuttle.java:149)
at org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:101)
at org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:34)
at org.apache.calcite.rex.RexCall.accept(RexCall.java:191)
at org.apache.calcite.rex.RexShuttle.apply(RexShuttle.java:277)
at org.apache.calcite.rex.RexShuttle.mutate(RexShuttle.java:239)
at org.apache.calcite.rex.RexShuttle.apply(RexShuttle.java:257)
at org.apache.calcite.rex.RexUtil.fixUp(RexUtil.java:1635)
at
org.apache.calcite.rel.rules.FilterJoinRule.perform(FilterJoinRule.java:249)
at
org.apache.calcite.rel.rules.FilterJoinRule$FilterIntoJoinRule.onMatch(FilterJoinRule.java:383)
at
org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:319)
at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:560)
at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:419)
at
org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:256)
at
org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:127)
at
org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:215)
at org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:202)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:250)
at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:215)
at org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:259)
at RunWithPlanner.main(RunWithPlanner.java:17)
{code}
Code to reproduce:
{code:java}
SchemaPlus rootSchema = Frameworks.createRootSchema(true);
rootSchema.add("LIKES", new AbstractTable() {
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
RelDataTypeFactory.Builder builder = typeFactory.builder();
builder.add("DRINKER", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
builder.add("BEER", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
return builder.build();
}
});
rootSchema.add("SERVES", new AbstractTable() {
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
RelDataTypeFactory.Builder builder = typeFactory.builder();
builder.add("BAR", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
builder.add("BEER", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
builder.add("PRICE", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.DECIMAL));
return builder.build();
}
});
rootSchema.add("DRINKER", new AbstractTable() {
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
RelDataTypeFactory.Builder builder = typeFactory.builder();
builder.add("NAME", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
builder.add("ADDRESS", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
return builder.build();
}
});
rootSchema.add("FREQUENTS", new AbstractTable() {
public RelDataType getRowType(final RelDataTypeFactory typeFactory) {
RelDataTypeFactory.Builder builder = typeFactory.builder();
builder.add("DRINKER", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
builder.add("BAR", new BasicSqlType(new RelDataTypeSystemImpl() {
}, SqlTypeName.VARCHAR));
builder.add("TIMES_A_WEEK", new BasicSqlType(new
RelDataTypeSystemImpl() {
}, SqlTypeName.SMALLINT));
return builder.build();
}
});
String SAMPLE_QUERY = "SELECT Frequents.drinker, Frequents.bar\n" +
"FROM Frequents\n" +
"WHERE NOT EXISTS(SELECT * FROM Likes, Serves\n" +
"WHERE Likes.drinker = Frequents.drinker\n" +
"AND Serves.bar = Frequents.bar\n" +
"AND Likes.beer = Serves.beer)";
Planner planner =
Frameworks.getPlanner(Frameworks.newConfigBuilder().defaultSchema(rootSchema).build());
SqlNode parsed = planner.parse(SAMPLE_QUERY);
SqlNode validated = planner.validate(parsed);
RelRoot relRoot = planner.rel(validated);
System.out.println(RelOptUtil.toString(relRoot.rel));{code}
Using character types (VARCHAR, CHAR) for above schema will cause the
exception. Forcing all the types to be non-character types (say INTEGER,
BOOLEAN, etc.) will not cause exception but the resulting logical plan is
incorrect because of one projection on the wrong columns (see the marked line
below):
{code:java}
LogicalProject(DRINKER=[$0], BAR=[$1])
LogicalFilter(condition=[IS NULL($5)])
LogicalJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[left])
EnumerableTableScan(table=[[FREQUENTS]])
LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
LogicalProject(DRINKER1=[$7], BAR00=[$8], $f0=[true]) [this is
incorrect]
LogicalJoin(condition=[=($1, $5)], joinType=[inner])
LogicalJoin(condition=[=($0, $2)], joinType=[inner])
EnumerableTableScan(table=[[LIKES]])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DRINKER=[$0], BAR=[$1])
EnumerableTableScan(table=[[FREQUENTS]])
LogicalJoin(condition=[=($0, $4)], joinType=[inner])
EnumerableTableScan(table=[[SERVES]])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DRINKER=[$0], BAR=[$1])
EnumerableTableScan(table=[[FREQUENTS]])
{code}
This projection:
LogicalProject(DRINKER1=[$7], BAR00=[$8], $f0=[true])
is on the wrong columns and therefore will produce incorrect query result.
Correct columns could be DRINKER1=[$0], BAR00=[$4]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
