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)
