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

["Sergey Nuyanzin (Jira)" <ji...@apache.org>]

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

Sergey Nuyanzin closed CALCITE-6162.
------------------------------------

Resolved in release 1.37.0 (2024-05-06)

> 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)