[
https://issues.apache.org/jira/browse/CALCITE-6162?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hanumath Rao Maduri reassigned CALCITE-6162:
--------------------------------------------
Assignee: Hanumath Rao Maduri
> 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
>
> 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)
