You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Muhammad Gelbana <m....@gmail.com> on 2019/06/11 17:46:28 UTC
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
Re: Re: How to avoid SUM0 or disable a rule ?
Posted by Muhammad Gelbana <m....@gmail.com>.
Sorry folks. False alarm. The aggregator works fine, but my table scan was
faulty.
Thanks,
Gelbana
On Tue, Jun 11, 2019 at 9:24 PM Muhammad Gelbana <m....@gmail.com>
wrote:
> With pleaseure. I'll try to fix it first to confirm that my assumption is
> correct.
>
> Thanks,
> Gelbana
>
>
> On Tue, Jun 11, 2019 at 8:44 PM Haisheng Yuan <h....@alibaba-inc.com>
> wrote:
>
>> Cool, can you create an issue for this bug?
>>
>> - Haisheng
>>
>> ------------------------------------------------------------------
>> 发件人：Muhammad Gelbana<m....@gmail.com>
>> 日 期：2019年06月12日 02:39:20
>> 收件人：dev@calcite.apache.org (dev@calcite.apache.org)<
>> dev@calcite.apache.org>
>> 抄 送：Haisheng Yuan<h....@alibaba-inc.com>
>> 主 题：Re: How to avoid SUM0 or disable a rule ?
>>
>> I believe it's a bug because DoubleSum (Also LongSum and IntSum) are
>> initialized with a value of 0 [1]
>>
>> [1]
>> https://github.com/apache/calcite/blob/a3c56be7bccc58859524ba39e5b30b7078f97d00/core/src/main/java/org/apache/calcite/interpreter/AggregateNode.java#L459
>>
>> Thanks,
>> Gelbana
>>
>>
>> On Tue, Jun 11, 2019 at 8:35 PM Vamshi Krishna <
>> vamshi.v.krishna@gmail.com> wrote:
>>
>>> It's done in the SqlToRelConverter.java:5427. I don't think there is a
>>> way currently to disable it (i may be wrong).
>>> There should be a configurable option to disable this.
>>>
>>>
>>> -Vamshi
>>>
>>> On Tue, Jun 11, 2019 at 2:31 PM Muhammad Gelbana <m....@gmail.com>
>>> wrote:
>>> >
>>> > I just cleared the reducible aggregate calls collection at runtime (to
>>> void
>>> > the rule) and I'm still facing the same problem. This onviously has
>>> nothing
>>> > to do with the rule. I'll investigate further. Thanks for your help.
>>> >
>>> > Thanks,
>>> > Gelbana
>>> >
>>> >
>>> > On Tue, Jun 11, 2019 at 8:16 PM Haisheng Yuan <h....@alibaba-inc.com>
>>> > wrote:
>>> >
>>> > > 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....@gmail.com>
>>> > > 日 期：2019年06月12日 01:46:28
>>> > > 收件人：<de...@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
>>> > >
>>> > >
>>>
>>
>>
Re: Re: How to avoid SUM0 or disable a rule ?
Posted by Muhammad Gelbana <m....@gmail.com>.
With pleaseure. I'll try to fix it first to confirm that my assumption is
correct.
Thanks,
Gelbana
On Tue, Jun 11, 2019 at 8:44 PM Haisheng Yuan <h....@alibaba-inc.com>
wrote:
> Cool, can you create an issue for this bug?
>
> - Haisheng
>
> ------------------------------------------------------------------
> 发件人：Muhammad Gelbana<m....@gmail.com>
> 日 期：2019年06月12日 02:39:20
> 收件人：dev@calcite.apache.org (dev@calcite.apache.org)<dev@calcite.apache.org
> >
> 抄 送：Haisheng Yuan<h....@alibaba-inc.com>
> 主 题：Re: How to avoid SUM0 or disable a rule ?
>
> I believe it's a bug because DoubleSum (Also LongSum and IntSum) are
> initialized with a value of 0 [1]
>
> [1]
> https://github.com/apache/calcite/blob/a3c56be7bccc58859524ba39e5b30b7078f97d00/core/src/main/java/org/apache/calcite/interpreter/AggregateNode.java#L459
>
> Thanks,
> Gelbana
>
>
> On Tue, Jun 11, 2019 at 8:35 PM Vamshi Krishna <va...@gmail.com>
> wrote:
>
>> It's done in the SqlToRelConverter.java:5427. I don't think there is a
>> way currently to disable it (i may be wrong).
>> There should be a configurable option to disable this.
>>
>>
>> -Vamshi
>>
>> On Tue, Jun 11, 2019 at 2:31 PM Muhammad Gelbana <m....@gmail.com>
>> wrote:
>> >
>> > I just cleared the reducible aggregate calls collection at runtime (to
>> void
>> > the rule) and I'm still facing the same problem. This onviously has
>> nothing
>> > to do with the rule. I'll investigate further. Thanks for your help.
>> >
>> > Thanks,
>> > Gelbana
>> >
>> >
>> > On Tue, Jun 11, 2019 at 8:16 PM Haisheng Yuan <h....@alibaba-inc.com>
>> > wrote:
>> >
>> > > 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....@gmail.com>
>> > > 日 期：2019年06月12日 01:46:28
>> > > 收件人：<de...@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
>> > >
>> > >
>>
>
>
Re: Re: How to avoid SUM0 or disable a rule ?
Posted by Haisheng Yuan <h....@alibaba-inc.com>.
Cool, can you create an issue for this bug?
- Haisheng
------------------------------------------------------------------
发件人：Muhammad Gelbana<m....@gmail.com>
日 期：2019年06月12日 02:39:20
收件人：dev@calcite.apache.org (dev@calcite.apache.org)<de...@calcite.apache.org>
抄 送：Haisheng Yuan<h....@alibaba-inc.com>
主 题：Re: How to avoid SUM0 or disable a rule ?
I believe it's a bug because DoubleSum (Also LongSum and IntSum) are initialized with a value of 0 [1]
[1] https://github.com/apache/calcite/blob/a3c56be7bccc58859524ba39e5b30b7078f97d00/core/src/main/java/org/apache/calcite/interpreter/AggregateNode.java#L459
Thanks,
Gelbana
On Tue, Jun 11, 2019 at 8:35 PM Vamshi Krishna <va...@gmail.com> wrote:
It's done in the SqlToRelConverter.java:5427. I don't think there is a
way currently to disable it (i may be wrong).
There should be a configurable option to disable this.
-Vamshi
On Tue, Jun 11, 2019 at 2:31 PM Muhammad Gelbana <m....@gmail.com> wrote:
>
> I just cleared the reducible aggregate calls collection at runtime (to void
> the rule) and I'm still facing the same problem. This onviously has nothing
> to do with the rule. I'll investigate further. Thanks for your help.
>
> Thanks,
> Gelbana
>
>
> On Tue, Jun 11, 2019 at 8:16 PM Haisheng Yuan <h....@alibaba-inc.com>
> wrote:
>
> > 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....@gmail.com>
> > 日 期：2019年06月12日 01:46:28
> > 收件人：<de...@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
> >
> >
Re: How to avoid SUM0 or disable a rule ?
Posted by Muhammad Gelbana <m....@gmail.com>.
I believe it's a bug because DoubleSum (Also LongSum and IntSum) are
initialized with a value of 0 [1]
[1]
https://github.com/apache/calcite/blob/a3c56be7bccc58859524ba39e5b30b7078f97d00/core/src/main/java/org/apache/calcite/interpreter/AggregateNode.java#L459
Thanks,
Gelbana
On Tue, Jun 11, 2019 at 8:35 PM Vamshi Krishna <va...@gmail.com>
wrote:
> It's done in the SqlToRelConverter.java:5427. I don't think there is a
> way currently to disable it (i may be wrong).
> There should be a configurable option to disable this.
>
>
> -Vamshi
>
> On Tue, Jun 11, 2019 at 2:31 PM Muhammad Gelbana <m....@gmail.com>
> wrote:
> >
> > I just cleared the reducible aggregate calls collection at runtime (to
> void
> > the rule) and I'm still facing the same problem. This onviously has
> nothing
> > to do with the rule. I'll investigate further. Thanks for your help.
> >
> > Thanks,
> > Gelbana
> >
> >
> > On Tue, Jun 11, 2019 at 8:16 PM Haisheng Yuan <h....@alibaba-inc.com>
> > wrote:
> >
> > > 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....@gmail.com>
> > > 日 期：2019年06月12日 01:46:28
> > > 收件人：<de...@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
> > >
> > >
>
Re: How to avoid SUM0 or disable a rule ?
Posted by Vamshi Krishna <va...@gmail.com>.
It's done in the SqlToRelConverter.java:5427. I don't think there is a
way currently to disable it (i may be wrong).
There should be a configurable option to disable this.
-Vamshi
On Tue, Jun 11, 2019 at 2:31 PM Muhammad Gelbana <m....@gmail.com> wrote:
>
> I just cleared the reducible aggregate calls collection at runtime (to void
> the rule) and I'm still facing the same problem. This onviously has nothing
> to do with the rule. I'll investigate further. Thanks for your help.
>
> Thanks,
> Gelbana
>
>
> On Tue, Jun 11, 2019 at 8:16 PM Haisheng Yuan <h....@alibaba-inc.com>
> wrote:
>
> > 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....@gmail.com>
> > 日 期：2019年06月12日 01:46:28
> > 收件人：<de...@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
> >
> >
Re: How to avoid SUM0 or disable a rule ?
Posted by Muhammad Gelbana <m....@gmail.com>.
I just cleared the reducible aggregate calls collection at runtime (to void
the rule) and I'm still facing the same problem. This onviously has nothing
to do with the rule. I'll investigate further. Thanks for your help.
Thanks,
Gelbana
On Tue, Jun 11, 2019 at 8:16 PM Haisheng Yuan <h....@alibaba-inc.com>
wrote:
> 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....@gmail.com>
> 日 期：2019年06月12日 01:46:28
> 收件人：<de...@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
>
>
Re: How to avoid SUM0 or disable a rule ?
Posted by Haisheng Yuan <h....@alibaba-inc.com>.
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....@gmail.com>
日 期：2019年06月12日 01:46:28
收件人：<de...@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