[ https://issues.apache.org/jira/browse/CALCITE-3507?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16975601#comment-16975601 ]
Danny Chen commented on CALCITE-3507: ------------------------------------- Thanks for reporting this [~tc260], supply a test case in RelOptRulesTest would help a lot to clarify this problem ~ > 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 > Priority: Major > > 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)