[jira] [Commented] (CALCITE-6162) Add rule(s) to remove joins with constant single tuple relations
[
https://issues.apache.org/jira/browse/CALCITE-6162?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17798437#comment-17798437
]
Julian Hyde commented on CALCITE-6162:
--
That sounds right.
> 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)
[jira] [Commented] (CALCITE-6162) Add rule(s) to remove joins with constant single tuple relations
[
https://issues.apache.org/jira/browse/CALCITE-6162?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17798090#comment-17798090
]
Stamatis Zampetakis commented on CALCITE-6162:
--
I haven't examined all possible joins but I have the feeling that we could
simplify most (if not all) of them. For the left join above we could possibly
make use of CASE WHEN operator:
{code:sql}
select c.empno, c.ename, c.t
from (select e.empno, e.ename, CASE WHEN (e.empno = 7934) THEN
current_timestamp ELSE null END as t from emp e) c
{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)
[jira] [Commented] (CALCITE-6162) Add rule(s) to remove joins with constant single tuple relations
[
https://issues.apache.org/jira/browse/CALCITE-6162?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Commented] (CALCITE-6162) Add rule(s) to remove joins with constant single tuple relations
[
https://issues.apache.org/jira/browse/CALCITE-6162?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17795762#comment-17795762
]
Stamatis Zampetakis commented on CALCITE-6162:
--
The
[PruneEmptyRules|https://github.com/apache/calcite/blob/56eb062ba2eae239e9fdda6891830cf2ec60b605/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java]
could be a good starting point for getting some inspiration on how to write,
test, and reuse code for the new rules.
> 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
>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)
