[
https://issues.apache.org/jira/browse/CALCITE-5434?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Robert Kruszewski updated CALCITE-5434:
---------------------------------------
Description:
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 Sum(CASE
WHEN ( d_date < Cast('1999-03-20' AS DATE) ) THEN
inv_quantity_on_hand
ELSE 0
END) AS inv_before,
Sum(CASE
WHEN ( d_date >= Cast('1999-03-20' AS DATE) ) THEN
inv_quantity_on_hand
ELSE 0
END) AS inv_after
FROM case_agg_test
GROUP BY i_item_id;
{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
was:
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 Sum(CASE
WHEN ( d_date < Cast('1999-03-20' AS DATE) ) THEN
inv_quantity_on_hand
ELSE 0
END) AS inv_before,
Sum(CASE
WHEN ( d_date >= Cast('1999-03-20' AS DATE) ) THEN
inv_quantity_on_hand
ELSE 0
END) AS inv_after
FROM inventory,
date_dim,
item
WHERE i_item_sk = inv_item_sk
AND inv_date_sk = d_date_sk
GROUP BY i_item_id;
{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
> 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 Sum(CASE
> WHEN ( d_date < Cast('1999-03-20' AS DATE) ) THEN
> inv_quantity_on_hand
> ELSE 0
> END) AS inv_before,
> Sum(CASE
> WHEN ( d_date >= Cast('1999-03-20' AS DATE) ) THEN
> inv_quantity_on_hand
> ELSE 0
> END) AS inv_after
> FROM case_agg_test
> GROUP BY i_item_id;
> {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)
