Re: [DISCUSSION] Extension of Metadata Query
We have an interface to fetch the RelMetadataQuery in RelOptRuleCall [1], which I believe is the most common routine to query metadata during planning, I’m a little confused for your saying >The methods in RelMetadataQuery are for convenience only. If these methods in RelMetadataQuery are only for convenience, what is the other way I can query a metadata ? The MedataFactory from the RelOptCluster ? (Users would never expect to use this because it’s based on Java Reflection and has performance problem) [1] https://github.com/apache/calcite/blob/a3c56be7bccc58859524ba39e5b30b7078f97d00/core/src/main/java/org/apache/calcite/plan/RelOptRuleCall.java#L200 Best, Danny Chan 在 2019年6月12日 +0800 AM6:27，Julian Hyde ，写道： > The goal is that it should be possible to add a new kind of metadata. The > methods in RelMetadataQuery are for convenience only. So you should be able > to use your new kind of metadata, and existing ones, without modifying > RelMetadataQuery. > > If that is not possible, it’s a bug, and you should log a JIRA case. > > Given how the methods re-generate handlers (by catching exceptions and > modifying mutable private members) I can see that new kinds of metadata might > be difficult to add. > > Julian > > > > On Jun 9, 2019, at 7:54 PM, Yuzhao Chen wrote: > > > > Thanks, Stamatis > > > > You are right, this discussion came up due to CALCITE-2885, it is not about > > the performance problem, it is the extension of RelMetadataQuery, because > > we add all kinds of top interfaces in RelMetadataQuery, e.g. the row count > > query [1]. > > > > When we add a new RelMetadataProvider, a corresponding interface/method > > should be added into RelMetadataQuery, but for current RelOpeCluster impl, > > we could not do that(with a always default instance) > > > > As for the methods in RelMetadataProvider, i only saw the usage of > > #handlers in RelMetadataQuery, and saw the reference you pase, it seems not > > that relevant with this topic. What do you think ? > > > > [1] > > https://github.com/apache/calcite/blob/941cd4e9540e3ef9b7c15daee42831a0c63da8b9/core/src/main/java/org/apache/calcite/rel/metadata/RelMetadataQuery.java#L222 > > > > Best, > > Danny Chan > > 在 2019年6月5日 +0800 AM6:44，Stamatis Zampetakis ，写道： > > > Thanks for bringing this up Danny. > > > > > > I guess the discussion came up due to CALCITE-2885 [1]. Looking back into > > > it, I am not sure that the intention there is to make the RelMetadataQuery > > > pluggable. We could possibly solve the performance problem without > > > extending the RelMetadataQuery. We have to look again into this case. > > > > > > For more details regarding the existence of the two methods in > > > RelMetadataProvider have a look in CALCITE-604 [2]. More general for the > > > design of RelMetadataProvider you may find useful the description in [3]. > > > > > > Best, > > > Stamatis > > > > > > [1] https://issues.apache.org/jira/browse/CALCITE-2885 > > > [2] https://issues.apache.org/jira/browse/CALCITE-604 > > > [3] > > > https://web.archive.org/web/20140624040836/www.hydromatic.net/wiki/RelationalExpressionMetadata/ > > > > > > On Tue, Jun 4, 2019 at 7:48 PM Julian Hyde wrote: > > > > > > > > 1. Why we have 2 methods in RelMetadataProvider? > > > > > > > > The metadata system is complicated. We need to allow multiple handlers > > > > for any given call. So, making a metadata call involves multiple > > > > dispatch [1] based on the metadata method being called, the type of > > > > RelNode, and the handlers that are registered. Also it needs to cache > > > > results, and detect cycles. And the dispatch needs to be efficient, so > > > > we generate janino code to do the dispatch, and re-generate when new > > > > handlers or sub-classes of RelNode are added. > > > > > > > > I forget details, the two methods are basically required to allow us > > > > to generate code to do the dispatch. > > > > > > > > > 2. We should make the RelMetadataQuery in RelOptCluster pluggable. > > > > > > > > I disagree. RelMetadataQuery is not an extension point. Its sole > > > > purpose is to to keep state (the cache and cycle-checking). > > > > RelMetadataProvider is the extension point. (By analogy, if you are > > > > un-parsing an AST, you let each AST sub-class handle unparsing itself, > > > > but the unparsed text goes to a simple StringBuilder. RelMetadataQuery > > > > is in the role of the StringBuilder. In a complex system, it is nice > > > > to keep some of the components simple, or at least keep them to > > > > prescribed roles.) > > > > > > > > Julian > > > > > > > > [1] https://en.wikipedia.org/wiki/Multiple_dispatch > > > > > > > > On Sun, Jun 2, 2019 at 11:19 PM Yuzhao Chen > > > > wrote: > > > > > > > > > > Currently we provide answer to metadata query through > > > > RelMetadataProvider [1], there are some sub-classes of it: > > > > > > > > > > RelMetadataProvider > > > > > | > > > > > |- VolcanoRelMetadataProvider > > > > > |-
[jira] [Created] (CALCITE-3124) Infinite rule matching when AggregateRemoveRule is enabled for SUM0
Haisheng Yuan created CALCITE-3124:
--
Summary: Infinite rule matching when AggregateRemoveRule is
enabled for SUM0
Key: CALCITE-3124
URL: https://issues.apache.org/jira/browse/CALCITE-3124
Project: Calcite
Issue Type: Bug
Reporter: Haisheng Yuan
Make the following changes (uncomment return clause) to /AggregateRemoveRule,
the test case {{JdbcTest.testHavingNot2}} won't complete due to infinite rule
matching.
{code:diff}
--- a/core/src/main/java/org/apache/calcite/rel/rules/AggregateRemoveRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/AggregateRemoveRule.java
@@ -102,7 +102,7 @@ public void onMatch(RelOptRuleCall call) {
if (aggregation.getKind() == SqlKind.SUM0) {
// Bail out for SUM0 to avoid potential infinite rule matching,
// because it may be generated by transforming SUM aggregate
// function to SUM0 and COUNT.
-return;
+//return;
}
final SqlSplittableAggFunction splitter =
Objects.requireNonNull(
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
Re: Re: How to avoid SUM0 or disable a rule ?
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
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
> wrote:
>
>> Cool, can you create an issue for this bug?
>>
>> - Haisheng
>>
>> --
>> 发件人：Muhammad Gelbana
>> 日 期：2019年06月12日 02:39:20
>> 收件人：dev@calcite.apache.org (dev@calcite.apache.org)<
>> dev@calcite.apache.org>
>> 抄 送：Haisheng Yuan
>> 主 题：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.kris...@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
>>> 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
>>> > wrote:
>>> >
>>> > > Hi Gelbana,
>>> > >
>>> > > You can construct your own AggregateReduceFunctionsRule instance by
>>> > > specifying the functions you want to reduce:
>>> > >
>>> > > public AggregateReduceFunctionsRule(Class
>>> aggregateClass,
>>> > > RelBuilderFactory relBuilderFactory, EnumSet
>>> 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
>>> > > 日 期：2019年06月12日 01:46:28
>>> > > 收件人：
>>> > > 主 题：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: [DISCUSSION] Extension of Metadata Query
The goal is that it should be possible to add a new kind of metadata. The methods in RelMetadataQuery are for convenience only. So you should be able to use your new kind of metadata, and existing ones, without modifying RelMetadataQuery. If that is not possible, it’s a bug, and you should log a JIRA case. Given how the methods re-generate handlers (by catching exceptions and modifying mutable private members) I can see that new kinds of metadata might be difficult to add. Julian > On Jun 9, 2019, at 7:54 PM, Yuzhao Chen wrote: > > Thanks, Stamatis > > You are right, this discussion came up due to CALCITE-2885, it is not about > the performance problem, it is the extension of RelMetadataQuery, because we > add all kinds of top interfaces in RelMetadataQuery, e.g. the row count query > [1]. > > When we add a new RelMetadataProvider, a corresponding interface/method > should be added into RelMetadataQuery, but for current RelOpeCluster impl, we > could not do that(with a always default instance) > > As for the methods in RelMetadataProvider, i only saw the usage of #handlers > in RelMetadataQuery, and saw the reference you pase, it seems not that > relevant with this topic. What do you think ? > > [1] > https://github.com/apache/calcite/blob/941cd4e9540e3ef9b7c15daee42831a0c63da8b9/core/src/main/java/org/apache/calcite/rel/metadata/RelMetadataQuery.java#L222 > > Best, > Danny Chan > 在 2019年6月5日 +0800 AM6:44，Stamatis Zampetakis ，写道： >> Thanks for bringing this up Danny. >> >> I guess the discussion came up due to CALCITE-2885 [1]. Looking back into >> it, I am not sure that the intention there is to make the RelMetadataQuery >> pluggable. We could possibly solve the performance problem without >> extending the RelMetadataQuery. We have to look again into this case. >> >> For more details regarding the existence of the two methods in >> RelMetadataProvider have a look in CALCITE-604 [2]. More general for the >> design of RelMetadataProvider you may find useful the description in [3]. >> >> Best, >> Stamatis >> >> [1] https://issues.apache.org/jira/browse/CALCITE-2885 >> [2] https://issues.apache.org/jira/browse/CALCITE-604 >> [3] >> https://web.archive.org/web/20140624040836/www.hydromatic.net/wiki/RelationalExpressionMetadata/ >> >> On Tue, Jun 4, 2019 at 7:48 PM Julian Hyde wrote: >> 1. Why we have 2 methods in RelMetadataProvider? >>> >>> The metadata system is complicated. We need to allow multiple handlers >>> for any given call. So, making a metadata call involves multiple >>> dispatch [1] based on the metadata method being called, the type of >>> RelNode, and the handlers that are registered. Also it needs to cache >>> results, and detect cycles. And the dispatch needs to be efficient, so >>> we generate janino code to do the dispatch, and re-generate when new >>> handlers or sub-classes of RelNode are added. >>> >>> I forget details, the two methods are basically required to allow us >>> to generate code to do the dispatch. >>> 2. We should make the RelMetadataQuery in RelOptCluster pluggable. >>> >>> I disagree. RelMetadataQuery is not an extension point. Its sole >>> purpose is to to keep state (the cache and cycle-checking). >>> RelMetadataProvider is the extension point. (By analogy, if you are >>> un-parsing an AST, you let each AST sub-class handle unparsing itself, >>> but the unparsed text goes to a simple StringBuilder. RelMetadataQuery >>> is in the role of the StringBuilder. In a complex system, it is nice >>> to keep some of the components simple, or at least keep them to >>> prescribed roles.) >>> >>> Julian >>> >>> [1] https://en.wikipedia.org/wiki/Multiple_dispatch >>> >>> On Sun, Jun 2, 2019 at 11:19 PM Yuzhao Chen wrote: Currently we provide answer to metadata query through >>> RelMetadataProvider [1], there are some sub-classes of it: RelMetadataProvider | |- VolcanoRelMetadataProvider |- ChainedRelMetadataProvider/DefaultRelMetadataProvider |- HepRelMetadataProvider |- CachingRelMetadataProvider |- ReflectiveRelMetadataProvider |- JaninoRelMetadataProvider The RelMetadataProvider has two methods: #apply and #handlers, the >>> #apply method seems a programming interface and there is a demo code how we >>> can use it: RelMetadataProvider provider; LogicalFilter filter; RexNode predicate; Function function = provider.apply(LogicalFilter.class, Selectivity.class}; Selectivity selectivity = function.apply(filter); Double d = selectivity.selectivity(predicate); But let's see our RelOptCluster's member variables[2], there are >>> MetadataFactory and RelMetadataQuery which all can be used to query the >>> metadata, for MetadataFactory, there is a default impl named >>> MetadataFactoryImpl which will invoke RelMetadataProvider#apply internally, >>> for RelMetadataQuery, it will invoke RelMetadataProvider#handlers (finally
Re: Re: Select into Temporary Table
For historical reasons, perhaps. We need to parse and translate into CREATE TABLE AS SELECT... if we are going to support this syntax for Postgres and SQL Server. - Haisheng -- 发件人：Julian Hyde 日 期：2019年06月12日 05:41:38 收件人： 主 题：Re: Select into Temporary Table I’ve never understood why some SQL dialects have “SELECT ... INTO table”. What’s wrong with “INSERT INTO table SELECT ...”? Julian > On Jun 11, 2019, at 1:27 PM, Andrew O wrote: > > Does / should Calcite support select into expressions? E.g. I'm using v1.19 > with queries of the style: > > select * into temporary table "#myTempResults" from (select colA > from my Table where colA = 'abc') > > but these queries fail to parse at the "into" words in the expression. (I > have a calcite connection setup with SqlDdlParserImpl) > > Assuming this isn't currently supported, is this something that is in the > scope of the default Calcite code, or something that would need to be a > custom extension? > > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE, but > I have found where us this set / used. > > Also, as additional context > - my target DB for the queries is Postgres (so supports this SQL query > directly) > - also in the future I would like to support parsing / processing Cursor > related operations (e.g. Declare Cursor, fetch next, fetch next, etc.). > Although I'm sure if / where these would belong in Calcite. > > Thanks in advance > > Andrew
Re: Select into Temporary Table
In answer to your question, no I don't think Calcite should support SELECT INTO. On Tue, Jun 11, 2019 at 2:53 PM Andrew O wrote: > > Indeed, unfortunately in this case it's pre-generated SQL so unfortunately > is a fixed input style. > > On Tue, 11 Jun 2019, 22:41 Julian Hyde, wrote: > > > I’ve never understood why some SQL dialects have “SELECT ... INTO table”. > > What’s wrong with “INSERT INTO table SELECT ...”? > > > > Julian > > > > > On Jun 11, 2019, at 1:27 PM, Andrew O wrote: > > > > > > Does / should Calcite support select into expressions? E.g. I'm using > > v1.19 > > > with queries of the style: > > > > > > select * into temporary table "#myTempResults" from (select colA > > > from my Table where colA = 'abc') > > > > > > but these queries fail to parse at the "into" words in the expression. (I > > > have a calcite connection setup with SqlDdlParserImpl) > > > > > > Assuming this isn't currently supported, is this something that is in the > > > scope of the default Calcite code, or something that would need to be a > > > custom extension? > > > > > > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE, > > but > > > I have found where us this set / used. > > > > > > Also, as additional context > > > - my target DB for the queries is Postgres (so supports this SQL query > > > directly) > > > - also in the future I would like to support parsing / processing Cursor > > > related operations (e.g. Declare Cursor, fetch next, fetch next, etc.). > > > Although I'm sure if / where these would belong in Calcite. > > > > > > Thanks in advance > > > > > > Andrew > >
Re: Select into Temporary Table
Indeed, unfortunately in this case it's pre-generated SQL so unfortunately is a fixed input style. On Tue, 11 Jun 2019, 22:41 Julian Hyde, wrote: > I’ve never understood why some SQL dialects have “SELECT ... INTO table”. > What’s wrong with “INSERT INTO table SELECT ...”? > > Julian > > > On Jun 11, 2019, at 1:27 PM, Andrew O wrote: > > > > Does / should Calcite support select into expressions? E.g. I'm using > v1.19 > > with queries of the style: > > > > select * into temporary table "#myTempResults" from (select colA > > from my Table where colA = 'abc') > > > > but these queries fail to parse at the "into" words in the expression. (I > > have a calcite connection setup with SqlDdlParserImpl) > > > > Assuming this isn't currently supported, is this something that is in the > > scope of the default Calcite code, or something that would need to be a > > custom extension? > > > > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE, > but > > I have found where us this set / used. > > > > Also, as additional context > > - my target DB for the queries is Postgres (so supports this SQL query > > directly) > > - also in the future I would like to support parsing / processing Cursor > > related operations (e.g. Declare Cursor, fetch next, fetch next, etc.). > > Although I'm sure if / where these would belong in Calcite. > > > > Thanks in advance > > > > Andrew >
Re: Select into Temporary Table
I’ve never understood why some SQL dialects have “SELECT ... INTO table”. What’s wrong with “INSERT INTO table SELECT ...”? Julian > On Jun 11, 2019, at 1:27 PM, Andrew O wrote: > > Does / should Calcite support select into expressions? E.g. I'm using v1.19 > with queries of the style: > > select * into temporary table "#myTempResults" from (select colA > from my Table where colA = 'abc') > > but these queries fail to parse at the "into" words in the expression. (I > have a calcite connection setup with SqlDdlParserImpl) > > Assuming this isn't currently supported, is this something that is in the > scope of the default Calcite code, or something that would need to be a > custom extension? > > I do see in Schema.TableType there is an enum value of TEMPORARY_TABLE, but > I have found where us this set / used. > > Also, as additional context > - my target DB for the queries is Postgres (so supports this SQL query > directly) > - also in the future I would like to support parsing / processing Cursor > related operations (e.g. Declare Cursor, fetch next, fetch next, etc.). > Although I'm sure if / where these would belong in Calcite. > > Thanks in advance > > Andrew
Re: Re: How to avoid SUM0 or disable a rule ?
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
wrote:
> Cool, can you create an issue for this bug?
>
> - Haisheng
>
> --
> 发件人：Muhammad Gelbana
> 日 期：2019年06月12日 02:39:20
> 收件人：dev@calcite.apache.org (dev@calcite.apache.org) >
> 抄 送：Haisheng Yuan
> 主 题：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
> 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
>> 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
>> > wrote:
>> >
>> > > Hi Gelbana,
>> > >
>> > > You can construct your own AggregateReduceFunctionsRule instance by
>> > > specifying the functions you want to reduce:
>> > >
>> > > public AggregateReduceFunctionsRule(Class
>> aggregateClass,
>> > > RelBuilderFactory relBuilderFactory, EnumSet
>> 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
>> > > 日 期：2019年06月12日 01:46:28
>> > > 收件人：
>> > > 主 题：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 ?
Cool, can you create an issue for this bug?
- Haisheng
--
发件人：Muhammad Gelbana
日 期：2019年06月12日 02:39:20
收件人：dev@calcite.apache.org (dev@calcite.apache.org)
抄 送：Haisheng Yuan
主 题：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
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 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
> wrote:
>
> > Hi Gelbana,
> >
> > You can construct your own AggregateReduceFunctionsRule instance by
> > specifying the functions you want to reduce:
> >
> > public AggregateReduceFunctionsRule(Class
> > aggregateClass,
> > RelBuilderFactory relBuilderFactory, EnumSet
> > 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
> > 日 期：2019年06月12日 01:46:28
> > 收件人：
> > 主 题：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 ?
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 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
> wrote:
>
> > Hi Gelbana,
> >
> > You can construct your own AggregateReduceFunctionsRule instance by
> > specifying the functions you want to reduce:
> >
> > public AggregateReduceFunctionsRule(Class
> > aggregateClass,
> > RelBuilderFactory relBuilderFactory, EnumSet
> > 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
> > 日 期：2019年06月12日 01:46:28
> > 收件人：
> > 主 题：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 ?
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
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
> 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
> > wrote:
> >
> > > Hi Gelbana,
> > >
> > > You can construct your own AggregateReduceFunctionsRule instance by
> > > specifying the functions you want to reduce:
> > >
> > > public AggregateReduceFunctionsRule(Class
> aggregateClass,
> > > RelBuilderFactory relBuilderFactory, EnumSet
> 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
> > > 日 期：2019年06月12日 01:46:28
> > > 收件人：
> > > 主 题：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 ?
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
wrote:
> Hi Gelbana,
>
> You can construct your own AggregateReduceFunctionsRule instance by
> specifying the functions you want to reduce:
>
> public AggregateReduceFunctionsRule(Class aggregateClass,
> RelBuilderFactory relBuilderFactory, EnumSet 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
> 日 期：2019年06月12日 01:46:28
> 收件人：
> 主 题：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 ?
Hi Gelbana,
You can construct your own AggregateReduceFunctionsRule instance by specifying
the functions you want to reduce:
public AggregateReduceFunctionsRule(Class aggregateClass,
RelBuilderFactory relBuilderFactory, EnumSet 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
日 期：2019年06月12日 01:46:28
收件人：
主 题：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
