[jira] [Commented] (CALCITE-3970) Table-valued function TUMBLE uses non-standard syntax
[
https://issues.apache.org/jira/browse/CALCITE-3970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106971#comment-17106971
]
Viliam Durina commented on CALCITE-3970:
I didn't plan to do a PR.
> Table-valued function TUMBLE uses non-standard syntax
> -
>
> Key: CALCITE-3970
> URL: https://issues.apache.org/jira/browse/CALCITE-3970
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Viliam Durina
>Priority: Major
>
> The currently supported syntax is this:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE my_table, ...
> {code}
> But the SQL standard specifies that {{my_table}} must be in parentheses, such
> as here:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE(my_table), ...
> {code}
> The second syntax is currently rejected with:
> {code:none}
> Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
> Encountered "(" at line 1, column 33.
> Was expecting one of:
> ...
> ...
> ...
> ...
> ...
> {code}
> I'm not sure if the currently supported syntax is optional, but I think it's
> not.
> I followed this document:
> [http://standards.iso.org/ittf/PubliclyAvailableStandards/c069776_ISO_IEC_TR_19075-7_2017.zip]
> The {{TABLE}} clause acts in both ways: (1) to convert a table value to a
> table object (when used in the {{FROM}} clause to convert the function
> result) and (2) to convert a table object to a table value (when used to
> convert arguments to a function).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4000) Support OFFSET parameter in TUMBLE/HOP/SESSION table functions
[
https://issues.apache.org/jira/browse/CALCITE-4000?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106960#comment-17106960
]
Viliam Durina commented on CALCITE-4000:
There's no offset for SESSION.
> Support OFFSET parameter in TUMBLE/HOP/SESSION table functions
> ---
>
> Key: CALCITE-4000
> URL: https://issues.apache.org/jira/browse/CALCITE-4000
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Rui Wang
>Assignee: Rui Wang
>Priority: Major
>
> TUMBLE/HOP/SESSION have an optional "OFFSET" parameter to indicate how many
> time off from 0 for windowing.
> {code:java}
> TUMBLE(table, size [, offset])
> HOP(table, shift, size, [, offset])
> SESSION(table, gap, [, offset])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4001) Should support dialect when unparsing SqlDataTypeSpec
[ https://issues.apache.org/jira/browse/CALCITE-4001?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xurenhe updated CALCITE-4001: - Description: SqlBasicTypeNameSpec cannot cover all data types of other sql's engine, when doing SqlNode To SqlString, such as cast(id as bigint), but some sql-engine only support cast(id as long). I think there should be based on dialect, just like org.apache.calcite.sql.SqlDialect#unparseOffsetFetch. was: SqlBasicTypeNameSpec cannot cover all data types of other sql's engine, when doing SqlNode To SqlString, such as cast(id as bigint), but in some sql engine only support cast(id as long). I think there should be based on dialect, just like org.apache.calcite.sql.SqlDialect#unparseOffsetFetch. > Should support dialect when unparsing SqlDataTypeSpec > - > > Key: CALCITE-4001 > URL: https://issues.apache.org/jira/browse/CALCITE-4001 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Xurenhe >Priority: Minor > > SqlBasicTypeNameSpec cannot cover all data types of other sql's engine, when > doing SqlNode To SqlString, > such as cast(id as bigint), but some sql-engine only support cast(id as long). > I think there should be based on dialect, just like > org.apache.calcite.sql.SqlDialect#unparseOffsetFetch. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-4001) Should support dialect when unparsing SqlDataTypeSpec
Xurenhe created CALCITE-4001: Summary: Should support dialect when unparsing SqlDataTypeSpec Key: CALCITE-4001 URL: https://issues.apache.org/jira/browse/CALCITE-4001 Project: Calcite Issue Type: Improvement Components: core Reporter: Xurenhe SqlBasicTypeNameSpec cannot cover all data types of other sql's engine, when doing SqlNode To SqlString, such as cast(id as bigint), but in some sql engine only support cast(id as long). I think there should be based on dialect, just like org.apache.calcite.sql.SqlDialect#unparseOffsetFetch. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4000) Support OFFSET parameter in TUMBLE/HOP/SESSION table functions
[
https://issues.apache.org/jira/browse/CALCITE-4000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rui Wang updated CALCITE-4000:
--
Description:
TUMBLE/HOP/SESSION have an optional "OFFSET" parameter to indicate how many
time off from 0 for windowing.
{code:java}
TUMBLE(table, size [, offset])
HOP(table, shift, size, [, offset])
SESSION(table, gap, [, offset])
{code}
was:
TUMBLE/HOP/SESSION have an optional "OFFSET" parameter to indicate how many
time off from 0 for windowing.
TUMBLE(table, size [, offset])
HOP(table, shift, size, [, offset])
SESSION(table, gap, [, offset])
> Support OFFSET parameter in TUMBLE/HOP/SESSION table functions
> ---
>
> Key: CALCITE-4000
> URL: https://issues.apache.org/jira/browse/CALCITE-4000
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Rui Wang
>Assignee: Rui Wang
>Priority: Major
>
> TUMBLE/HOP/SESSION have an optional "OFFSET" parameter to indicate how many
> time off from 0 for windowing.
> {code:java}
> TUMBLE(table, size [, offset])
> HOP(table, shift, size, [, offset])
> SESSION(table, gap, [, offset])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4000) Support OFFSET parameter in TUMBLE/HOP/SESSION table functions
[ https://issues.apache.org/jira/browse/CALCITE-4000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rui Wang updated CALCITE-4000: -- Summary: Support OFFSET parameter in TUMBLE/HOP/SESSION table functions (was: Support the offset parameters for TUMBLE/HOP/SESSION table functions ) > Support OFFSET parameter in TUMBLE/HOP/SESSION table functions > --- > > Key: CALCITE-4000 > URL: https://issues.apache.org/jira/browse/CALCITE-4000 > Project: Calcite > Issue Type: Sub-task >Reporter: Rui Wang >Assignee: Rui Wang >Priority: Major > > TUMBLE/HOP/SESSION have an optional "OFFSET" parameter to indicate how many > time off from 0 for windowing. > TUMBLE(table, size [, offset]) > HOP(table, shift, size, [, offset]) > SESSION(table, gap, [, offset]) -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-4000) Support the offset parameters for TUMBLE/HOP/SESSION table functions
Rui Wang created CALCITE-4000: - Summary: Support the offset parameters for TUMBLE/HOP/SESSION table functions Key: CALCITE-4000 URL: https://issues.apache.org/jira/browse/CALCITE-4000 Project: Calcite Issue Type: Sub-task Reporter: Rui Wang Assignee: Rui Wang TUMBLE/HOP/SESSION have an optional "OFFSET" parameter to indicate how many time off from 0 for windowing. TUMBLE(table, size [, offset]) HOP(table, shift, size, [, offset]) SESSION(table, gap, [, offset]) -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106893#comment-17106893
]
TANG Wen-hui edited comment on CALCITE-3998 at 5/14/20, 5:10 AM:
-
The commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
would not change the SqlTypeName, the SqlTypeName always stay the same with
the operand. And the changes in this commit which are related to the first plan
are that the commit has changed AbstractSumSplitter#singleton (but it has not
changed the SqlTypeName too) when matching AggregateRemoveRule.
I'm curious. why the return type of "sum(n1)" is Bigint when n1 is integer, How
does it do that? Does this change occurs in Calcite or in HerdDB?
was (Author: winipanda):
The commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
would not change the SqlTypeName, the SqlTypeName always stay the same with
the operand. And the changes in this commit which are related to the first plan
are that the commit has changed AbstractSumSplitter#singleton (but it has not
changed the SqlTypeName too) when matching AggregateRemoveRule.
I'm curious. why the return type of "sum(n)" is Bigint when n is integer, How
does it do that? Does this change occurs in Calcite or in HerdDB?
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> {noformat}
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> {noformat}
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> {noformat}
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0
[jira] [Comment Edited] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106893#comment-17106893
]
TANG Wen-hui edited comment on CALCITE-3998 at 5/14/20, 5:10 AM:
-
The commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
would not change the SqlTypeName, the SqlTypeName always stay the same with
the operand. And the changes in this commit which are related to the first plan
are that the commit has changed AbstractSumSplitter#singleton (but it has not
changed the SqlTypeName too) when matching AggregateRemoveRule.
I'm curious. why the return type of "sum(n)" is Bigint when n is integer, How
does it do that? Does this change occurs in Calcite or in HerdDB?
was (Author: winipanda):
The commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
would not change the SqlTypeName, the SqlTypeName always stay the same with
the operand. And the changes in this commit which are related to the first plan
are that the commit has changed AbstractSumSplitter#singleton (but it has not
changed the SqlTypeName too) when matching AggregateRemoveRule.
I'm curious. why the return type of sum(n) is Bigint when n is integer, How
does it do that? Does this change occurs in Calcite or in HerdDB?
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> {noformat}
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> {noformat}
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> {noformat}
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 i
[jira] [Comment Edited] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106893#comment-17106893
]
TANG Wen-hui edited comment on CALCITE-3998 at 5/14/20, 5:09 AM:
-
The commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
would not change the SqlTypeName, the SqlTypeName always stay the same with
the operand. And the changes in this commit which are related to the first plan
are that the commit has changed AbstractSumSplitter#singleton (but it has not
changed the SqlTypeName too) when matching AggregateRemoveRule.
I'm curious. why the return type of sum(n) is Bigint when n is integer, How
does it do that? Does this change occurs in Calcite or in HerdDB?
was (Author: winipanda):
The commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
would not change the SqlTypeName, the SqlTypeName always stay the same with
the operand. And the changes in this commit which are related to the first plan
are that the commit has changed AbstractSumSplitter#singleton (but it has not
changed the SqlTypeName too) when matching AggregateRemoveRule.
I'm curious. why the return type of sum is Bigint when n is integer, How does
it do that? Does this change occurs in Calcite or in HerdDB?
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> {noformat}
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> {noformat}
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> {noformat}
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, i
[jira] [Comment Edited] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106893#comment-17106893
]
TANG Wen-hui edited comment on CALCITE-3998 at 5/14/20, 5:09 AM:
-
The commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
would not change the SqlTypeName, the SqlTypeName always stay the same with
the operand. And the changes in this commit which are related to the first plan
are that the commit has changed AbstractSumSplitter#singleton (but it has not
changed the SqlTypeName too) when matching AggregateRemoveRule.
I'm curious. why the return type of sum is Bigint when n is integer, How does
it do that? Does this change occurs in Calcite or in HerdDB?
was (Author: winipanda):
The commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
would not change the SqlTypeName, the SqlTypeName always stay the same with
the operand. And the changes in this commit which are related to the first plan
are that the commit has changed AbstractSumSplitter#singleton (but it has not
changed the SqlTypeName too) when matching AggregateRemoveRule.
I'm curious. why the return type of sum(n) is Bigint when n is integer, How
does it do that? Does this change occurs in Calcite or in HerdDB?
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> {noformat}
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> {noformat}
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> {noformat}
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, i
[jira] [Commented] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106893#comment-17106893
]
TANG Wen-hui commented on CALCITE-3998:
---
The commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
would not change the SqlTypeName, the SqlTypeName always stay the same with
the operand. And the changes in this commit which are related to the first plan
are that the commit has changed AbstractSumSplitter#singleton (but it has not
changed the SqlTypeName too) when matching AggregateRemoveRule.
I'm curious. why the return type of sum(n) is Bigint when n is integer, How
does it do that? Does this change occurs in Calcite or in HerdDB?
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> {noformat}
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> {noformat}
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> {noformat}
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1294
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[1]]): rowcount =
> 2.0, cumulative cost = {0.012 rows, 0.018002 cpu, 0.0 io}, id =
> 1265
> {noformat}
> This is the test on HerdDB
> https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/sql/SimplerPlannerTest.java#L237
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106848#comment-17106848
]
Feng Zhu commented on CALCITE-3998:
---
{quote}I also noted that sometimes the type of sum(N) where N is an INTEGER
column sometimes it is now reported by Calcite as INTEGER and sometimes as a
BIGINT. In 1.22 every time is reported as BIGINT.{quote}
I guess this non-deterministic behavior may be related with planner and rules.
_RelDataTypeSystem.deriveSumType_ is deterministic and in calcite-1.22.0, it
always returns the argument type.
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> {noformat}
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> {noformat}
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> {noformat}
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1294
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[1]]): rowcount =
> 2.0, cumulative cost = {0.012 rows, 0.018002 cpu, 0.0 io}, id =
> 1265
> {noformat}
> This is the test on HerdDB
> https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/sql/SimplerPlannerTest.java#L237
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106837#comment-17106837
]
TANG Wen-hui commented on CALCITE-3998:
---
I will check whether this issue is related with commit
[https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4].
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> {noformat}
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> {noformat}
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> {noformat}
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1294
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[1]]): rowcount =
> 2.0, cumulative cost = {0.012 rows, 0.018002 cpu, 0.0 io}, id =
> 1265
> {noformat}
> This is the test on HerdDB
> https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/sql/SimplerPlannerTest.java#L237
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3999) Simplify DialectPool implementation using Guava cache
[
https://issues.apache.org/jira/browse/CALCITE-3999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106808#comment-17106808
]
Jesus Camacho Rodriguez commented on CALCITE-3999:
--
I have created a PR in https://github.com/apache/calcite/pull/1977/.
> Simplify DialectPool implementation using Guava cache
> -
>
> Key: CALCITE-3999
> URL: https://issues.apache.org/jira/browse/CALCITE-3999
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
>Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> JdbcUtils contains a pool to cache SqlDialect objects. Currently, it relies
> on multiple maps and a synchronized {{get}} method. Although I am not very
> familiar with that code, it seems the implementation could be made simpler
> and more efficient by using a Guava cache. In addition, since we would not
> have a single synchronized get method, multiple threads could concurrently
> create dialects for distinct data sources.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3999) Simplify DialectPool implementation using Guava cache
[
https://issues.apache.org/jira/browse/CALCITE-3999?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jesus Camacho Rodriguez updated CALCITE-3999:
-
Summary: Simplify DialectPool implementation using Guava cache (was:
Simplify DialectPool implementation)
> Simplify DialectPool implementation using Guava cache
> -
>
> Key: CALCITE-3999
> URL: https://issues.apache.org/jira/browse/CALCITE-3999
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
>Priority: Major
>
> JdbcUtils contains a pool to cache SqlDialect objects. Currently, it relies
> on multiple maps and a synchronized {{get}} method. Although I am not very
> familiar with that code, it seems the implementation could be made simpler
> and more efficient by using a Guava cache. In addition, since we would not
> have a single synchronized get method, multiple threads could concurrently
> create dialects for distinct data sources.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3999) Simplify DialectPool implementation
Jesus Camacho Rodriguez created CALCITE-3999:
Summary: Simplify DialectPool implementation
Key: CALCITE-3999
URL: https://issues.apache.org/jira/browse/CALCITE-3999
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Jesus Camacho Rodriguez
Assignee: Jesus Camacho Rodriguez
JdbcUtils contains a pool to cache SqlDialect objects. Currently, it relies on
multiple maps and a synchronized {{get}} method. Although I am not very
familiar with that code, it seems the implementation could be made simpler and
more efficient by using a Guava cache. In addition, since we would not have a
single synchronized get method, multiple threads could concurrently create
dialects for distinct data sources.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3977) RelDecorrelator does not resolve correlation variable with field accesses
[
https://issues.apache.org/jira/browse/CALCITE-3977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106701#comment-17106701
]
Stamatis Zampetakis commented on CALCITE-3977:
--
I think for a long time the optimizer, rules, and other parts in Calcite were
relying in the assumption that struct types are flattened (e.g., using
RelStructuredTypeFlattener). In this spirit, maybe adding a projection before
the filter in the plan above can help.
> RelDecorrelator does not resolve correlation variable with field accesses
> -
>
> Key: CALCITE-3977
> URL: https://issues.apache.org/jira/browse/CALCITE-3977
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Thomas Rebele
>Priority: Major
> Attachments: Calcite3977.java
>
>
> The RelCorrelator seems to have problems with some plans that contain a field
> access (probably a RexFieldAccess, but I haven't looked further into it). In
> this ticket there's a filter on *$cor0.birthPlace.city*.
> Here the complete plan:
> {code:java}
> before decorrelate
> LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{}])
> LogicalTableScan(table=[[bookstore, authors]])
> LogicalFilter(condition=[=('Munich', $cor0.birthPlace.city)])
> LogicalTableScan(table=[[bookstore, authors]])
> after decorrelate
> LogicalJoin(condition=[=($2, $8)], joinType=[left])
> LogicalTableScan(table=[[bookstore, authors]])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalFilter(condition=[=('Munich', $cor0.birthPlace.city)])
> LogicalTableScan(table=[[bookstore, authors]])
> LogicalAggregate(group=[{0}])
> LogicalProject(birthPlace=[$2])
> LogicalTableScan(table=[[bookstore, authors]])
> {code}
> There seem to be two problems:
> * The LogicalCorrelate has been removed, but the $cor0.birthPlace.city is
> still in the filter condition.
> * The inner join does not seem to be necessary. If it is, could somebody
> explain, why?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3994) RexSimplify reduce concat calls with constant arguments
[
https://issues.apache.org/jira/browse/CALCITE-3994?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106695#comment-17106695
]
Stamatis Zampetakis commented on CALCITE-3994:
--
I agree with [~julianhyde]. I have the impression that this case is already
handled by {{RexExecutor}} which is normally passed in {{RexSimplify}}. If you
are using the {{ReduceExpressionsRule}} then maybe you did not set an executor
for the planner (check {{RelOptPlanner#setExecutor}}).
> RexSimplify reduce concat calls with constant arguments
> ---
>
> Key: CALCITE-3994
> URL: https://issues.apache.org/jira/browse/CALCITE-3994
> Project: Calcite
> Issue Type: Improvement
>Affects Versions: 1.21.0
>Reporter: Jess Balint
>Priority: Minor
>
> would be good to implement support for string concat in RexSimplify
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106679#comment-17106679
]
Haisheng Yuan commented on CALCITE-3979:
No problem, I think we can get it into 1.23.0.
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106678#comment-17106678
]
Zoltan Haindrich commented on CALCITE-3979:
---
[~hyuan] please consider this for 1.23.0 - it's a bug reported last week - but
it was probably there since 1.18 ; the pr is ready/etc.
if you think it's too much - we can leave it for 1.24
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106670#comment-17106670
]
Zoltan Haindrich commented on CALCITE-3979:
---
I see - this logic is org.eigenbase old - so it was there from the begining;
and it was most probably worked more or less correctly until RexNode.equals got
fixed.
I've taken a look at other places - I've seen a few interesting situations; but
they should work correctly.
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3996) Fix NPE when query syntax is invalid
[ https://issues.apache.org/jira/browse/CALCITE-3996?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=1710#comment-1710 ] Laurent Goujon commented on CALCITE-3996: - Can you please provide a more detailed description? > Fix NPE when query syntax is invalid > > > Key: CALCITE-3996 > URL: https://issues.apache.org/jira/browse/CALCITE-3996 > Project: Calcite > Issue Type: Bug >Reporter: James Kim >Priority: Critical > > Fix NPE when query syntax is invalid -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-2997) Avoid pushing down join condition in SqlToRelConverter
[
https://issues.apache.org/jira/browse/CALCITE-2997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106658#comment-17106658
]
Julian Hyde commented on CALCITE-2997:
--
I agree, [~swtalbot]. I have re-opened the issue. It should not have been
closed as 'invalid', because the requirement is a valid ask.
We're making ever more use of RelBuilder in SqlToRelConverter, and RelBuilder
performs various rewrites/optimizations as it goes. Some of these rewrites are
essential for the integrity of {{RelNode}}/{{RexNode}} data structure(e.g. the
assumption that {{(AND x (AND y z))}} is flattened to {{(AND x y z)}}), but
others are deemed "almost certainly beneficial".
This rewrite is of the latter category. We should make it possible to disable
such rewrites individually.
In this case (see
[code|https://github.com/apache/calcite/blob/e44beba286ea9049c5fd00c3a3b0e4a4f1c03356/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L2617]),
there is a {{RelBuilder}} involved but the rewrite is done by the
{{SqlToRelConverter}}. The ideal fix would move the rewrite code into
{{RelBuilder}}, and add a config parameter to {{RelBuilder}} so that people can
disable it. Less ideally, we would leave the code in {{SqlToRelConverter}} and
add a config parameter to {{SqlToRelConverter}}.
> Avoid pushing down join condition in SqlToRelConverter
> --
>
> Key: CALCITE-2997
> URL: https://issues.apache.org/jira/browse/CALCITE-2997
> Project: Calcite
> Issue Type: Bug
>Reporter: Jin Xing
>Assignee: Julian Hyde
>Priority: Major
>
> In current code, *SqlToRelConverter:createJoin* is calling
> *RelOptUtil.pushDownJoinConditions* for optimization. And we can find below
> conversion from *SqlNode* to *RelNode*:
> {code:java}
> SqlNode:
> select * from A join B on A.x = B.x * 2
> RelNode (Logical-Plan):
> Join (condition:col0=col1)
> |-Project(x as col0)
> | |-Scan(A)
> |-Project(x * 2 as col1)
> |-Scan(B){code}
> As we can see the logical plan(*RelNode*) posted above is not the pure
> reflection of the original SQL String(*SqlNode*). The optimization is mixed
> into the phase on which AST is converted to Logical-Plan. Actually optimizing
> rule of JoinPushExpressionsRule is doing exactly the same kind of thing.
> Shall we just keep the optimization inside Optimized-Logical-Plan ? I mean
> shall we avoid calling *RelOptUtil.pushDownJoinConditions* in
> *SqlToRelConverter:createJoin*
> I raised this issue because that we are doing something based on the
> Logical-Plan. And it makes us really confused that the Logical-Plan doesn't
> corresponds to SqlNode.
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Enrico Olivelli updated CALCITE-3998:
-
Description:
I also noted that sometimes the type of sum(N) where N is an INTEGER column
sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
In 1.22 every time is reported as BIGINT.
So we have another test failing.
SELECT sum(n1), count(*) as cc, k1
FROM tblspace1.tsql
GROUP by k1
ORDER BY sum(n1)
Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
prefer to see it as a BIGINT in order to prevent overflows
Here are the plans:
{noformat}
INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
k1 ORDER BY sum(n1) -- Logical Plan
LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
{10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost = {4.0
rows, 7.0 cpu, 0.0 io}, id = 1034
LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
k1 ORDER BY sum(n1) -- Best Plan
EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost = {5.0
rows, 31.0 cpu, 0.0 io}, id = 1245
EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
cpu, 0.0 io}, id = 1243
BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]): rowcount
= 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
{noformat}
Within the same test case with the same tables the result of this query is not
changed
SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
tblspace1.tsql -- Logical Plan
{noformat}
LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io}, id
= 1253
LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
cpu, 0.0 io}, id = 1252
LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
tblspace1.tsql -- Best Plan
EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io}, id
= 1295
EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0 cpu,
0.0 io}, id = 1294
BindableTableScan(table=[[tblspace1, tsql]], projects=[[1]]): rowcount =
2.0, cumulative cost = {0.012 rows, 0.018002 cpu, 0.0 io}, id = 1265
{noformat}
This is the test on HerdDB
https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/sql/SimplerPlannerTest.java#L237
was:
I also noted that sometimes the type of sum(N) where N is an INTEGER column
sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
In 1.22 every time is reported as BIGINT.
So we have another test failing.
SELECT sum(n1), count(*) as cc, k1
FROM tblspace1.tsql
GROUP by k1
ORDER BY sum(n1)
Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
prefer to see it as a BIGINT in order to prevent overflows
Here are the plans:
INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
k1 ORDER BY sum(n1) -- Logical Plan
LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
{10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost = {4.0
rows, 7.0 cpu, 0.0 io}, id = 1034
LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
k1 ORDER BY sum(n1) -- Bes
[jira] [Updated] (CALCITE-3993) Add isDefault(), keys(), keyBits() to RelTrait interface
[
https://issues.apache.org/jira/browse/CALCITE-3993?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Haisheng Yuan updated CALCITE-3993:
---
Description:
It is an extremely frequent usage to check whether the distribution is default
(ANY), the collation is default (EMPTY).
Also add the following to RelCollation
{code:java}
ImmutableIntList getKeys();
{code}
was:
It is an extremely frequent usage to check whether the distribution is default
(ANY), the collation is default (EMPTY).
Also add the following to RelDistribution and RelCollation
{code:java}
ImmutableIntList getKeys();
ImmutableBitSet getKeysBits();
{code}
> Add isDefault(), keys(), keyBits() to RelTrait interface
>
>
> Key: CALCITE-3993
> URL: https://issues.apache.org/jira/browse/CALCITE-3993
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> It is an extremely frequent usage to check whether the distribution is
> default (ANY), the collation is default (EMPTY).
> Also add the following to RelCollation
> {code:java}
> ImmutableIntList getKeys();
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3993) Add isDefault(), keys(), keyBits() to RelTrait interface
[
https://issues.apache.org/jira/browse/CALCITE-3993?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106632#comment-17106632
]
Haisheng Yuan commented on CALCITE-3993:
I mean, in downstream system, not in Calcite, :). Calcite's Enumerble doesn't
deal with traits much.
> Add isDefault(), keys(), keyBits() to RelTrait interface
>
>
> Key: CALCITE-3993
> URL: https://issues.apache.org/jira/browse/CALCITE-3993
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> It is an extremely frequent usage to check whether the distribution is
> default (ANY), the collation is default (EMPTY).
> Also add the following to RelDistribution and RelCollation
> {code:java}
> ImmutableIntList getKeys();
> ImmutableBitSet getKeysBits();
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3993) Add isDefault(), keys(), keyBits() to RelTrait interface
[
https://issues.apache.org/jira/browse/CALCITE-3993?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106628#comment-17106628
]
Xiening Dai commented on CALCITE-3993:
--
I understand it's new. You claim it's an extremely frequent usage case to check
if distribution/collation is default. But in your PR, it's not been used
anywhere at all.
> Add isDefault(), keys(), keyBits() to RelTrait interface
>
>
> Key: CALCITE-3993
> URL: https://issues.apache.org/jira/browse/CALCITE-3993
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> It is an extremely frequent usage to check whether the distribution is
> default (ANY), the collation is default (EMPTY).
> Also add the following to RelDistribution and RelCollation
> {code:java}
> ImmutableIntList getKeys();
> ImmutableBitSet getKeysBits();
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3993) Add isDefault(), keys(), keyBits() to RelTrait interface
[
https://issues.apache.org/jira/browse/CALCITE-3993?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106623#comment-17106623
]
Haisheng Yuan commented on CALCITE-3993:
It just adds the method. I didn't use the API in the code.
> Add isDefault(), keys(), keyBits() to RelTrait interface
>
>
> Key: CALCITE-3993
> URL: https://issues.apache.org/jira/browse/CALCITE-3993
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> It is an extremely frequent usage to check whether the distribution is
> default (ANY), the collation is default (EMPTY).
> Also add the following to RelDistribution and RelCollation
> {code:java}
> ImmutableIntList getKeys();
> ImmutableBitSet getKeysBits();
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3993) Add isDefault(), keys(), keyBits() to RelTrait interface
[
https://issues.apache.org/jira/browse/CALCITE-3993?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106620#comment-17106620
]
Xiening Dai commented on CALCITE-3993:
--
I don't see the new isDefault() API being used in your PR. Did I miss anything?
> Add isDefault(), keys(), keyBits() to RelTrait interface
>
>
> Key: CALCITE-3993
> URL: https://issues.apache.org/jira/browse/CALCITE-3993
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> It is an extremely frequent usage to check whether the distribution is
> default (ANY), the collation is default (EMPTY).
> Also add the following to RelDistribution and RelCollation
> {code:java}
> ImmutableIntList getKeys();
> ImmutableBitSet getKeysBits();
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Reopened] (CALCITE-3996) Fix NPE when query syntax is invalid
[ https://issues.apache.org/jira/browse/CALCITE-3996?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Kim reopened CALCITE-3996: > Fix NPE when query syntax is invalid > > > Key: CALCITE-3996 > URL: https://issues.apache.org/jira/browse/CALCITE-3996 > Project: Calcite > Issue Type: Bug >Reporter: James Kim >Priority: Critical > > Fix NPE when query syntax is invalid -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Reopened] (CALCITE-2997) Avoid pushing down join condition in SqlToRelConverter
[
https://issues.apache.org/jira/browse/CALCITE-2997?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde reopened CALCITE-2997:
--
Assignee: Julian Hyde
> Avoid pushing down join condition in SqlToRelConverter
> --
>
> Key: CALCITE-2997
> URL: https://issues.apache.org/jira/browse/CALCITE-2997
> Project: Calcite
> Issue Type: Bug
>Reporter: Jin Xing
>Assignee: Julian Hyde
>Priority: Major
>
> In current code, *SqlToRelConverter:createJoin* is calling
> *RelOptUtil.pushDownJoinConditions* for optimization. And we can find below
> conversion from *SqlNode* to *RelNode*:
> {code:java}
> SqlNode:
> select * from A join B on A.x = B.x * 2
> RelNode (Logical-Plan):
> Join (condition:col0=col1)
> |-Project(x as col0)
> | |-Scan(A)
> |-Project(x * 2 as col1)
> |-Scan(B){code}
> As we can see the logical plan(*RelNode*) posted above is not the pure
> reflection of the original SQL String(*SqlNode*). The optimization is mixed
> into the phase on which AST is converted to Logical-Plan. Actually optimizing
> rule of JoinPushExpressionsRule is doing exactly the same kind of thing.
> Shall we just keep the optimization inside Optimized-Logical-Plan ? I mean
> shall we avoid calling *RelOptUtil.pushDownJoinConditions* in
> *SqlToRelConverter:createJoin*
> I raised this issue because that we are doing something based on the
> Logical-Plan. And it makes us really confused that the Logical-Plan doesn't
> corresponds to SqlNode.
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106601#comment-17106601
]
Julian Hyde commented on CALCITE-3979:
--
bq. do you see any reasons to keep the cast removal logic in
ReduceExpressionsRule?
No. Frankly, I have not looked at this code closely, so my ask is about
process: We screwed up. While you fix this, please review how this bad code got
in there in the first place, and make sure we are solving the problem, not the
symptoms.
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106593#comment-17106593
]
Haisheng Yuan commented on CALCITE-3998:
There is a change on the method, no sure it is related or not:
https://github.com/apache/calcite/commit/e081c5b4227a57defe47246d8ff3e6f7cce838e4
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1294
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[1]]): rowcount =
> 2.0, cumulative cost = {0.012 rows, 0.018002 cpu, 0.0 io}, id =
> 1265
> This is the test on HerdDB
> https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/sql/SimplerPlannerTest.java#L237
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-2997) Avoid pushing down join condition in SqlToRelConverter
[
https://issues.apache.org/jira/browse/CALCITE-2997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106592#comment-17106592
]
Steven Talbot commented on CALCITE-2997:
cc [~julianhyde]
I'd move to reopen this. Even if the original case perhaps does not need to
turn this off, I think there are valid cases where we would want to add a
config parameter to disable this behavior. While the push down provides a more
optimized plan, it also creates a more complex tree of RelNodes and writes much
more verbose SQL if those RelNodes are translated back to SQL with the Projects
that this rule creates. I could see a variety of cases where one would prefer
for those reasons to disable this behavior.
> Avoid pushing down join condition in SqlToRelConverter
> --
>
> Key: CALCITE-2997
> URL: https://issues.apache.org/jira/browse/CALCITE-2997
> Project: Calcite
> Issue Type: Bug
>Reporter: Jin Xing
>Priority: Major
>
> In current code, *SqlToRelConverter:createJoin* is calling
> *RelOptUtil.pushDownJoinConditions* for optimization. And we can find below
> conversion from *SqlNode* to *RelNode*:
> {code:java}
> SqlNode:
> select * from A join B on A.x = B.x * 2
> RelNode (Logical-Plan):
> Join (condition:col0=col1)
> |-Project(x as col0)
> | |-Scan(A)
> |-Project(x * 2 as col1)
> |-Scan(B){code}
> As we can see the logical plan(*RelNode*) posted above is not the pure
> reflection of the original SQL String(*SqlNode*). The optimization is mixed
> into the phase on which AST is converted to Logical-Plan. Actually optimizing
> rule of JoinPushExpressionsRule is doing exactly the same kind of thing.
> Shall we just keep the optimization inside Optimized-Logical-Plan ? I mean
> shall we avoid calling *RelOptUtil.pushDownJoinConditions* in
> *SqlToRelConverter:createJoin*
> I raised this issue because that we are doing something based on the
> Logical-Plan. And it makes us really confused that the Logical-Plan doesn't
> corresponds to SqlNode.
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3994) RexSimplify reduce concat calls with constant arguments
[ https://issues.apache.org/jira/browse/CALCITE-3994?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106583#comment-17106583 ] Julian Hyde commented on CALCITE-3994: -- Why is this not a job for constant reduction? > RexSimplify reduce concat calls with constant arguments > --- > > Key: CALCITE-3994 > URL: https://issues.apache.org/jira/browse/CALCITE-3994 > Project: Calcite > Issue Type: Improvement >Affects Versions: 1.21.0 >Reporter: Jess Balint >Priority: Minor > > would be good to implement support for string concat in RexSimplify -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106576#comment-17106576
]
Julian Hyde edited comment on CALCITE-3998 at 5/13/20, 6:51 PM:
Can you use \{code\} or \{noformat\} markup and fix the emojis in your
description.
As of CALCITE-1945 the type of SUM can be controlled via
{{RelDataTypeSystem.deriveSumType}}. I don't know whether anything has changed
recently, or whether you could use this method to get the results you want.
was (Author: julianhyde):
Can you use {code} or {format} markup and fix the emojis in your description.
As of CALCITE-1945 the type of SUM can be controlled via
{{RelDataTypeSystem.deriveSumType}}. I don't know whether anything has changed
recently, or whether you could use this method to get the results you want.
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1294
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[1]]): rowcount =
> 2.0, cumulative cost = {0.012 rows, 0.018002 cpu, 0.0 io}, id =
> 1265
> This is the test on HerdDB
> https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/sql/SimplerPlannerTest.java#L237
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
[
https://issues.apache.org/jira/browse/CALCITE-3998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106576#comment-17106576
]
Julian Hyde commented on CALCITE-3998:
--
Can you use {code} or {format} markup and fix the emojis in your description.
As of CALCITE-1945 the type of SUM can be controlled via
{{RelDataTypeSystem.deriveSumType}}. I don't know whether anything has changed
recently, or whether you could use this method to get the results you want.
> Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
>
>
> Key: CALCITE-3998
> URL: https://issues.apache.org/jira/browse/CALCITE-3998
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Enrico Olivelli
>Priority: Blocker
>
> I also noted that sometimes the type of sum(N) where N is an INTEGER column
> sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
> In 1.22 every time is reported as BIGINT.
> So we have another test failing.
> SELECT sum(n1), count(*) as cc, k1
> FROM tblspace1.tsql
> GROUP by k1
> ORDER BY sum(n1)
> Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
> prefer to see it as a BIGINT in order to prevent overflows
> Here are the plans:
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
> LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
> cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
> LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
> 2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
> LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost =
> {4.0 rows, 7.0 cpu, 0.0 io}, id = 1034
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0,
> cumulative cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
> May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
> k1 ORDER BY sum(n1) -- Best Plan
> EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
> {5.0 rows, 31.0 cpu, 0.0 io}, id = 1245
> EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
> cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1243
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]):
> rowcount = 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
> Within the same test case with the same tables the result of this query is
> not changed
> SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Logical Plan
> LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io},
> id = 1253
> LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
> cpu, 0.0 io}, id = 1252
> LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
> cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
> May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
> INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
> tblspace1.tsql -- Best Plan
> EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
> rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io},
> id = 1295
> EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
> cpu, 0.0 io}, id = 1294
> BindableTableScan(table=[[tblspace1, tsql]], projects=[[1]]): rowcount =
> 2.0, cumulative cost = {0.012 rows, 0.018002 cpu, 0.0 io}, id =
> 1265
> This is the test on HerdDB
> https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/sql/SimplerPlannerTest.java#L237
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3970) Table-valued function TUMBLE uses non-standard syntax
[
https://issues.apache.org/jira/browse/CALCITE-3970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106493#comment-17106493
]
Rui Wang commented on CALCITE-3970:
---
While we are continuing discussing, an actionable task is to support `TABLE()`
syntax, cause it's used in standard already.
Hi Viliam, do you have a plan to open a PR (if so I can help do code review).
If not I am willing to make a change.
> Table-valued function TUMBLE uses non-standard syntax
> -
>
> Key: CALCITE-3970
> URL: https://issues.apache.org/jira/browse/CALCITE-3970
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Viliam Durina
>Priority: Major
>
> The currently supported syntax is this:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE my_table, ...
> {code}
> But the SQL standard specifies that {{my_table}} must be in parentheses, such
> as here:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE(my_table), ...
> {code}
> The second syntax is currently rejected with:
> {code:none}
> Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
> Encountered "(" at line 1, column 33.
> Was expecting one of:
> ...
> ...
> ...
> ...
> ...
> {code}
> I'm not sure if the currently supported syntax is optional, but I think it's
> not.
> I followed this document:
> [http://standards.iso.org/ittf/PubliclyAvailableStandards/c069776_ISO_IEC_TR_19075-7_2017.zip]
> The {{TABLE}} clause acts in both ways: (1) to convert a table value to a
> table object (when used in the {{FROM}} clause to convert the function
> result) and (2) to convert a table object to a table value (when used to
> convert arguments to a function).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3970) Table-valued function TUMBLE uses non-standard syntax
[
https://issues.apache.org/jira/browse/CALCITE-3970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106470#comment-17106470
]
Rui Wang edited comment on CALCITE-3970 at 5/13/20, 5:10 PM:
-
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexCorrelVariable to refer to CURSORS. CURSORS, for example, can be converted
to Rel's input. This fits table function implementation: table function has
table parameters, which becomes CURSORS and later becomes TableFunctionScan's
inputs. And in runtime, based on a RexCorrelVariable, table function impl will
know which input in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
A small attempt to summarize the idea by using CURSOR in table functions and
TableFunctionScan:
{code:java}
SqlNode:
FROM:
TableFunction: [TABLE[name]]:
During conversion:
FROM:
CURSORS:[SELECT * FROM name]
TableFunction: [CURSOR$0]:
After conversion to Rels:
TableFunctionScan:
inputs: [source1[type: a BIGINT], source2[type: b VARCHAR]]
table_function: [RexCorrelVariable(source1, type: a bigint)]
{code}
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
was (Author: amaliujia):
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexCorrelVariable to refer to CURSORS. CURSORS, for example, can be converted
to Rel's input. This fits table function implementation: table function has
table parameters, which becomes CURSORS and later becomes TableFunctionScan's
inputs. And in runtime, based on a RexCorrelVariable, table function impl will
know which input in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
A small attempt to summarize the idea by using CURSOR in table functions and
TableFunctionScan:
{code:java}
SqlNode:
FROM:
TableFunction: [TABLE[name]]:
During conversion:
FROM:
CURSORS:[SELECT * FROM name]
TableFunction: [CURSOR$0]:
After conversion to Rels:
TableFunctionScan:
inputs: [source1[type: a BIGINT], source2[type: b VARCHAR]]
table_function: TUMBLE(RexCorrelVariable(source1, type: a bigint), ...)
{code}
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
> Table-valued function TUMBLE uses non-standard syntax
> -
>
> Key: CALCITE-3970
> URL: https://issues.apache.org/jira/browse/CALCITE-3970
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Viliam Durina
>Priority: Major
>
> The currently supported syntax is this:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE my_table, ...
> {code}
> But the SQL standard specifies that {{my_table}} must be in parentheses, such
> as here:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE(my_table), ...
> {code}
> The second syntax is currently rejected with:
> {code:none}
> Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
> Encountered "(" at line 1, column 33.
> Was expecting one of:
> ...
> ...
> ...
> ...
> ...
> {code}
> I'm not sure if the currently supported syntax is optional, but I think it's
> not.
> I followed this document:
> [http://st
[jira] [Comment Edited] (CALCITE-3970) Table-valued function TUMBLE uses non-standard syntax
[
https://issues.apache.org/jira/browse/CALCITE-3970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106470#comment-17106470
]
Rui Wang edited comment on CALCITE-3970 at 5/13/20, 5:09 PM:
-
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexCorrelVariable to refer to CURSORS. CURSORS, for example, can be converted
to Rel's input. This fits table function implementation: table function has
table parameters, which becomes CURSORS and later becomes TableFunctionScan's
inputs. And in runtime, based on a RexCorrelVariable, table function impl will
know which input in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
A small attempt to summarize the idea by using CURSOR in table functions and
TableFunctionScan:
{code:java}
SqlNode:
FROM:
TableFunction: [TABLE[name]]:
During conversion:
FROM:
CURSORS:[SELECT * FROM name]
TableFunction: [CURSOR$0]:
After conversion to Rels:
TableFunctionScan:
inputs: [source1[type: a BIGINT], source2[type: b VARCHAR]]
table_function: TUMBLE(RexCorrelVariable(source1, type: a bigint), ...)
{code}
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
was (Author: amaliujia):
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexCorrelVariable to refer to CURSORS. CURSORS, for example, can be converted
to Rel's input. This fits table function implementation: table function has
table parameters, which becomes CURSORS and later becomes TableFunctionScan's
inputs. And in runtime, based on a RexNode, table function impl will know which
input in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
A small attempt to summarize the idea by using CURSOR in table functions and
TableFunctionScan:
{code:java}
SqlNode:
FROM:
TableFunction: [TABLE[name]]:
During conversion:
FROM:
CURSORS:[SELECT * FROM name]
TableFunction: [CURSOR$0]:
After conversion to Rels:
TableFunctionScan:
inputs: [source1[type: a BIGINT], source2[type: b VARCHAR]]
table_function: TUMBLE(RexCorrelVariable(source1, type: a bigint), ...)
{code}
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
> Table-valued function TUMBLE uses non-standard syntax
> -
>
> Key: CALCITE-3970
> URL: https://issues.apache.org/jira/browse/CALCITE-3970
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Viliam Durina
>Priority: Major
>
> The currently supported syntax is this:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE my_table, ...
> {code}
> But the SQL standard specifies that {{my_table}} must be in parentheses, such
> as here:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE(my_table), ...
> {code}
> The second syntax is currently rejected with:
> {code:none}
> Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
> Encountered "(" at line 1, column 33.
> Was expecting one of:
> ...
> ...
> ...
> ...
> ...
> {code}
> I'm not sure if the currently supported syntax is optional, but I think it's
> not.
> I followed this document:
> [http://s
[jira] [Comment Edited] (CALCITE-3970) Table-valued function TUMBLE uses non-standard syntax
[
https://issues.apache.org/jira/browse/CALCITE-3970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106470#comment-17106470
]
Rui Wang edited comment on CALCITE-3970 at 5/13/20, 5:05 PM:
-
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexCorrelVariable to refer to CURSORS. CURSORS, for example, can be converted
to Rel's input. This fits table function implementation: table function has
table parameters, which becomes CURSORS and later becomes TableFunctionScan's
inputs. And in runtime, based on a RexNode, table function impl will know which
input in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
A small attempt to summarize the idea by using CURSOR in table functions and
TableFunctionScan:
{code:java}
SqlNode:
FROM:
TableFunction: [TABLE[name]]:
During conversion:
FROM:
CURSORS:[SELECT * FROM name]
TableFunction: [CURSOR$0]:
After conversion to Rels:
TableFunctionScan:
inputs: [source1[type: a BIGINT], source2[type: b VARCHAR]]
table_function: TUMBLE(RexCorrelVariable(source1, type: a bigint), ...)
{code}
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
was (Author: amaliujia):
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexCorrelVariable to refer to CURSORS. CURSORS, for example, can be converted
to Rel's input. This fits table function implementation: table function has
table parameters, which becomes CURSORS and later becomes TableFunctionScan's
inputs. And in runtime, based on a RexNode, table function impl will know which
input in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
A small attempt to summarize the idea by using CURSOR in table functions and
TableFunctionScan:
{code:java}
SqlNode:
FROM:
CURSORS:[]
TableFunction: [TABLE[name]]:
During conversion:
FROM:
CURSORS:[SELECT * FROM name]
TableFunction: [CURSOR$0]:
After conversion to Rels:
TableFunctionScan:
inputs: [source1[type: a BIGINT], source2[type: b VARCHAR]]
table_function: TUMBLE(RexCorrelVariable(source1, type: a bigint), ...)
{code}
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
> Table-valued function TUMBLE uses non-standard syntax
> -
>
> Key: CALCITE-3970
> URL: https://issues.apache.org/jira/browse/CALCITE-3970
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Viliam Durina
>Priority: Major
>
> The currently supported syntax is this:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE my_table, ...
> {code}
> But the SQL standard specifies that {{my_table}} must be in parentheses, such
> as here:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE(my_table), ...
> {code}
> The second syntax is currently rejected with:
> {code:none}
> Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
> Encountered "(" at line 1, column 33.
> Was expecting one of:
> ...
> ...
> ...
> ...
> ...
> {code}
> I'm not sure if the currently supported syntax is optional, but I think it's
> not.
> I followed this document:
>
[jira] [Comment Edited] (CALCITE-3970) Table-valued function TUMBLE uses non-standard syntax
[
https://issues.apache.org/jira/browse/CALCITE-3970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106470#comment-17106470
]
Rui Wang edited comment on CALCITE-3970 at 5/13/20, 5:04 PM:
-
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexCorrelVariable to refer to CURSORS. CURSORS, for example, can be converted
to Rel's input. This fits table function implementation: table function has
table parameters, which becomes CURSORS and later becomes TableFunctionScan's
inputs. And in runtime, based on a RexNode, table function impl will know which
input in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
A small attempt to summarize the idea by using CURSOR in table functions and
TableFunctionScan:
{code:java}
SqlNode:
FROM:
CURSORS:[]
TableFunction: [TABLE[name]]:
During conversion:
FROM:
CURSORS:[SELECT * FROM name]
TableFunction: [CURSOR$0]:
After conversion to Rels:
TableFunctionScan:
inputs: [source1[type: a BIGINT], source2[type: b VARCHAR]]
table_function: TUMBLE(RexCorrelVariable(source1, type: a bigint), ...)
{code}
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
was (Author: amaliujia):
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexCorrelVariable to refer to CURSORS. CURSORS, for example, can be converted
to Rel's input. This fits table function implementation: table function has
table parameters, which becomes CURSORS and later becomes TableFunctionScan's
inputs. And in runtime, based on a RexNode, table function impl will know which
input in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
A small attempt to summarize the idea by using CURSOR in table functions and
TableFunctionScan:
SqlNode:
FROM:
CURSORS:[]
TableFunction: [TABLE[name]]:
During conversion:
FROM:
CURSORS:[SELECT * FROM name]
TableFunction: [CURSOR$0]:
After conversion to Rels:
TableFunctionScan:
inputs: [source1[type: a bigint], source2[type: b string]]
table_function: TUMBLE(RexCorrelVariable(source1, type: a bigint), ...)
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
> Table-valued function TUMBLE uses non-standard syntax
> -
>
> Key: CALCITE-3970
> URL: https://issues.apache.org/jira/browse/CALCITE-3970
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Viliam Durina
>Priority: Major
>
> The currently supported syntax is this:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE my_table, ...
> {code}
> But the SQL standard specifies that {{my_table}} must be in parentheses, such
> as here:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE(my_table), ...
> {code}
> The second syntax is currently rejected with:
> {code:none}
> Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
> Encountered "(" at line 1, column 33.
> Was expecting one of:
> ...
> ...
> ...
> ...
> ...
> {code}
> I'm not sure if the currently supported syntax is optional, but I think it's
> not.
> I followed this document:
> [http
[jira] [Comment Edited] (CALCITE-3970) Table-valued function TUMBLE uses non-standard syntax
[
https://issues.apache.org/jira/browse/CALCITE-3970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106470#comment-17106470
]
Rui Wang edited comment on CALCITE-3970 at 5/13/20, 5:03 PM:
-
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexCorrelVariable to refer to CURSORS. CURSORS, for example, can be converted
to Rel's input. This fits table function implementation: table function has
table parameters, which becomes CURSORS and later becomes TableFunctionScan's
inputs. And in runtime, based on a RexNode, table function impl will know which
input in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
A small attempt to summarize the idea by using CURSOR in table functions and
TableFunctionScan:
SqlNode:
FROM:
CURSORS:[]
TableFunction: [TABLE[name]]:
During conversion:
FROM:
CURSORS:[SELECT * FROM name]
TableFunction: [CURSOR$0]:
After conversion to Rels:
TableFunctionScan:
inputs: [source1[type: a bigint], source2[type: b string]]
table_function: TUMBLE(RexCorrelVariable(source1, type: a bigint), ...)
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
was (Author: amaliujia):
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexNode to refer to CURSORS. CURSORS, for example, can be converted to Rel's
input. This fits table function implementation: table function has table
parameters, which becomes CURSORS and later becomes TableFunctionScan's inputs.
And in runtime, based on a RexNode, table function impl will know which input
in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
> Table-valued function TUMBLE uses non-standard syntax
> -
>
> Key: CALCITE-3970
> URL: https://issues.apache.org/jira/browse/CALCITE-3970
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Viliam Durina
>Priority: Major
>
> The currently supported syntax is this:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE my_table, ...
> {code}
> But the SQL standard specifies that {{my_table}} must be in parentheses, such
> as here:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE(my_table), ...
> {code}
> The second syntax is currently rejected with:
> {code:none}
> Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
> Encountered "(" at line 1, column 33.
> Was expecting one of:
> ...
> ...
> ...
> ...
> ...
> {code}
> I'm not sure if the currently supported syntax is optional, but I think it's
> not.
> I followed this document:
> [http://standards.iso.org/ittf/PubliclyAvailableStandards/c069776_ISO_IEC_TR_19075-7_2017.zip]
> The {{TABLE}} clause acts in both ways: (1) to convert a table value to a
> table object (when used in the {{FROM}} clause to convert the function
> result) and (2) to convert a table object to a table value (when used to
> convert arguments to a function).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3970) Table-valued function TUMBLE uses non-standard syntax
[
https://issues.apache.org/jira/browse/CALCITE-3970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106470#comment-17106470
]
Rui Wang commented on CALCITE-3970:
---
>Julian: So, how about allowing TABLE as an alternative to CURSOR, and
>deprecating CURSOR with an eye to eventually removing it?
I also didn't find the reference of CURSOR, and then what's the SQL semantics
of it becomes not clear to me. So deprecate it from the syntax makes sense to
me.
I tried to dig into CURSOR implementation and I find it makes sense. E.g.
Within a scope there is a list of CURSORS, and then in other places we can use
RexNode to refer to CURSORS. CURSORS, for example, can be converted to Rel's
input. This fits table function implementation: table function has table
parameters, which becomes CURSORS and later becomes TableFunctionScan's inputs.
And in runtime, based on a RexNode, table function impl will know which input
in TableFunctionScan to use.
So keep CURSOR idea in implementation seems useful.
> Danny: what kind of rex node should we translate the `col` column of table
> my_table
I think the suggestion here [1] is a working approach. See my description of
CURSOR implementation above.
However to adopt the implementation of [1], there will be some non-trivial
changes required.
[1]:
https://issues.apache.org/jira/browse/CALCITE-3955?focusedCommentId=17097014&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17097014
> Table-valued function TUMBLE uses non-standard syntax
> -
>
> Key: CALCITE-3970
> URL: https://issues.apache.org/jira/browse/CALCITE-3970
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Viliam Durina
>Priority: Major
>
> The currently supported syntax is this:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE my_table, ...
> {code}
> But the SQL standard specifies that {{my_table}} must be in parentheses, such
> as here:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE(my_table), ...
> {code}
> The second syntax is currently rejected with:
> {code:none}
> Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
> Encountered "(" at line 1, column 33.
> Was expecting one of:
> ...
> ...
> ...
> ...
> ...
> {code}
> I'm not sure if the currently supported syntax is optional, but I think it's
> not.
> I followed this document:
> [http://standards.iso.org/ittf/PubliclyAvailableStandards/c069776_ISO_IEC_TR_19075-7_2017.zip]
> The {{TABLE}} clause acts in both ways: (1) to convert a table value to a
> table object (when used in the {{FROM}} clause to convert the function
> result) and (2) to convert a table object to a table value (when used to
> convert arguments to a function).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3997) Problem with MERGE JOIN: java.lang.AssertionError: cannot merge join: left input is not sorted on left keys
[ https://issues.apache.org/jira/browse/CALCITE-3997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106432#comment-17106432 ] Haisheng Yuan commented on CALCITE-3997: No, maybe CALCITE-3576. The context is https://github.com/apache/calcite/pull/1247#discussion_r289489174. I can't reproduce the bug in Calcite. But can you try to remove rule FilterJoinRule.FILTER_ON_JOIN and see it error out or not? > Problem with MERGE JOIN: java.lang.AssertionError: cannot merge join: left > input is not sorted on left keys > --- > > Key: CALCITE-3997 > URL: https://issues.apache.org/jira/browse/CALCITE-3997 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.23.0 >Reporter: Enrico Olivelli >Priority: Blocker > > I have a couple of problems with HerdDB. > 1) JOIN order unsorted columns in presence of a WHERE over other columns > This is my case: > CREATE TABLE tblspace1.table1 (k1 string primary key,n1 int,s1 string) > CREATE TABLE tblspace1.table3 (k1 string primary key,n3 int,s3 string) > SELECT t1.k1 as first, t2.k1 as second > FROMtblspace1.table1 t1 > INNER JOIN tblspace1.table3 t2 ON t1.k1=t2.k1 > WHERE t1.n1 + 1 = t2.n3 > In this case for table1 and table3 no column is physically sorted (no column > with a collation) > I have this Planner error: > java.lang.AssertionError: cannot merge join: left input is not sorted on left > keys > at > org.apache.calcite.rel.metadata.RelMdCollation.mergeJoin(RelMdCollation.java:457) > at > org.apache.calcite.rel.metadata.RelMdCollation.collations(RelMdCollation.java:153) > at GeneratedMetadataHandler_Collation.collations_$(Unknown Source) > at GeneratedMetadataHandler_Collation.collations(Unknown Source) > at > org.apache.calcite.rel.metadata.RelMetadataQuery.collations(RelMetadataQuery.java:539) > at > org.apache.calcite.rel.metadata.RelMdCollation.project(RelMdCollation.java:273) > at > org.apache.calcite.rel.logical.LogicalProject.lambda$create$0(LogicalProject.java:122) > at org.apache.calcite.plan.RelTraitSet.replaceIfs(RelTraitSet.java:242) > at > org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:121) > at > org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:111) > at > org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:172) > at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:1464) > at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1258) > at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1230) > at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1219) > at > org.apache.calcite.plan.RelOptUtil.pushDownJoinConditions(RelOptUtil.java:3620) > at > org.apache.calcite.rel.rules.JoinPushExpressionsRule.onMatch(JoinPushExpressionsRule.java:59) > at > org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:221) > at > org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:519) > at herddb.sql.CalcitePlanner.runPlanner(CalcitePlanner.java:535) > at herddb.sql.CalcitePlanner.translate(CalcitePlanner.java:292) > If I remove the "WHERE" clause then no error is reported. > we have many other test cases about JOINs and this one is the only one that > fails > This is the failing test case on HerdDB > https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/core/SimpleJoinTest.java#L522 > We are using the default set of rules Programs.ofRules(Programs.RULE_SET) -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3576) Remove Enumerable convention check in FilterIntoJoinRule
[ https://issues.apache.org/jira/browse/CALCITE-3576?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Haisheng Yuan updated CALCITE-3576: --- Description: Context: https://github.com/apache/calcite/pull/1247#discussion_r289489174 > Remove Enumerable convention check in FilterIntoJoinRule > > > Key: CALCITE-3576 > URL: https://issues.apache.org/jira/browse/CALCITE-3576 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Haisheng Yuan >Priority: Major > Labels: pull-request-available > Fix For: 1.23.0 > > Time Spent: 2h 10m > Remaining Estimate: 0h > > Context: https://github.com/apache/calcite/pull/1247#discussion_r289489174 -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3977) RelDecorrelator does not resolve correlation variable with field accesses
[
https://issues.apache.org/jira/browse/CALCITE-3977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106399#comment-17106399
]
Ruben Q L commented on CALCITE-3977:
Looking inside RelDecorrelator code, it seems that this use case (a correlation
variable with several field accesses) is not supported.
The main classes inside RelDecorrelator to identify / reference a correlation
(CorRef and CorDef) are based on a:
{code}
public final CorrelationId corr;
public final int field;
{code}
For my examples, I am considering the Bookstoreschema:
{code}
public static class Author {
public final int aid;
public final String name;
public final Place birthPlace;
...
}
public static class Place {
public final Coordinate coords;
public final String city;
public final String country;
...
}
{code}
The RelDecorrelator approach works fine to decorrelate plans which use for
example {{$cor0.name}}, where we will have {{CorrelationId=$cor0}} and
{{field=1}}, i.e. {{$cor0.1}}.
However, in plans like the one described in this ticket, using
{{$cor0.birthPlace.city}}, this is not enough. It seems that the {{int field}}
would require to be a list (or array), so that we can have a CorRef / CorDef
with several "layers", i.e. {{$cor0.2.1}}. Right now these scenarios are not
correctly handled, hence the current bug.
Maybe [~julianhyde] (who seems the main contributor to the RelDecorrelator),
can confirm my assumption, and suggest an approach (maybe, as I proposed,
changing {{int field}} into {{int[] fields}} ?) in order to improve this module
to support this scenario.
> RelDecorrelator does not resolve correlation variable with field accesses
> -
>
> Key: CALCITE-3977
> URL: https://issues.apache.org/jira/browse/CALCITE-3977
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Thomas Rebele
>Priority: Major
> Attachments: Calcite3977.java
>
>
> The RelCorrelator seems to have problems with some plans that contain a field
> access (probably a RexFieldAccess, but I haven't looked further into it). In
> this ticket there's a filter on *$cor0.birthPlace.city*.
> Here the complete plan:
> {code:java}
> before decorrelate
> LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{}])
> LogicalTableScan(table=[[bookstore, authors]])
> LogicalFilter(condition=[=('Munich', $cor0.birthPlace.city)])
> LogicalTableScan(table=[[bookstore, authors]])
> after decorrelate
> LogicalJoin(condition=[=($2, $8)], joinType=[left])
> LogicalTableScan(table=[[bookstore, authors]])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalFilter(condition=[=('Munich', $cor0.birthPlace.city)])
> LogicalTableScan(table=[[bookstore, authors]])
> LogicalAggregate(group=[{0}])
> LogicalProject(birthPlace=[$2])
> LogicalTableScan(table=[[bookstore, authors]])
> {code}
> There seem to be two problems:
> * The LogicalCorrelate has been removed, but the $cor0.birthPlace.city is
> still in the filter condition.
> * The inner join does not seem to be necessary. If it is, could somebody
> explain, why?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3997) Problem with MERGE JOIN: java.lang.AssertionError: cannot merge join: left input is not sorted on left keys
[ https://issues.apache.org/jira/browse/CALCITE-3997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106372#comment-17106372 ] Enrico Olivelli commented on CALCITE-3997: -- can be issue related to CALCITE-3982 ? > Problem with MERGE JOIN: java.lang.AssertionError: cannot merge join: left > input is not sorted on left keys > --- > > Key: CALCITE-3997 > URL: https://issues.apache.org/jira/browse/CALCITE-3997 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.23.0 >Reporter: Enrico Olivelli >Priority: Blocker > > I have a couple of problems with HerdDB. > 1) JOIN order unsorted columns in presence of a WHERE over other columns > This is my case: > CREATE TABLE tblspace1.table1 (k1 string primary key,n1 int,s1 string) > CREATE TABLE tblspace1.table3 (k1 string primary key,n3 int,s3 string) > SELECT t1.k1 as first, t2.k1 as second > FROMtblspace1.table1 t1 > INNER JOIN tblspace1.table3 t2 ON t1.k1=t2.k1 > WHERE t1.n1 + 1 = t2.n3 > In this case for table1 and table3 no column is physically sorted (no column > with a collation) > I have this Planner error: > java.lang.AssertionError: cannot merge join: left input is not sorted on left > keys > at > org.apache.calcite.rel.metadata.RelMdCollation.mergeJoin(RelMdCollation.java:457) > at > org.apache.calcite.rel.metadata.RelMdCollation.collations(RelMdCollation.java:153) > at GeneratedMetadataHandler_Collation.collations_$(Unknown Source) > at GeneratedMetadataHandler_Collation.collations(Unknown Source) > at > org.apache.calcite.rel.metadata.RelMetadataQuery.collations(RelMetadataQuery.java:539) > at > org.apache.calcite.rel.metadata.RelMdCollation.project(RelMdCollation.java:273) > at > org.apache.calcite.rel.logical.LogicalProject.lambda$create$0(LogicalProject.java:122) > at org.apache.calcite.plan.RelTraitSet.replaceIfs(RelTraitSet.java:242) > at > org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:121) > at > org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:111) > at > org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:172) > at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:1464) > at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1258) > at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1230) > at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1219) > at > org.apache.calcite.plan.RelOptUtil.pushDownJoinConditions(RelOptUtil.java:3620) > at > org.apache.calcite.rel.rules.JoinPushExpressionsRule.onMatch(JoinPushExpressionsRule.java:59) > at > org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:221) > at > org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:519) > at herddb.sql.CalcitePlanner.runPlanner(CalcitePlanner.java:535) > at herddb.sql.CalcitePlanner.translate(CalcitePlanner.java:292) > If I remove the "WHERE" clause then no error is reported. > we have many other test cases about JOINs and this one is the only one that > fails > This is the failing test case on HerdDB > https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/core/SimpleJoinTest.java#L522 > We are using the default set of rules Programs.ofRules(Programs.RULE_SET) -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3998) Bad datatype for sum(n), it should be BIGINT but it is sometimes INTEGER
Enrico Olivelli created CALCITE-3998:
Summary: Bad datatype for sum(n), it should be BIGINT but it is
sometimes INTEGER
Key: CALCITE-3998
URL: https://issues.apache.org/jira/browse/CALCITE-3998
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.23.0
Reporter: Enrico Olivelli
I also noted that sometimes the type of sum(N) where N is an INTEGER column
sometimes it is now reported by Calcite as INTEGER and sometimes as a BIGINT.
In 1.22 every time is reported as BIGINT.
So we have another test failing.
SELECT sum(n1), count(*) as cc, k1
FROM tblspace1.tsql
GROUP by k1
ORDER BY sum(n1)
Here sum(n1) is reported now a INTEGER, previously it was a BIGINT. I would
prefer to see it as a BIGINT in order to prevent overflows
Here are the plans:
INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
k1 ORDER BY sum(n1) -- Logical Plan
LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost =
{10.52595367432 rows, 37.0 cpu, 0.0 io}, id = 1038
LogicalProject(EXPR$0=[$1], CC=[$2], K1=[$0]): rowcount = 2.0, cumulative
cost = {8.52595367432 rows, 13.0 cpu, 0.0 io}, id = 1037
LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)], CC=[COUNT()]): rowcount =
2.0, cumulative cost = {6.52595367432 rows, 7.0 cpu, 0.0 io}, id = 1035
LogicalProject(K1=[$0], n1=[$1]): rowcount = 2.0, cumulative cost = {4.0
rows, 7.0 cpu, 0.0 io}, id = 1034
LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1032
May 12, 2020 11:07:37 AM herddb.sql.CalcitePlanner runPlanner
INFO: Query: SELECT sum(n1), count(*) as cc, k1 FROM tblspace1.tsql GROUP by
k1 ORDER BY sum(n1) -- Best Plan
EnumerableSort(sort0=[$0], dir0=[ASC]): rowcount = 2.0, cumulative cost = {5.0
rows, 31.0 cpu, 0.0 io}, id = 1245
EnumerableProject(EXPR$0=[$1], CC=[1:BIGINT], K1=[$0]): rowcount = 2.0,
cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 1244
EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0
cpu, 0.0 io}, id = 1243
BindableTableScan(table=[[tblspace1, tsql]], projects=[[0, 1]]): rowcount
= 2.0, cumulative cost = {0.016 rows, 0.024 cpu, 0.0 io}, id = 1055
Within the same test case with the same tables the result of this query is not
changed
SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM tblspace1.tsql
INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
tblspace1.tsql -- Logical Plan
LogicalAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
rowcount = 1.0, cumulative cost = {5.387500047683716 rows, 5.0 cpu, 0.0 io}, id
= 1253
LogicalProject(n1=[$1]): rowcount = 2.0, cumulative cost = {4.0 rows, 5.0
cpu, 0.0 io}, id = 1252
LogicalTableScan(table=[[tblspace1, tsql]]): rowcount = 2.0, cumulative
cost = {2.0 rows, 3.0 cpu, 0.0 io}, id = 1250
May 12, 2020 11:08:48 AM herddb.sql.CalcitePlanner runPlanner
INFO: Query: SELECT sum(n1) as ss, min(n1) as mi, max(n1) as ma FROM
tblspace1.tsql -- Best Plan
EnumerableAggregate(group=[{}], SS=[SUM($0)], MI=[MIN($0)], MA=[MAX($0)]):
rowcount = 1.0, cumulative cost = {2.387500047683716 rows, 1.0 cpu, 0.0 io}, id
= 1295
EnumerableInterpreter: rowcount = 2.0, cumulative cost = {1.0 rows, 1.0 cpu,
0.0 io}, id = 1294
BindableTableScan(table=[[tblspace1, tsql]], projects=[[1]]): rowcount =
2.0, cumulative cost = {0.012 rows, 0.018002 cpu, 0.0 io}, id = 1265
This is the test on HerdDB
https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/sql/SimplerPlannerTest.java#L237
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3997) Problem with MERGE JOIN: java.lang.AssertionError: cannot merge join: left input is not sorted on left keys
Enrico Olivelli created CALCITE-3997: Summary: Problem with MERGE JOIN: java.lang.AssertionError: cannot merge join: left input is not sorted on left keys Key: CALCITE-3997 URL: https://issues.apache.org/jira/browse/CALCITE-3997 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.23.0 Reporter: Enrico Olivelli I have a couple of problems with HerdDB. 1) JOIN order unsorted columns in presence of a WHERE over other columns This is my case: CREATE TABLE tblspace1.table1 (k1 string primary key,n1 int,s1 string) CREATE TABLE tblspace1.table3 (k1 string primary key,n3 int,s3 string) SELECT t1.k1 as first, t2.k1 as second FROMtblspace1.table1 t1 INNER JOIN tblspace1.table3 t2 ON t1.k1=t2.k1 WHERE t1.n1 + 1 = t2.n3 In this case for table1 and table3 no column is physically sorted (no column with a collation) I have this Planner error: java.lang.AssertionError: cannot merge join: left input is not sorted on left keys at org.apache.calcite.rel.metadata.RelMdCollation.mergeJoin(RelMdCollation.java:457) at org.apache.calcite.rel.metadata.RelMdCollation.collations(RelMdCollation.java:153) at GeneratedMetadataHandler_Collation.collations_$(Unknown Source) at GeneratedMetadataHandler_Collation.collations(Unknown Source) at org.apache.calcite.rel.metadata.RelMetadataQuery.collations(RelMetadataQuery.java:539) at org.apache.calcite.rel.metadata.RelMdCollation.project(RelMdCollation.java:273) at org.apache.calcite.rel.logical.LogicalProject.lambda$create$0(LogicalProject.java:122) at org.apache.calcite.plan.RelTraitSet.replaceIfs(RelTraitSet.java:242) at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:121) at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:111) at org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:172) at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:1464) at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1258) at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1230) at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1219) at org.apache.calcite.plan.RelOptUtil.pushDownJoinConditions(RelOptUtil.java:3620) at org.apache.calcite.rel.rules.JoinPushExpressionsRule.onMatch(JoinPushExpressionsRule.java:59) at org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:221) at org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:519) at herddb.sql.CalcitePlanner.runPlanner(CalcitePlanner.java:535) at herddb.sql.CalcitePlanner.translate(CalcitePlanner.java:292) If I remove the "WHERE" clause then no error is reported. we have many other test cases about JOINs and this one is the only one that fails This is the failing test case on HerdDB https://github.com/diennea/herddb/blob/vote-calcite-123/herddb-core/src/test/java/herddb/core/SimpleJoinTest.java#L522 We are using the default set of rules Programs.ofRules(Programs.RULE_SET) -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3505) Infinite matching of FilterProjectTransposeRule causes stackoverflow
[
https://issues.apache.org/jira/browse/CALCITE-3505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106227#comment-17106227
]
Jiatao Tao commented on CALCITE-3505:
-
"I strongly think we should resolve the infinite rule matching caused by cycle
from engine level. Because when a new Calcite user create a RelOptRule, if
infinite matching happens,"
+1
> Infinite matching of FilterProjectTransposeRule causes stackoverflow
>
>
> Key: CALCITE-3505
> URL: https://issues.apache.org/jira/browse/CALCITE-3505
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Jin Xing
>Priority: Major
> Attachments: graphviz.svg
>
>
> Run ScannableTableTest#testProjectableFilterableTableJoin with minor change
> to reproduce
> {code:java}
> @Test public void testProjectableFilterableTableJoin() throws Exception {
> final StringBuilder buf = new StringBuilder();
> final String explain = "PLAN="
> + "EnumerableHashJoin(condition=[=($0, $3)], joinType=[inner])\n"
> + " EnumerableInterpreter\n"
> + "BindableTableScan(table=[[s, b1]], filters=[[=($0, 10)]])\n"
> + " EnumerableInterpreter\n"
> + "BindableTableScan(table=[[s, b2]], filters=[[=($0, 10)]])";
> CalciteAssert.that()
> .with(
> newSchema("s",
> Pair.of("b1", new BeatlesProjectableFilterableTable(buf,
> true)),
> Pair.of("b2", new BeatlesProjectableFilterableTable(buf,
> true
> .query("select * from \"s\".\"b1\", \"s\".\"b2\" "
> + "where \"s\".\"b1\".\"i\" = 10 and \"s\".\"b2\".\"i\" =
> 10 "
> + "and \"s\".\"b1\".\"i\" = \"s\".\"b2\".\"i\"")
> .withHook(Hook.PLANNER, (Consumer) planner -> {
> planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
> })
> .explainContains(explain);
> }
> {code}
> This test has nothing to do with ENUMERABLE_MERGE_JOIN_RULE, but if we
> disable it with planner hook, stackoverflow happens;
> I debugged and found that FilterProjectTransposeRule is matched infinitely
> but not sure the root cause.
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3994) RexSimplify reduce concat calls with constant arguments
[ https://issues.apache.org/jira/browse/CALCITE-3994?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106175#comment-17106175 ] Stamatis Zampetakis commented on CALCITE-3994: -- Can you explain a bit more what's the problem and what you would like RexSimplify to do with a concrete example? > RexSimplify reduce concat calls with constant arguments > --- > > Key: CALCITE-3994 > URL: https://issues.apache.org/jira/browse/CALCITE-3994 > Project: Calcite > Issue Type: Improvement >Affects Versions: 1.21.0 >Reporter: Jess Balint >Priority: Minor > > would be good to implement support for string concat in RexSimplify -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3970) Table-valued function TUMBLE uses non-standard syntax
[
https://issues.apache.org/jira/browse/CALCITE-3970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106167#comment-17106167
]
Danny Chen commented on CALCITE-3970:
-
[~julianhyde] Another question is for query
{code:sql}
SELECT * FROM TABLE(TUMBLE(TABLE(my_table), DESCRIPTOR(col1)))
{code}
what kind of rex node should we translate the `col` column of table my_table,
the current implementation translates it to a RexAccess, such as "$cor0.0", but
apparently it is wrong, there is no correlation in the plan.
> Table-valued function TUMBLE uses non-standard syntax
> -
>
> Key: CALCITE-3970
> URL: https://issues.apache.org/jira/browse/CALCITE-3970
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Viliam Durina
>Priority: Major
>
> The currently supported syntax is this:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE my_table, ...
> {code}
> But the SQL standard specifies that {{my_table}} must be in parentheses, such
> as here:
> {code:java}
> SELECT * FROM TABLE(TUMBLE(TABLE(my_table), ...
> {code}
> The second syntax is currently rejected with:
> {code:none}
> Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException:
> Encountered "(" at line 1, column 33.
> Was expecting one of:
> ...
> ...
> ...
> ...
> ...
> {code}
> I'm not sure if the currently supported syntax is optional, but I think it's
> not.
> I followed this document:
> [http://standards.iso.org/ittf/PubliclyAvailableStandards/c069776_ISO_IEC_TR_19075-7_2017.zip]
> The {{TABLE}} clause acts in both ways: (1) to convert a table value to a
> table object (when used in the {{FROM}} clause to convert the function
> result) and (2) to convert a table object to a table value (when used to
> convert arguments to a function).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
