Re: How to avoid SUM0 or disable a rule ?

[Haisheng Yuan]

Hi Gelbana,

You can construct your own AggregateReduceFunctionsRule instance by specifying 
the functions you want to reduce:

public AggregateReduceFunctionsRule(Class<? extends Aggregate> aggregateClass,
    RelBuilderFactory relBuilderFactory, EnumSet<SqlKind> functionsToReduce) {

But I think the issue you described might be a bug, can you open a JIRA issue 
with a test case if possible?

- Haisheng

------------------------------------------------------------------
发件人：Muhammad Gelbana<m.gelb...@gmail.com>
日　期：2019年06月12日 01:46:28
收件人：<dev@calcite.apache.org>
主　题：How to avoid SUM0 or disable a rule ?

Executing the following query produces unexpected results

SELECT
    "Calcs"."key" AS "key",
    SUM("Calcs"."num2") AS "sum:num2:ok",
    SUM("Calcs"."num2") AS "$__alias__0"
FROM "TestV1"."Calcs" "Calcs"
GROUP BY 1
ORDER BY 3 ASC NULLS FIRST
LIMIT 10

The returned results contains 0 instead of NULLs while running the query
against a PostgreSQL instance returns NULLs as expected.

The reason for that is that Calcite uses SUM0 implementation instead of SUM.
I found that the AggregateReduceFunctionsRule rule is the one that converts
the SUM aggregate call to SUM0, so is there a way to remove this rule
before planning ?

Thanks,
Gelbana