diff options
author | Herman van Hovell <hvanhovell@databricks.com> | 2016-11-28 07:10:52 -0800 |
---|---|---|
committer | Herman van Hovell <hvanhovell@databricks.com> | 2016-11-28 07:10:52 -0800 |
commit | 38e29824d9a50464daa397c28e89610ed0aed4b6 (patch) | |
tree | 91de58c8327d60437eb6ba3ca2064d6f0e388c52 /sql/core/src/test/resources/sql-tests | |
parent | 9f273c5173c05017c3009faaf3e10f2f70a842d0 (diff) | |
download | spark-38e29824d9a50464daa397c28e89610ed0aed4b6.tar.gz spark-38e29824d9a50464daa397c28e89610ed0aed4b6.tar.bz2 spark-38e29824d9a50464daa397c28e89610ed0aed4b6.zip |
[SPARK-18597][SQL] Do not push-down join conditions to the right side of a LEFT ANTI join
## What changes were proposed in this pull request?
We currently push down join conditions of a Left Anti join to both sides of the join. This is similar to Inner, Left Semi and Existence (a specialized left semi) join. The problem is that this changes the semantics of the join; a left anti join filters out rows that matches the join condition.
This PR fixes this by only pushing down conditions to the left hand side of the join. This is similar to the behavior of left outer join.
## How was this patch tested?
Added tests to `FilterPushdownSuite.scala` and created a SQLQueryTestSuite file for left anti joins with a regression test.
Author: Herman van Hovell <hvanhovell@databricks.com>
Closes #16026 from hvanhovell/SPARK-18597.
Diffstat (limited to 'sql/core/src/test/resources/sql-tests')
-rw-r--r-- | sql/core/src/test/resources/sql-tests/inputs/anti-join.sql | 7 | ||||
-rw-r--r-- | sql/core/src/test/resources/sql-tests/results/anti-join.sql.out | 29 |
2 files changed, 36 insertions, 0 deletions
diff --git a/sql/core/src/test/resources/sql-tests/inputs/anti-join.sql b/sql/core/src/test/resources/sql-tests/inputs/anti-join.sql new file mode 100644 index 0000000000..0346f57d60 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/anti-join.sql @@ -0,0 +1,7 @@ +-- SPARK-18597: Do not push down predicates to left hand side in an anti-join +CREATE OR REPLACE TEMPORARY VIEW tbl_a AS VALUES (1, 1), (2, 1), (3, 6) AS T(c1, c2); +CREATE OR REPLACE TEMPORARY VIEW tbl_b AS VALUES 1 AS T(c1); + +SELECT * +FROM tbl_a + LEFT ANTI JOIN tbl_b ON ((tbl_a.c1 = tbl_a.c2) IS NULL OR tbl_a.c1 = tbl_a.c2); diff --git a/sql/core/src/test/resources/sql-tests/results/anti-join.sql.out b/sql/core/src/test/resources/sql-tests/results/anti-join.sql.out new file mode 100644 index 0000000000..6f38c4d08b --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/anti-join.sql.out @@ -0,0 +1,29 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 3 + + +-- !query 0 +CREATE OR REPLACE TEMPORARY VIEW tbl_a AS VALUES (1, 1), (2, 1), (3, 6) AS T(c1, c2) +-- !query 0 schema +struct<> +-- !query 0 output + + + +-- !query 1 +CREATE OR REPLACE TEMPORARY VIEW tbl_b AS VALUES 1 AS T(c1) +-- !query 1 schema +struct<> +-- !query 1 output + + + +-- !query 2 +SELECT * +FROM tbl_a + LEFT ANTI JOIN tbl_b ON ((tbl_a.c1 = tbl_a.c2) IS NULL OR tbl_a.c1 = tbl_a.c2) +-- !query 2 schema +struct<c1:int,c2:int> +-- !query 2 output +2 1 +3 6 |