[jira] [Commented] (CALCITE-3395) add BuiltinMethod for Substring(String, int)
[ https://issues.apache.org/jira/browse/CALCITE-3395?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17007285#comment-17007285 ] Feng Zhu commented on CALCITE-3395: --- Hi, [~yuanyoujun]. In CALCITE-3563, we have added mechnism in code generation to intelligently figure out the proper method through overloaded ones. I think it can solve this problem. Could you verify it? > add BuiltinMethod for Substring(String, int) > > > Key: CALCITE-3395 > URL: https://issues.apache.org/jira/browse/CALCITE-3395 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Youjun Yuan >Priority: Minor > Original Estimate: 4h > Remaining Estimate: 4h > > substring function has two versoins: > 1, Substring(String, int, int) > 2, Substring(String, int) > currently in BuiltinMethod.java, only the first one is defined. Need to > define the second one as well, so that we can use both of them. > Apache Flink(FunctionGenerator.scala), reference the BuiltinMethod, since > Calcite only defines 1, not 2, Flink always resolve SUBSTRING to > Substring(String, int, int) even if there is only 2 parameters. This problem > happens to be covered by method overloading of java, but it's still a > potential problem. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-3651) NullPointerException when convert relational algebra that correlates TableFunctionScan
[
https://issues.apache.org/jira/browse/CALCITE-3651?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Haisheng Yuan resolved CALCITE-3651.
Fix Version/s: 1.22.0
Resolution: Fixed
Fixed in
https://github.com/apache/calcite/commit/3dd6d850adc6a0d7b14bedac0e431047c63beb3c,
thanks for the PR, [~yanlin-Lynn]!
> NullPointerException when convert relational algebra that correlates
> TableFunctionScan
> ---
>
> Key: CALCITE-3651
> URL: https://issues.apache.org/jira/browse/CALCITE-3651
> Project: Calcite
> Issue Type: Bug
>Reporter: Wang Yanlin
>Assignee: Wang Yanlin
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Converting RelNode that correlate TableFunctionScan will cause NPE.
> {noformat}
> LogicalProject(product_class_id=[$0], product_id=[$1], brand_name=[$2],
> product_name=[$3], SKU=[$4], SRP=[$5], gross_weight=[$6], net_weight=[$7],
> recyclable_package=[$8], low_fat=[$9], units_per_case=[$10],
> cases_per_pallet=[$11], shelf_width=[$12], shelf_height=[$13],
> shelf_depth=[$14], I=[$15])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{1}])
> JdbcTableScan(table=[[foodmart, product]])
> LogicalTableFunctionScan(invocation=[RAMP($cor0.product_id)],
> rowType=[RecordType(INTEGER I)])
> {noformat}
> Add this test case to reproduce
> {noformat}
> // RelToSqlConverterTest
> @Test public void testLateralCorrelate() {
> final String query = "select * from \"product\", lateral
> table(RAMP(\"product\".\"product_id\"))";
> sql(query).ok("");
> }
> {noformat}
> got
> {noformat}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:527)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:126)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:132)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.toSql(RelToSqlConverterTest.java:190)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.access$200(RelToSqlConverterTest.java:94)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.exec(RelToSqlConverterTest.java:4541)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:4516)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testLateralCorrelate(RelToSqlConverterTest.java:3741)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:675)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:125)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:139)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:131)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:81)
> at
> org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
> at
> org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:104)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:62)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:43)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:35)
> at
> org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
> at
> org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
> at
>
[jira] [Updated] (CALCITE-3668) VolcanoPlanner doesn't match all the RelSubSet in matchRecursive
[
https://issues.apache.org/jira/browse/CALCITE-3668?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-3668:
Labels: pull-request-available (was: )
> VolcanoPlanner doesn't match all the RelSubSet in matchRecursive
>
>
> Key: CALCITE-3668
> URL: https://issues.apache.org/jira/browse/CALCITE-3668
> Project: Calcite
> Issue Type: Bug
>Reporter: Haisheng Yuan
>Priority: Major
> Labels: pull-request-available
>
> If I have a rule to match pattern with Filter-RelSubset, VolcanoPlanner only
> matches 1 RelSubset in the RelSet, instead of all the subsets.
> {code:java}
> if (operand.getMatchedClass() == RelSubset.class) {
> // If the rule wants the whole subset, we just provide it
> successors = ImmutableList.of(subset);
> } else {
> successors = subset.getRelList();
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3665) Better estimate join row count when one of the sides is known to be unique
[ https://issues.apache.org/jira/browse/CALCITE-3665?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17007152#comment-17007152 ] Rui Wang commented on CALCITE-3665: --- Ah I think I got what you meant: if one side of join is unique, then estimate rows will be rowCount(another join side). > Better estimate join row count when one of the sides is known to be unique > -- > > Key: CALCITE-3665 > URL: https://issues.apache.org/jira/browse/CALCITE-3665 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.21.0 >Reporter: Vladimir Sitnikov >Priority: Major > > For instance: > 1) select from emp e left join dept d on (e.X = d.id) > This query can't multiply rows, so its row count estimation is > rowCount(join.left) > 2) select from dept d right join emp e on (d.id = e.X) > This query can't multiply rows, so its row count estimation is > rowCount(join.right) > 3) select from emp e join dept d on (e.id = d.id) > The rows can't be multiplied here as well > Currently, Calcite estimates the number of rows as left*right which is an > overestimation in many cases :( -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3665) Better estimate join row count when one of the sides is known to be unique
[ https://issues.apache.org/jira/browse/CALCITE-3665?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17007151#comment-17007151 ] Rui Wang commented on CALCITE-3665: --- Hi [~vladimirsitnikov], could you elaborate a bit on the uniqueness of join sides? Are you saying, for example, in "select from emp e left join dept d on (e.X = d.id)", both e.X and d.id are unique, which is also known before query planning? > Better estimate join row count when one of the sides is known to be unique > -- > > Key: CALCITE-3665 > URL: https://issues.apache.org/jira/browse/CALCITE-3665 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.21.0 >Reporter: Vladimir Sitnikov >Priority: Major > > For instance: > 1) select from emp e left join dept d on (e.X = d.id) > This query can't multiply rows, so its row count estimation is > rowCount(join.left) > 2) select from dept d right join emp e on (d.id = e.X) > This query can't multiply rows, so its row count estimation is > rowCount(join.right) > 3) select from emp e join dept d on (e.id = d.id) > The rows can't be multiplied here as well > Currently, Calcite estimates the number of rows as left*right which is an > overestimation in many cases :( -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3531) AggregateProjectPullUpConstantsRule should not remove deterministic function group key if the function is dynamic
[
https://issues.apache.org/jira/browse/CALCITE-3531?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17007132#comment-17007132
]
Rui Wang edited comment on CALCITE-3531 at 1/2/20 11:48 PM:
I found where current_timestamp is defined: SQL standard 2011 6.32 .
Quote from the general rules 1) under the section:
{code:java}
1) The s CURRENT_DATE, CURRENT_TIME, and
CURRENT_TIMESTAMP
respectively return the current date, current time, and current timestamp; the
time and timestamp values
are returned with time zone displacement equal to the current default time zone
displacement of the SQL session.
{code}
The last sentence might have stated what we have been searching for: probably
it says these functions work within a SQL session.
In addition, I checked postgresql and hive quickly. In both engines
current_timestamp is supposed to return the same value within the same query.
was (Author: amaliujia):
I found where current_timestamp is defined: SQL standard 2011 6.32 .
Quote from the general rules 1) under the section:
{code:java}
1) The s CURRENT_DATE, CURRENT_TIME, and
CURRENT_TIMESTAMP
respectively return the current date, current time, and current timestamp; the
time and timestamp values
are returned with time zone displacement equal to the current default time zone
displacement of the SQL session.
{code}
The last sentence might have stated what we have been searching for: probably
it says these functions work within a SQL session.
> AggregateProjectPullUpConstantsRule should not remove deterministic function
> group key if the function is dynamic
> -
>
> Key: CALCITE-3531
> URL: https://issues.apache.org/jira/browse/CALCITE-3531
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.21.0
>Reporter: Danny Chen
>Assignee: Danny Chen
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> Now AggregateProjectPullUpConstantsRule simplify the query:
> {code:sql}
> select hiredate
> from sales.emp
> where sal is null and hiredate = current_timestamp
> group by sal, hiredate
> having count(*) > 3
> {code}
> from plan:
> {code:xml}
> LogicalProject(HIREDATE=[$1])
> LogicalFilter(condition=[>($2, 3)])
> LogicalAggregate(group=[{0, 1}], agg#0=[COUNT()])
> LogicalProject(SAL=[$5], HIREDATE=[$4])
> LogicalFilter(condition=[AND(IS NULL($5), =($4, CURRENT_TIMESTAMP))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> to plan:
> {code:xml}
> LogicalProject(HIREDATE=[$1])
> LogicalFilter(condition=[>($2, 3)])
> LogicalProject(SAL=[$0], HIREDATE=[CURRENT_TIMESTAMP], $f2=[$1])
> LogicalAggregate(group=[{0}], agg#0=[COUNT()])
> LogicalProject(SAL=[$5], HIREDATE=[$4])
> LogicalFilter(condition=[AND(IS NULL($5), =($4,
> CURRENT_TIMESTAMP))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> which is unsafe, because for stream sql, we need to group data by dateTime,
> also the result is wrong if a batch job runs across days.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3531) AggregateProjectPullUpConstantsRule should not remove deterministic function group key if the function is dynamic
[
https://issues.apache.org/jira/browse/CALCITE-3531?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17007132#comment-17007132
]
Rui Wang commented on CALCITE-3531:
---
I found where current_timestamp is defined: SQL standard 2011 6.32 .
Quote from the general rules 1) under the section:
{code:java}
1) The s CURRENT_DATE, CURRENT_TIME, and
CURRENT_TIMESTAMP
respectively return the current date, current time, and current timestamp; the
time and timestamp values
are returned with time zone displacement equal to the current default time zone
displacement of the SQL session.
{code}
The last sentence might have stated what we have been searching for: probably
it says these functions work within a SQL session.
> AggregateProjectPullUpConstantsRule should not remove deterministic function
> group key if the function is dynamic
> -
>
> Key: CALCITE-3531
> URL: https://issues.apache.org/jira/browse/CALCITE-3531
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.21.0
>Reporter: Danny Chen
>Assignee: Danny Chen
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> Now AggregateProjectPullUpConstantsRule simplify the query:
> {code:sql}
> select hiredate
> from sales.emp
> where sal is null and hiredate = current_timestamp
> group by sal, hiredate
> having count(*) > 3
> {code}
> from plan:
> {code:xml}
> LogicalProject(HIREDATE=[$1])
> LogicalFilter(condition=[>($2, 3)])
> LogicalAggregate(group=[{0, 1}], agg#0=[COUNT()])
> LogicalProject(SAL=[$5], HIREDATE=[$4])
> LogicalFilter(condition=[AND(IS NULL($5), =($4, CURRENT_TIMESTAMP))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> to plan:
> {code:xml}
> LogicalProject(HIREDATE=[$1])
> LogicalFilter(condition=[>($2, 3)])
> LogicalProject(SAL=[$0], HIREDATE=[CURRENT_TIMESTAMP], $f2=[$1])
> LogicalAggregate(group=[{0}], agg#0=[COUNT()])
> LogicalProject(SAL=[$5], HIREDATE=[$4])
> LogicalFilter(condition=[AND(IS NULL($5), =($4,
> CURRENT_TIMESTAMP))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> which is unsafe, because for stream sql, we need to group data by dateTime,
> also the result is wrong if a batch job runs across days.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3668) VolcanoPlanner doesn't match all the RelSubSet in matchRecursive
Haisheng Yuan created CALCITE-3668:
--
Summary: VolcanoPlanner doesn't match all the RelSubSet in
matchRecursive
Key: CALCITE-3668
URL: https://issues.apache.org/jira/browse/CALCITE-3668
Project: Calcite
Issue Type: Bug
Reporter: Haisheng Yuan
If I have a rule to match pattern with Filter-RelSubset, VolcanoPlanner only
matches 1 RelSubset in the RelSet, instead of all the subsets.
{code:java}
if (operand.getMatchedClass() == RelSubset.class) {
// If the rule wants the whole subset, we just provide it
successors = ImmutableList.of(subset);
} else {
successors = subset.getRelList();
}
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3664) Sort in subquery is lost when converting SqlNode to Relnode
[
https://issues.apache.org/jira/browse/CALCITE-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17007089#comment-17007089
]
Haisheng Yuan commented on CALCITE-3664:
It is expected. See https://issues.apache.org/jira/browse/CALCITE-2798
> Sort in subquery is lost when converting SqlNode to Relnode
> ---
>
> Key: CALCITE-3664
> URL: https://issues.apache.org/jira/browse/CALCITE-3664
> Project: Calcite
> Issue Type: Bug
>Reporter: Wang Yanlin
>Assignee: Wang Yanlin
>Priority: Major
>
> Lost sort in subquery when converting a SqlNode to Relnode, making its
> semantics changed.
> The test case to reproduce
> {code:java}
> // SqlToRelConverterTest
> @Test public void testSortInSubquery() {
> final String sql = "select ename from (select ename from emp order by
> ename) a";
> sql(sql).ok();
> }
> {code}
> This test case will success with this plan.
> {code:java}
>
>
>
>
>
>
>
>
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Closed] (CALCITE-500) Ensure EnumerableJoin hashes the smallest input
[
https://issues.apache.org/jira/browse/CALCITE-500?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vladimir Sitnikov closed CALCITE-500.
-
Resolution: Fixed
Closing the issue as executor has to be consistent with optimizer.
Optimizer assumes the left collation is intact, so we can't just use another
input for building the hash table.
> Ensure EnumerableJoin hashes the smallest input
> ---
>
> Key: CALCITE-500
> URL: https://issues.apache.org/jira/browse/CALCITE-500
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.0.0-incubating
>Reporter: Vladimir Sitnikov
>Assignee: Atri Sharma
>Priority: Major
> Labels: newbie, pull-request-available
> Attachments: lookup.png
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> {{EnumerableJoin}} tries to put the smallest input the first, however when it
> comes to execution, Calcite creates lookup for _second_ input of join.
> It would be nice to ensure the lookup is created on the smallest input.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3657) EnumerableHashJoin should not use NLogN for costing
[ https://issues.apache.org/jira/browse/CALCITE-3657?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17007059#comment-17007059 ] Vladimir Sitnikov commented on CALCITE-3657: I suggest we treat non-equi-join-condition costing separately. It would touch all types of joins (e.g. nested loop, hash, merge), and leaving it unaccounted for a while is ok. > EnumerableHashJoin should not use NLogN for costing > --- > > Key: CALCITE-3657 > URL: https://issues.apache.org/jira/browse/CALCITE-3657 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.21.0 >Reporter: Vladimir Sitnikov >Assignee: Vladimir Sitnikov >Priority: Major > -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Assigned] (CALCITE-3657) EnumerableHashJoin should not use NLogN for costing
[ https://issues.apache.org/jira/browse/CALCITE-3657?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Vladimir Sitnikov reassigned CALCITE-3657: -- Assignee: Vladimir Sitnikov > EnumerableHashJoin should not use NLogN for costing > --- > > Key: CALCITE-3657 > URL: https://issues.apache.org/jira/browse/CALCITE-3657 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.21.0 >Reporter: Vladimir Sitnikov >Assignee: Vladimir Sitnikov >Priority: Major > -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-3666) Refine RelMdUniqueKeys and RelMdColumnUniqueness for Calc
[ https://issues.apache.org/jira/browse/CALCITE-3666?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Vladimir Sitnikov resolved CALCITE-3666. Fix Version/s: 1.22.0 Resolution: Fixed Fixed in https://github.com/apache/calcite/commit/7dcceeedbc9bb28167f3b7497a8eee1429bc51f6 > Refine RelMdUniqueKeys and RelMdColumnUniqueness for Calc > - > > Key: CALCITE-3666 > URL: https://issues.apache.org/jira/browse/CALCITE-3666 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.21.0 >Reporter: Vladimir Sitnikov >Assignee: Vladimir Sitnikov >Priority: Major > Labels: pull-request-available > Fix For: 1.22.0 > > Time Spent: 20m > Remaining Estimate: 0h > > RelMdColumnUniqueness does not have an override for Calc, thus it can't > resolve uniqueness for EnumerableCalc > In practice, it is hard to deduce uniqueness for select emp.id*emp.deptno > from emp, however, simple projects can be resolved (e.g. when Calc selects > one of its input as is). -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3663) Support for TRIM function in Bigquery dialect
[
https://issues.apache.org/jira/browse/CALCITE-3663?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17006989#comment-17006989
]
Rui Wang commented on CALCITE-3663:
---
Thanks [~dhirenda.gautam]
I will help take a look at your PR soon.
> Support for TRIM function in Bigquery dialect
> -
>
> Key: CALCITE-3663
> URL: https://issues.apache.org/jira/browse/CALCITE-3663
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Dhirenda Gautam
>Priority: Major
>
> In current Calcite implementation for query : SELECT TRIM('ABC') for
> big-query Dialect it translated into SELECT TRIM(BOTH ' ' FROM 'ABC') .
> But the appropriate query for BigQuery is :: SELECT TRIM('ABC')
> Similarly below query
> SELECT TRIM(BOTH 'a' from 'ABC')
> SELECT TRIM(LEADING ' ' from 'ABC')
> SELECT TRIM(TRAILING ' ' from 'ABC') are translated into invalid BigQuery
> query.
> Unparse logic for the trim has been handle in BigQuery dialect to convert the
> source Trim query into valid bigQuery query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3666) Refine RelMdUniqueKeys and RelMdColumnUniqueness for Calc
[ https://issues.apache.org/jira/browse/CALCITE-3666?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Vladimir Sitnikov updated CALCITE-3666: --- Summary: Refine RelMdUniqueKeys and RelMdColumnUniqueness for Calc (was: Refine RelMdColumnUniqueness for Calc) > Refine RelMdUniqueKeys and RelMdColumnUniqueness for Calc > - > > Key: CALCITE-3666 > URL: https://issues.apache.org/jira/browse/CALCITE-3666 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.21.0 >Reporter: Vladimir Sitnikov >Assignee: Vladimir Sitnikov >Priority: Major > > RelMdColumnUniqueness does not have an override for Calc, thus it can't > resolve uniqueness for EnumerableCalc > In practice, it is hard to deduce uniqueness for select emp.id*emp.deptno > from emp, however, simple projects can be resolved (e.g. when Calc selects > one of its input as is). -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3285) EnumerableMergeJoin should support non-equi join conditions
[ https://issues.apache.org/jira/browse/CALCITE-3285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17006842#comment-17006842 ] Jin Xing commented on CALCITE-3285: --- Hi [~vladimirsitnikov] Yes, you are right, it's a bug. I filed a JIRA -- CALCITE-3667 and proposed a fix [1]. It'd be great if you can take a look when you have time ~ Best, Jin https://github.com/apache/calcite/pull/1717 > EnumerableMergeJoin should support non-equi join conditions > --- > > Key: CALCITE-3285 > URL: https://issues.apache.org/jira/browse/CALCITE-3285 > Project: Calcite > Issue Type: Improvement >Reporter: Haisheng Yuan >Assignee: Jin Xing >Priority: Major > Labels: pull-request-available > Fix For: 1.22.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Calcite should be able to generate EnumerableMergeJoin with non-equi join > conditions, as long as there are equi-join conditions. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3667) EnumerableMergeJoin should not use take-while enumerator
[ https://issues.apache.org/jira/browse/CALCITE-3667?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-3667: Labels: pull-request-available (was: ) > EnumerableMergeJoin should not use take-while enumerator > - > > Key: CALCITE-3667 > URL: https://issues.apache.org/jira/browse/CALCITE-3667 > Project: Calcite > Issue Type: Improvement >Reporter: Jin Xing >Assignee: Jin Xing >Priority: Major > Labels: pull-request-available > > Currently EnumerableMergeJoin use take-while enumerator [1] to emit values > satisfy predicate. However take-while enumerator stops the enumeration at > once when predicate is failed, which is not correct and we should finish the > enumeration and extract all the qualified values. > [1] > https://github.com/apache/calcite/blob/master/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java#L3896 -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3667) EnumerableMergeJoin should not use take-while enumerator
[ https://issues.apache.org/jira/browse/CALCITE-3667?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jin Xing updated CALCITE-3667: -- Description: Currently EnumerableMergeJoin use take-while enumerator [1] to emit values satisfy predicate. However take-while enumerator stops the enumeration at once when predicate is failed, which is not correct and we should finish the enumeration and extract all the qualified values. [1] https://github.com/apache/calcite/blob/master/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java#L3896 > EnumerableMergeJoin should not use take-while enumerator > - > > Key: CALCITE-3667 > URL: https://issues.apache.org/jira/browse/CALCITE-3667 > Project: Calcite > Issue Type: Improvement >Reporter: Jin Xing >Assignee: Jin Xing >Priority: Major > > Currently EnumerableMergeJoin use take-while enumerator [1] to emit values > satisfy predicate. However take-while enumerator stops the enumeration at > once when predicate is failed, which is not correct and we should finish the > enumeration and extract all the qualified values. > [1] > https://github.com/apache/calcite/blob/master/linq4j/src/main/java/org/apache/calcite/linq4j/EnumerableDefaults.java#L3896 -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3667) EnumerableMergeJoin should not use take-while enumerator
Jin Xing created CALCITE-3667: - Summary: EnumerableMergeJoin should not use take-while enumerator Key: CALCITE-3667 URL: https://issues.apache.org/jira/browse/CALCITE-3667 Project: Calcite Issue Type: Improvement Reporter: Jin Xing Assignee: Jin Xing -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3285) EnumerableMergeJoin should support non-equi join conditions
[ https://issues.apache.org/jira/browse/CALCITE-3285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17006820#comment-17006820 ] Jin Xing commented on CALCITE-3285: --- Thanks for ping me [~vladimirsitnikov] I will verify soon ~ > EnumerableMergeJoin should support non-equi join conditions > --- > > Key: CALCITE-3285 > URL: https://issues.apache.org/jira/browse/CALCITE-3285 > Project: Calcite > Issue Type: Improvement >Reporter: Haisheng Yuan >Assignee: Jin Xing >Priority: Major > Labels: pull-request-available > Fix For: 1.22.0 > > Time Spent: 2h 50m > Remaining Estimate: 0h > > Calcite should be able to generate EnumerableMergeJoin with non-equi join > conditions, as long as there are equi-join conditions. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3664) Sort in subquery is lost when converting SqlNode to Relnode
[
https://issues.apache.org/jira/browse/CALCITE-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17006813#comment-17006813
]
Jin Xing commented on CALCITE-3664:
---
Is it because collation of emp already satisfied the *order by ename*, thus no
need to create a Sort operator
> Sort in subquery is lost when converting SqlNode to Relnode
> ---
>
> Key: CALCITE-3664
> URL: https://issues.apache.org/jira/browse/CALCITE-3664
> Project: Calcite
> Issue Type: Bug
>Reporter: Wang Yanlin
>Assignee: Wang Yanlin
>Priority: Major
>
> Lost sort in subquery when converting a SqlNode to Relnode, making its
> semantics changed.
> The test case to reproduce
> {code:java}
> // SqlToRelConverterTest
> @Test public void testSortInSubquery() {
> final String sql = "select ename from (select ename from emp order by
> ename) a";
> sql(sql).ok();
> }
> {code}
> This test case will success with this plan.
> {code:java}
>
>
>
>
>
>
>
>
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3657) EnumerableHashJoin should not use NLogN for costing
[ https://issues.apache.org/jira/browse/CALCITE-3657?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17006806#comment-17006806 ] Ruben Q L commented on CALCITE-3657: Since this ticket is modifying the computeSelfCost of EnumerableHashJoin, I think we could also introduce another factor to make the cost computation more accurate: the (hash)join condition. With the implementation of CALCITE-2973, now HashJoin supports all types of join condition, so we can have three different scenarios: a) The condition is a "complete equi-join condition"; this is the best case scenario, the join is performed purely on a hash-based algorithm and no extra predicate is required. b) The condition is a "partial equi-join conditiom", i.e. the condition contains some equi-join items, but also some non-equi-join items; in this case the join is performed on a hash-based algorithm (for the equi-join items) + an extra predicate (for the non-equi-join ones). c) The join condition is a "complete non-equi-join-condition", i.e. there are no equi-join elements to build a hash-based solution, so the algorithm is performed based on a predicate which evaluates the whole condition. This is the worst-case scenario, since the HashJoin actually behaves as a kind of de-facto nested loop join. Currently, since the condition nature is not evaluated in the computeSelfCost, cases a-b-c would have an equivalent cost; I think we should reflect somehow that: cost a < cost b < cost c > EnumerableHashJoin should not use NLogN for costing > --- > > Key: CALCITE-3657 > URL: https://issues.apache.org/jira/browse/CALCITE-3657 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.21.0 >Reporter: Vladimir Sitnikov >Priority: Major > -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3666) Refine RelMdColumnUniqueness for Calc
Vladimir Sitnikov created CALCITE-3666: -- Summary: Refine RelMdColumnUniqueness for Calc Key: CALCITE-3666 URL: https://issues.apache.org/jira/browse/CALCITE-3666 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.21.0 Reporter: Vladimir Sitnikov Assignee: Vladimir Sitnikov RelMdColumnUniqueness does not have an override for Calc, thus it can't resolve uniqueness for EnumerableCalc In practice, it is hard to deduce uniqueness for select emp.id*emp.deptno from emp, however, simple projects can be resolved (e.g. when Calc selects one of its input as is). -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3665) Better estimate join row count when one of the sides is known to be unique
Vladimir Sitnikov created CALCITE-3665: -- Summary: Better estimate join row count when one of the sides is known to be unique Key: CALCITE-3665 URL: https://issues.apache.org/jira/browse/CALCITE-3665 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.21.0 Reporter: Vladimir Sitnikov For instance: 1) select from emp e left join dept d on (e.X = d.id) This query can't multiply rows, so its row count estimation is rowCount(join.left) 2) select from dept d right join emp e on (d.id = e.X) This query can't multiply rows, so its row count estimation is rowCount(join.right) 3) select from emp e join dept d on (e.id = d.id) The rows can't be multiplied here as well Currently, Calcite estimates the number of rows as left*right which is an overestimation in many cases :( -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3593) RelToSqlConverter changes target of ambiguous HAVING clause with a Project on Filter on Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-3593?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-3593:
Labels: pull-request-available (was: )
> RelToSqlConverter changes target of ambiguous HAVING clause with a Project on
> Filter on Aggregate
> -
>
> Key: CALCITE-3593
> URL: https://issues.apache.org/jira/browse/CALCITE-3593
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Assignee: Jin Xing
>Priority: Major
> Labels: pull-request-available
>
> Best shown with the shell of a test in RelToSqlConverter test.
> The following SQL on BigQuery
> {code:java}
> select product_id - 1000 as product_id
> from (
> select product_id, avg(gross_weight) as agw
> from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as
> product
> where net_weight < 100
> group by product_id having product_id > 0){code}
> produces one result, because the having filter applies to the product id
> before subtraction, of course.
> Running it through the machinery in that test
> (`sql(query).withBigQuery().ok(expected)`) translates it to:
> {noformat}
> SELECT product_id - 1000 AS product_id
> from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as
> product
> WHERE net_weight < 100
> GROUP BY product_id
> HAVING product_id > 0{noformat}
> This changes the meaning of the query: now the HAVING is on the
> after-subtraction product_id and you get no results, rather than the one
> result.
> Note that this is _not_ true in HyperSQL, as it has different semantics
> around the HAVING namespace.
> {noformat}
> select "product_id" - 1000 as "product_id"
> from (
> select "product_id", avg("gross_weight") as agw
> from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight"
> FROM (VALUES(0))) as product
> where "net_weight" < 100
> group by "product_id" having "product_id" > 0){noformat}
> becomes
> {noformat}
> SELECT "product_id" - 1000 AS "product_id"
> from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight"
> FROM (VALUES(0))) as product
> WHERE "net_weight" < 100
> GROUP BY "product_id"
> HAVING "product_id" > 0{noformat}
> But the meaning is preserved: both return a row.
> I'm not enough of a SQL standards expert to know which one is being more
> compliant, but presumably both would have to be supported via some sort of
> flag?
> I think the fix would be to force the subselect on dialects such as BigQuery
> that have this behavior. Probably something that looks a lot like
> [https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047]
> The test, of course, looks like pretty silly SQL no one would ever write, but
> the point is this is what's generated when you have
> {noformat}
> Project f(x) as x
> Filter g(x)
> Aggregate {}, ...{noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3664) Sort in subquery is lost when converting SqlNode to Relnode
[
https://issues.apache.org/jira/browse/CALCITE-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wang Yanlin updated CALCITE-3664:
-
Summary: Sort in subquery is lost when converting SqlNode to Relnode (was:
Lost sort in subquery when converting SqlNode to Relnode)
> Sort in subquery is lost when converting SqlNode to Relnode
> ---
>
> Key: CALCITE-3664
> URL: https://issues.apache.org/jira/browse/CALCITE-3664
> Project: Calcite
> Issue Type: Bug
>Reporter: Wang Yanlin
>Assignee: Wang Yanlin
>Priority: Major
>
> Lost sort in subquery when converting a SqlNode to Relnode, making its
> semantics changed.
> The test case to reproduce
> {code:java}
> // SqlToRelConverterTest
> @Test public void testSortInSubquery() {
> final String sql = "select ename from (select ename from emp order by
> ename) a";
> sql(sql).ok();
> }
> {code}
> This test case will success with this plan.
> {code:java}
>
>
>
>
>
>
>
>
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Assigned] (CALCITE-3664) Lost sort in subquery when converting SqlNode to Relnode
[
https://issues.apache.org/jira/browse/CALCITE-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wang Yanlin reassigned CALCITE-3664:
Assignee: Wang Yanlin
> Lost sort in subquery when converting SqlNode to Relnode
>
>
> Key: CALCITE-3664
> URL: https://issues.apache.org/jira/browse/CALCITE-3664
> Project: Calcite
> Issue Type: Bug
>Reporter: Wang Yanlin
>Assignee: Wang Yanlin
>Priority: Major
>
> Lost sort in subquery when converting a SqlNode to Relnode, making its
> semantics changed.
> The test case to reproduce
> {code:java}
> // SqlToRelConverterTest
> @Test public void testSortInSubquery() {
> final String sql = "select ename from (select ename from emp order by
> ename) a";
> sql(sql).ok();
> }
> {code}
> This test case will success with this plan.
> {code:java}
>
>
>
>
>
>
>
>
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3664) Lost sort in subquery when converting SqlNode to Relnode
Wang Yanlin created CALCITE-3664:
Summary: Lost sort in subquery when converting SqlNode to Relnode
Key: CALCITE-3664
URL: https://issues.apache.org/jira/browse/CALCITE-3664
Project: Calcite
Issue Type: Bug
Reporter: Wang Yanlin
Lost sort in subquery when converting a SqlNode to Relnode, making its
semantics changed.
The test case to reproduce
{code:java}
// SqlToRelConverterTest
@Test public void testSortInSubquery() {
final String sql = "select ename from (select ename from emp order by
ename) a";
sql(sql).ok();
}
{code}
This test case will success with this plan.
{code:java}
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3662) Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate field has no alias
[
https://issues.apache.org/jira/browse/CALCITE-3662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-3662:
Labels: pull-request-available (was: )
> Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate
> field has no alias
> ---
>
> Key: CALCITE-3662
> URL: https://issues.apache.org/jira/browse/CALCITE-3662
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.21.0
>Reporter: Lei Jiang
>Assignee: Lei Jiang
>Priority: Major
> Labels: pull-request-available
> Fix For: next
>
>
> Input SQL:
> {code:java}
> select sum(sal) from scott.emp group by ename limit 10
> {code}
> Optimized Plan:
> {code:java}
> JdbcToEnumerableConverter
> JdbcProject(EXPR$0=[$1])
> JdbcSort(fetch=[10])
> JdbcAggregate(group=[{1}], EXPR$0=[SUM($5)])
> JdbcTableScan(table=[[SCOTT, EMP]])
> {code}
> Output wrong SQL: outter select list is wrong.
> {code:java}
> SELECT SUM("SAL")
> FROM (SELECT "ENAME", SUM("SAL")
> FROM "SCOTT"."EMP"
> GROUP BY "ENAME"
> LIMIT 10) AS "t0" {code}
> It should be:
> {code:java}
> SELECT SUM("SAL")
> FROM "SCOTT"."EMP"
> GROUP BY "ENAME"
> LIMIT 10
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3662) Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate field has no alias
[
https://issues.apache.org/jira/browse/CALCITE-3662?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lei Jiang updated CALCITE-3662:
---
Summary: Generate wrong SQL when plan contains Project(Sort(Aggregate)) and
aggregate field has no alias (was: Generate wrong SQL when plan contains
Project(Sort(Aggregate)) and aggreagated field has no alias)
> Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate
> field has no alias
> ---
>
> Key: CALCITE-3662
> URL: https://issues.apache.org/jira/browse/CALCITE-3662
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.21.0
>Reporter: Lei Jiang
>Assignee: Lei Jiang
>Priority: Major
> Fix For: next
>
>
> Input SQL:
> {code:java}
> select sum(sal) from scott.emp group by ename limit 10
> {code}
> Optimized Plan:
> {code:java}
> JdbcToEnumerableConverter
> JdbcProject(EXPR$0=[$1])
> JdbcSort(fetch=[10])
> JdbcAggregate(group=[{1}], EXPR$0=[SUM($5)])
> JdbcTableScan(table=[[SCOTT, EMP]])
> {code}
> Output wrong SQL: outter select list is wrong.
> {code:java}
> SELECT SUM("SAL")
> FROM (SELECT "ENAME", SUM("SAL")
> FROM "SCOTT"."EMP"
> GROUP BY "ENAME"
> LIMIT 10) AS "t0" {code}
> It should be:
> {code:java}
> SELECT SUM("SAL")
> FROM "SCOTT"."EMP"
> GROUP BY "ENAME"
> LIMIT 10
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3663) Support for TRIM function in Bigquery dialect
[
https://issues.apache.org/jira/browse/CALCITE-3663?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17006648#comment-17006648
]
Dhirenda Gautam commented on CALCITE-3663:
--
Raised the PR https://github.com/apache/calcite/pull/1714
> Support for TRIM function in Bigquery dialect
> -
>
> Key: CALCITE-3663
> URL: https://issues.apache.org/jira/browse/CALCITE-3663
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Dhirenda Gautam
>Priority: Major
>
> In current Calcite implementation for query : SELECT TRIM('ABC') for
> big-query Dialect it translated into SELECT TRIM(BOTH ' ' FROM 'ABC') .
> But the appropriate query for BigQuery is :: SELECT TRIM('ABC')
> Similarly below query
> SELECT TRIM(BOTH 'a' from 'ABC')
> SELECT TRIM(LEADING ' ' from 'ABC')
> SELECT TRIM(TRAILING ' ' from 'ABC') are translated into invalid BigQuery
> query.
> Unparse logic for the trim has been handle in BigQuery dialect to convert the
> source Trim query into valid bigQuery query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
