[jira] [Created] (SPARK-25259) Left/Right join support push down during-join predicates

["Yuming Wang (JIRA)" <ji...@apache.org>]

Yuming Wang created SPARK-25259:
-----------------------------------

             Summary: 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


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