[
https://issues.apache.org/jira/browse/CALCITE-4143?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17166235#comment-17166235
]
Jiatao Tao edited comment on CALCITE-4143 at 7/28/20, 7:57 AM:
---------------------------------------------------------------
Hi [~julianhyde] [~liupengcheng]
A case I met like that(Simplified, just for explanation, the SQL is
meaningless):
SQL:
{code:java}
SELECT
u.name
from
USERS u LEFT JOIN JOBS j
ON u.ID = j.ID
AND j.name = 'Bob'
WHERE
u.name = 'Alice'
{code}
Optimized plan:
{code:java}
1 EnumerableProject(name=[$1])
2 EnumerableHashJoin(condition=[=($0, $2)], joinType=[left])
3 EnumerableProject(ID=[$0], NAME=[$1])
4 EnumerableFilter(condition=[=($1, _UTF-8'Alice')])
5 EnumerableTableScan(table=[[default_ns, RECR, USERS]])
6 EnumerableProject(ID=[$0], NAME=[$1])
7 EnumerableFilter(condition=[=($1, _UTF-8'Bob')])
8 EnumerableTableScan(table=[[default_ns, RECR, JOBS]])
{code}
See line 6, we only use job#name as a filter, we do not need to project that
after the filter.
After debugging, I found at first, the filter is above the project, it is
reasonable at that time the project has "NAME", but after the filter pushdown,
the "NAME" needn't be projected.
was (Author: aron.tao):
Hi [~julianhyde] [~liupengcheng]
A case I met like that(Simplified, just for explanation, the SQL is
meaningless):
SQL:
{code:java}
SELECT
u.name
from
USERS u LEFT JOIN JOBS j
ON u.ID = j.ID
AND j.name = 'Bob'
WHERE
u.name = 'Alice'
{code}
Optimized plan:
{code:java}
1 EnumerableProject(name=[$1])
2 EnumerableHashJoin(condition=[=($0, $2)], joinType=[left])
3 EnumerableProject(ID=[$0], NAME=[$1])
4 EnumerableFilter(condition=[=($1, _UTF-8'Alice')])
5 EnumerableTableScan(table=[[default_ns, RECR, USERS]])
6 EnumerableProject(ID=[$0], NAME=[$1])
7 EnumerableFilter(condition=[=($1, _UTF-8'Bob')])
8 EnumerableTableScan(table=[[default_ns, RECR, JOBS]])
{code}
See line 6, we only use job#name as a filter, we do not need to project that
after the filter.
After debugging, I found at first, the filter is above the prj.
> Trim fields after XXProjectTransposeRule
> ----------------------------------------
>
> Key: CALCITE-4143
> URL: https://issues.apache.org/jira/browse/CALCITE-4143
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Jiatao Tao
> Assignee: Jiatao Tao
> Priority: Major
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
