[jira] [Created] (CALCITE-5434) AggregateCaseToFilterRule fails to rewrite query for non nullable columns

["Robert Kruszewski (Jira)" <ji...@apache.org>]

Robert Kruszewski created CALCITE-5434:
------------------------------------------

             Summary: 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


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)