[jira] [Resolved] (CALCITE-6162) Add rule(s) to remove joins with constant single tuple relations

["Mihai Budiu (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-6162?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mihai Budiu resolved CALCITE-6162.
----------------------------------
    Fix Version/s: 1.37.0
       Resolution: Fixed

Fixed in https://github.com/apache/calcite/commit/6672f7e69b3c7759131b2b74fdd3ed5fbe100587
Thank you [~hanu.ncr]

> Add rule(s) to remove joins with constant single tuple relations
> ----------------------------------------------------------------
>
>                 Key: CALCITE-6162
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6162
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>            Reporter: Stamatis Zampetakis
>            Assignee: Hanumath Rao Maduri
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.37.0
>
>
> In various cases SQL users tend to create joins even when it is not really necessary. One common pattern is creating joins (or cartesian products) with constant relations with exactly one tuple.
> *Q1*
> Before:
> {code:sql}
> select e.empno, e.ename, c.* from emp e cross join (select 5, current_timestamp) c;
> {code}
> After:
> {code:sql}
> select e.empno, e.ename, 5, current_timestamp from emp e;
> {code}
> *Q2*
> Before:
> {code:sql}
> select e.empno, e.ename, c.t from emp e inner join (select 7934 as ono, current_timestamp as t) c on e.empno=c.ono;
> {code}
> After:
> {code:sql}
> select e.empno, e.ename, current_timestamp from emp e where e.empno=7934;
> {code}
> In the queries outlined above the one side of the join is constant and has exactly one tuple so the join can be dropped.
> In a nutshell the new rule(s) should be able to transform the "Before" to "After" for the above queries.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)