[CALCITE-4773] RelDecorrelator's RemoveSingleAggregateRule can produce result with wrong row type

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.27.0
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Ruben Q L
• Reporter: Ruben Q L
• Created: 13/Sep/21 14:01
• Updated: 20/Oct/21 05:25
• Resolved: 24/Sep/21 08:19

Description

The problem can be reproduced with the following test (to be added to unnest.iq):

SELECT unnested_outer.val, COUNT(1) AS count_val
FROM
(
    SELECT *
    FROM (VALUES (1, array [3, 5]), (2, array [6, 4, 6]))
    AS u(x, y)
) AS t, UNNEST(t.y) AS unnested_outer(val)
WHERE
(
    SELECT COUNT(unnested_inner.val) > 0
    FROM UNNEST(t.y) AS unnested_inner(val)
    WHERE unnested_inner.val = 4
)
GROUP BY unnested_outer.val
ORDER BY count_val DESC, unnested_outer.val ASC;
+-----+-----------+
| VAL | COUNT_VAL |
+-----+-----------+
|   6 |         2 |
|   4 |         1 |
+-----+-----------+
(2 rows)

!ok

When the test is executed, it fails with the following error:

> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(BOOLEAN NOT NULL $f0) NOT NULL
> expression type is RecordType(BOOLEAN $f0) NOT NULL
> set is rel#24436:LogicalAggregate.NONE.[](input=HepRelVertex#24435,group={},agg#0=SINGLE_VALUE($0))
> expression is LogicalProject($f0=[CAST(>($0, 0)):BOOLEAN])
>   LogicalAggregate(group=[{}], agg#0=[COUNT()])
>     LogicalFilter(condition=[=($0, 4)])
>       LogicalProject(VAL=[$0])
>         Uncollect
>           LogicalProject(Y=[$cor1.Y])
>             LogicalValues(tuples=[[{ 0 }]])
249a254,325
> 	at org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:391)
> 	at org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:60)
> 	at org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:269)
> 	at org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:284)
> 	at org.apache.calcite.sql2rel.RelDecorrelator$RemoveSingleAggregateRule.onMatch(RelDecorrelator.java:1923)
> 	at org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> 	at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> 	at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> 	at org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:251)
> 	at org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:130)
> 	at org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> 	at org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> 	at org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
...

The root cause seems to be in this piece of code inside RelDecorrelator's RemoveSingleAggregateRule#onMatch:

// singleAggRel produces a nullable type, so create the new
// projection that casts proj expr to a nullable type.
final RelBuilder relBuilder = call.builder();
final RelDataType type =
  relBuilder.getTypeFactory()
	  .createTypeWithNullability(projExprs.get(0).getType(), true);
final RexNode cast = relBuilder.getRexBuilder().makeCast(type, projExprs.get(0));
relBuilder.push(aggregate).project(cast);
call.transformTo(relBuilder.build());

Note that the comment assumes that "singleAggRel produces a nullable type", but in this particular case, it seems to produce a non-nullable type, so probably this piece of code needs to be adapted.

Issue Links

• links to: GitHub Pull Request #2513