[
https://issues.apache.org/jira/browse/CALCITE-6162?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17796494#comment-17796494
]
Julian Hyde commented on CALCITE-6162:
--------------------------------------
I see you have {{cross join}} and {{{}inner join{}}}. Is there a
straightforward way to do {{left join}}? E.g.
{code}
select e.empno, e.ename, c.t
from emp e
left join (select 7934 as ono, current_timestamp as t) c
on e.empno = c.ono
{code}
> 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)
