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/10/27 09:47:00 UTC
[jira] [Updated] (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 updated SPARK-25259:
--------------------------------
Description:
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 DEPARTMENT(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 PROJECT(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}
was:
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}
> 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 DEPARTMENT(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 PROJECT(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