You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2023/12/12 13:54:00 UTC

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

Stamatis Zampetakis created CALCITE-6162:
--------------------------------------------

             Summary: 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


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)