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)