[jira] [Commented] (CALCITE-4340) Correlated Queries in ON clause do not work when referring to left side
[
https://issues.apache.org/jira/browse/CALCITE-4340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17216431#comment-17216431
]
Danny Chen commented on CALCITE-4340:
-
What's the relationship of this issue with CALCITE-4210, is it a enhancement ?
> Correlated Queries in ON clause do not work when referring to left side
> ---
>
> Key: CALCITE-4340
> URL: https://issues.apache.org/jira/browse/CALCITE-4340
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: James Starr
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Changes from CALCITE-4210 does not robustly support correlated queries in ON
> clauses.
> For the short term I am going revert the logic for supporting ON clause and
> throw an exception when correlated queries occur in ON clause.
> Then I will follow up with a fix that will support single subqueries in on
> clauses. This will require fixing the offset in Blackboard.register,
> correctly detect if correlated variable is on the being used and adding a
> rule RelDecorrelator joined decorrelations.
> Given:
> {code:sql}
> SELECT *
> FROM dept
> LEFT JOIN bonus ON bonus.job = (
> SELECT emp.job
> FROM emp
> WHERE emp.deptno = dept.deptno
> )
> {code}
> Should generate
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1], ENAME=[$2], JOB=[$3], SAL=[$4],
> COMM=[$5])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}]) //requiring dept.DEPTNO as the correlated value
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalFilter(condition=[=($1, $4)]) //This is comparing bonus.job =
> emp.job
> LogicalJoin(condition=[true], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalProject(JOB=[$2])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO)]) //all employees
> for a given department
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> But instead generates:
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1], ENAME=[$2], JOB=[$3], SAL=[$4],
> COMM=[$5])
> LogicalJoin(condition=[=($3, $0)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}])
> LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
> LogicalProject(JOB=[$2])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4277) skip rule match when rel's relset is obsolete
[ https://issues.apache.org/jira/browse/CALCITE-4277?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17216421#comment-17216421 ] Chunwei Lei commented on CALCITE-4277: -- Sounds fine. > skip rule match when rel's relset is obsolete > - > > Key: CALCITE-4277 > URL: https://issues.apache.org/jira/browse/CALCITE-4277 > Project: Calcite > Issue Type: Improvement >Reporter: Jiatao Tao >Assignee: Jiatao Tao >Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4342) More aggregate functions should be marked as splittable and ignore distinct optionality
[
https://issues.apache.org/jira/browse/CALCITE-4342?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17216403#comment-17216403
]
Liya Fan commented on CALCITE-4342:
---
[~julianhyde] Thanks a lot for your feedback. I have changed the title and
description accordingly. Please check.
Also, the suggestions about \{{getRollup}} methods sounds reasonable, I will
try to refactor them.
> More aggregate functions should be marked as splittable and ignore distinct
> optionality
> ---
>
> Key: CALCITE-4342
> URL: https://issues.apache.org/jira/browse/CALCITE-4342
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Liya Fan
>Assignee: Liya Fan
>Priority: Major
>
> As discussed in the mailing list, there are more aggregate functions (e.g.
> SINGLE_VALUE, ANY_VALUE) that can be classified as splittable and ignore
> distinct optionality. We need to tell the optimizer explicitly about this, so
> more optimizations can be applied in various scenarios.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4342) More aggregate functions should be marked as splittable and ignore distinct optionality
[ https://issues.apache.org/jira/browse/CALCITE-4342?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Liya Fan updated CALCITE-4342: -- Description: As discussed in the mailing list, there are more aggregate functions (e.g. SINGLE_VALUE, ANY_VALUE) that can be classified as splittable and ignore distinct optionality. We need to tell the optimizer explicitly about this, so more optimizations can be applied in various scenarios. (was: As discussed in the mailing list, there are more aggregate functions (e.g. SINGLE_VALUE, ANY_VALUE) that can be classified as splittable and duplicate-insensitive. We need to tell the optimizer explicitly about this, so more optimizations can be applied in various scenarios. ) > More aggregate functions should be marked as splittable and ignore distinct > optionality > --- > > Key: CALCITE-4342 > URL: https://issues.apache.org/jira/browse/CALCITE-4342 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Liya Fan >Assignee: Liya Fan >Priority: Major > > As discussed in the mailing list, there are more aggregate functions (e.g. > SINGLE_VALUE, ANY_VALUE) that can be classified as splittable and ignore > distinct optionality. We need to tell the optimizer explicitly about this, so > more optimizations can be applied in various scenarios. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-4342) More aggregate functions should be marked as splittable and ignore distinct optionality
[ https://issues.apache.org/jira/browse/CALCITE-4342?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Liya Fan updated CALCITE-4342: -- Summary: More aggregate functions should be marked as splittable and ignore distinct optionality (was: More aggregate functions should be marked as splittable and duplicate-insensitive) > More aggregate functions should be marked as splittable and ignore distinct > optionality > --- > > Key: CALCITE-4342 > URL: https://issues.apache.org/jira/browse/CALCITE-4342 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Liya Fan >Assignee: Liya Fan >Priority: Major > > As discussed in the mailing list, there are more aggregate functions (e.g. > SINGLE_VALUE, ANY_VALUE) that can be classified as splittable and > duplicate-insensitive. We need to tell the optimizer explicitly about this, > so more optimizations can be applied in various scenarios. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-4321) JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL
[
https://issues.apache.org/jira/browse/CALCITE-4321?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17216361#comment-17216361
]
Jeremiah Rhoads Hall commented on CALCITE-4321:
---
Happy to help and thanks for wrapping it up!
> JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL
> -
>
> Key: CALCITE-4321
> URL: https://issues.apache.org/jira/browse/CALCITE-4321
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
> Environment: MacOS 10.15, Java 11, PostgreSQL 12
>Reporter: Jeremiah Rhoads Hall
>Assignee: Julian Hyde
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.27.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> I tried out the PIVOT feature added in CALCITE-3752 and found what I think is
> a bug where the JDBC adapter will not generate `FILTER (WHERE ...)`
> expressions from relational expressions.
> Initially, I noticed that the SQL that was generated when trying out the
> pivot featue was missing the filter-where, but even when included directly in
> the Calcite SQL it's not in the SQL issued by the JDBC adapter.
> For example if I try to execute the following with Calcite:
> {code}
> select sum(amount) filter (where some_date_column = '2020-10-01') from
> my_table{code}
> The SQL issued by the adapter will be:
> {code}
> select sum(amount) from my_table
> {code}
> I have a small change here with a test case and a fix:
> https://github.com/apache/calcite/pull/2204
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
