[
https://issues.apache.org/jira/browse/SPARK-25259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16594563#comment-16594563
]
Yuming Wang commented on SPARK-25259:
-------------------------------------
I'm working on.
> Left/Right join support push down during-join predicates
> --------------------------------------------------------
>
> Key: SPARK-25259
> URL: https://issues.apache.org/jira/browse/SPARK-25259
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 2.4.0
> Reporter: Yuming Wang
> Priority: Major
>
> For example:
> {code:sql}
> create temporary view EMPLOYEE as select * from values
> ("000010", "HAAS", "A00"),
> ("000010", "THOMPSON", "B01"),
> ("000030", "KWAN", "C01"),
> ("000110", "LUCCHESSI", "A00"),
> ("000120", "O'CONNELL", "A))"),
> ("000130", "QUINTANA", "C01")
> as EMPLOYEE(EMPNO, LASTNAME, WORKDEPT);
> create temporary view DEPARTMENT as select * from values
> ("A00", "SPIFFY COMPUTER SERVICE DIV.", "000010"),
> ("B01", "PLANNING", "000020"),
> ("C01", "INFORMATION CENTER", "000030"),
> ("D01", "DEVELOPMENT CENTER", null)
> as EMPLOYEE(DEPTNO, DEPTNAME, MGRNO);
> create temporary view PROJECT as select * from values
> ("AD3100", "ADMIN SERVICES", "D01"),
> ("IF1000", "QUERY SERVICES", "C01"),
> ("IF2000", "USER EDUCATION", "E01"),
> ("MA2100", "WELD LINE AUDOMATION", "D01"),
> ("PL2100", "WELD LINE PLANNING", "01")
> as EMPLOYEE(PROJNO, PROJNAME, DEPTNO);
> {code}
> below SQL:
> {code:sql}
> SELECT PROJNO, PROJNAME, P.DEPTNO, DEPTNAME
> FROM PROJECT P LEFT OUTER JOIN DEPARTMENT D
> ON P.DEPTNO = D.DEPTNO
> AND P.DEPTNO='E01';
> {code}
> can Optimized to:
> {code:sql}
> SELECT PROJNO, PROJNAME, P.DEPTNO, DEPTNAME
> FROM PROJECT P LEFT OUTER JOIN (SELECT * FROM DEPARTMENT WHERE DEPTNO='E01') D
> ON P.DEPTNO = D.DEPTNO
> AND P.DEPTNO='E01';
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
