Hi,
I've filed the issue CALCITE-4652 (see [1]).
Please take a look at the suggested fix [2].
[1]. https://issues.apache.org/jira/browse/CALCITE-4652
[2]. https://github.com/apache/calcite/pull/2439
On 04.06.2021 18:46, Taras Ledkov wrote:
Hi,
I try to expand return type of the SUM aggregate function like
Postgress for my SQL engine based on Calcite framework.
e.g.: SUM of INTEGER or BIGINT returns DECIMAL.
Looks like the override the `RelDataTypeSystem#deriveSumType` is
appropriate place for he change. Is it OK?
Let's take a look at the query:
SELECT count(comm), COUNT(DISTINCT comm) FROM emp
`AggregateExpandDistinctAggregatesRule.Config.JOIN` creates the plan
in which not-distinct COUNT is replaced by SUM of COUNTs by groups.
LogicalAggregate(group=[{}], EXPR$0=[$SUM0($1)], EXPR$1=[COUNT($0)])
LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
LogicalProject(COMM=[$6])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
After change the SUM type the planner fails on the check of the
aggregate call types. (I've created short reproducer [1])
Error:
java.lang.AssertionError: type mismatch:
aggCall type:
BIGINT NOT NULL
inferred type:
DECIMAL(19, 0) NOT NULL
at org.apache.calcite.util.Litmus$1.fail(Litmus.java:32)
at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2211)
at
org.apache.calcite.rel.core.Aggregate.typeMatchesInferred(Aggregate.java:443)
at org.apache.calcite.rel.core.Aggregate.<init>(Aggregate.java:170)
at
org.apache.calcite.rel.logical.LogicalAggregate.<init>(LogicalAggregate.java:72)
at
org.apache.calcite.rel.logical.LogicalAggregate.copy(LogicalAggregate.java:154)
at
org.apache.calcite.rel.logical.LogicalAggregate.copy(LogicalAggregate.java:48)
at
org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule.convertSingletonDistinct(AggregateExpandDistinctAggregatesRule.java:401)
...
My questions:
1. is change of SUM return type is valid and we have to fix
`AggregateExpandDistinctAggregatesRule`?
2. If 1 - YES, then what is the best way to fix: add projection with
cast results of the `$SUM0` to BIGINT or something else?
3. If 1 - NO, then what is the best way to expand SUM return type?
[1]. https://github.com/apache/calcite/pull/2427/files
--
Taras Ledkov
Mail-To: tled...@gridgain.com