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