You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Yuming Wang (JIRA)" <ji...@apache.org> on 2018/08/29 07:46:00 UTC
[jira] [Resolved] (SPARK-25259) Left/Right join support push down
during-join predicates
[ https://issues.apache.org/jira/browse/SPARK-25259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yuming Wang resolved SPARK-25259.
---------------------------------
Resolution: Duplicate
> 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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org