[
https://issues.apache.org/jira/browse/CALCITE-7441?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18065682#comment-18065682
]
Stamatis Zampetakis commented on CALCITE-7441:
----------------------------------------------
To overcome the issue we have to wrap nullable conditions using the {{IS TRUE}}
operator.
The resulting plan after the fix will resemble the following SQL query.
{code:sql}
select sum(sal) filter (where mgr = 10 is true) from emp
{code}
> AggregateFilterToFilteredAggregateRule fails when WHERE condition is nullable
> -----------------------------------------------------------------------------
>
> Key: CALCITE-7441
> URL: https://issues.apache.org/jira/browse/CALCITE-7441
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> The {{AggregateFilterToFilteredAggregateRule}} fails when the condition in
> the WHERE clause is nullable. Consider, the following SQL query and assume
> that column {{mgr}} is nullable.
> {code:sql}
> select sum(sal) from emp where mgr = 10
> {code}
> It's easy to see that the condition {{mgr = 10}} is nullable when the
> respective column is nullable. The plan for the query is shown below:
> {noformat}
> LogicalAggregate(group=[{}], EXPR$0=[SUM($5)])
> LogicalFilter(condition=[=($3, 10)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {noformat}
> Applying the rule, on this query fails with the following stacktrace:
> {noformat}
> java.lang.IllegalArgumentException: filter must be BOOLEAN NOT NULL
> at
> com.google.common.base.Preconditions.checkArgument(Preconditions.java:141)
> at org.apache.calcite.rel.core.Aggregate.<init>(Aggregate.java:178)
> 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:346)
> at
> org.apache.calcite.tools.RelBuilder.aggregate_(RelBuilder.java:2786)
> at
> org.apache.calcite.tools.RelBuilder.pruneAggregateInputFieldsAndDeduplicateAggCalls(RelBuilder.java:2657)
> at
> org.apache.calcite.tools.RelBuilder.aggregate_(RelBuilder.java:2581)
> at org.apache.calcite.tools.RelBuilder.aggregate(RelBuilder.java:2478)
> at
> org.apache.calcite.rel.rules.AggregateFilterToFilteredAggregateRule.onMatch(AggregateFilterToFilteredAggregateRule.java:94)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:350)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:575)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:459)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeRuleInstance(HepPlanner.java:282)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance$State.execute(HepInstruction.java:178)
> at
> org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:250)
> at
> com.google.common.collect.ImmutableList.forEach(ImmutableList.java:421)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:249)
> at
> org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:244)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:219)
> {noformat}
> When creating filtered aggregates the condition must not be nullable.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
