You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Herman van Hovell (JIRA)" <ji...@apache.org> on 2016/11/28 16:54:00 UTC
[jira] [Comment Edited] (SPARK-18597) Do not push down filters for
LEFT ANTI JOIN
[ https://issues.apache.org/jira/browse/SPARK-18597?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15702454#comment-15702454 ]
Herman van Hovell edited comment on SPARK-18597 at 11/28/16 4:53 PM:
---------------------------------------------------------------------
[~hvanhovell], [~dongjoon]], [~smilegator] FYI:
I edited the description to reflect the correct behaviour of the {{LeftAnti}} in the example. The correct answer is all the rows from tbl_a as there is no row in tbl_a that satisfies the predicate tbl_a.c1 = tbl_a.c2 in the ON clause. By the LeftAnti semantics of tbl_a except rows that satisfy the predicate in the ON clause, all rows from tbl_a are returned.
was (Author: nsyca):
[~hvanhovell], @dongjoon, [~smilegator] FYI:
I edited the description to reflect the correct behaviour of the {{LeftAnti}} in the example. The correct answer is all the rows from tbl_a as there is no row in tbl_a that satisfies the predicate tbl_a.c1 = tbl_a.c2 in the ON clause. By the LeftAnti semantics of tbl_a except rows that satisfy the predicate in the ON clause, all rows from tbl_a are returned.
> Do not push down filters for LEFT ANTI JOIN
> -------------------------------------------
>
> Key: SPARK-18597
> URL: https://issues.apache.org/jira/browse/SPARK-18597
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Reporter: Herman van Hovell
> Assignee: Herman van Hovell
> Priority: Minor
> Labels: correctness
> Fix For: 2.1.0
>
>
> The optimizer pushes down filters for left anti joins. This unfortunately has the opposite effect. For example:
> {noformat}
> sql("create or replace temporary view tbl_a as values (1, 5), (2, 1), (3, 6) as t(c1, c2)")
> sql("create or replace temporary view tbl_b as values 1 as t(c1)")
> sql("""
> 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)
> """)
> {noformat}
> Should return rows [1, 5], [2, 1] & [3, 6], but returns no rows.
> The upside is that this will only happen when you use a really weird anti-join (only referencing the table on the left hand side).
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org