[
https://issues.apache.org/jira/browse/CALCITE-5434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17647791#comment-17647791
]
Julian Hyde commented on CALCITE-5434:
--------------------------------------
[~robert3005], Thanks for logging this. It would be useful to see a minimal
test case - remove as many clauses as you can without making the bug disappear.
I have a hypothesis that the cast in {{cast(d_date as date)}} is responsible
for the difference in nullability.
> AggregateCaseToFilterRule fails to rewrite query for non nullable columns
> -------------------------------------------------------------------------
>
> Key: CALCITE-5434
> URL: https://issues.apache.org/jira/browse/CALCITE-5434
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.32.0
> Reporter: Robert Kruszewski
> Priority: Major
>
> I'm trying to parse some TPC-DS queries with calcite and I noticed that
> aggregate functions with case statements fail to type check when optimizing
> with default rules.
>
> For instance if you try to optimize (TPC-DS query 21)
> {code:sql}
> SELECT *
> FROM
> (SELECT w_warehouse_name ,
> i_item_id ,
> sum(CASE
> WHEN (cast(d_date AS date) < CAST ('1999-03-20' AS date))
> THEN inv_quantity_on_hand
> ELSE 0
> END) AS inv_before ,
> sum(CASE
> WHEN (cast(d_date AS date) >= CAST ('1999-03-20' AS date))
> THEN inv_quantity_on_hand
> ELSE 0
> END) AS inv_after
> FROM inventory ,
> warehouse ,
> item ,
> date_dim
> WHERE i_current_price BETWEEN 0.99 AND 1.49
> AND i_item_sk = inv_item_sk
> AND inv_warehouse_sk = w_warehouse_sk
> AND inv_date_sk = d_date_sk
> AND d_date BETWEEN (CAST ('1999-03-20' AS date) - interval '30' day) AND
> (CAST ('1999-03-20' AS date) + interval '30' day)
> GROUP BY w_warehouse_name,
> i_item_id) x
> WHERE (CASE
> WHEN inv_before > 0 THEN inv_after / inv_before
> ELSE NULL
> END) BETWEEN 2.0/3.0 AND 3.0/2.0
> ORDER BY w_warehouse_name ,
> i_item_id
> LIMIT 100; {code}
> Then you get
> {code}
> Caused by: java.lang.AssertionError: type mismatch:
> aggCall type:
> INTEGER NOT NULL
> inferred type:
> INTEGER
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:32)
> at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2180)
> at
> org.apache.calcite.rel.core.Aggregate.typeMatchesInferred(Aggregate.java:448)
> at org.apache.calcite.rel.core.Aggregate.<init>(Aggregate.java:175)
> at
> org.apache.calcite.rel.logical.LogicalAggregate.<init>(LogicalAggregate.java:72)
> at
> org.apache.calcite.rel.logical.LogicalAggregate.create_(LogicalAggregate.java:144)
> at
> org.apache.calcite.rel.logical.LogicalAggregate.create(LogicalAggregate.java:116)
> at
> org.apache.calcite.rel.core.RelFactories$AggregateFactoryImpl.createAggregate(RelFactories.java:307)
> at org.apache.calcite.tools.RelBuilder.aggregate_(RelBuilder.java:2391)
> at org.apache.calcite.tools.RelBuilder.aggregate(RelBuilder.java:2353)
> at org.apache.calcite.tools.RelBuilder.aggregate(RelBuilder.java:2212)
> at
> org.apache.calcite.rel.rules.AggregateCaseToFilterRule.onMatch(AggregateCaseToFilterRule.java:146)
> at
> org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:223)
> at
> org.apache.calcite.plan.volcano.IterativeRuleDriver.drive(IterativeRuleDriver.java:59)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:523)
> at
> org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:277)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:337)
> at
> org.apache.calcite.prepare.PlannerImpl.transform(PlannerImpl.java:373)
> {code}
> Which suggests that my non nullable input has been converted to nullable. I
> have been able to trace it down to disagreement between
> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rules/AggregateCaseToFilterRule.java#L236
> and
> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java#L941-L942.
> It seems that if the latter is correct the first call should create a
> nullable type instead of forwarding the original.
> Please let me know if you need more information or if you'd like small
> program to reproduce it
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
