[
https://issues.apache.org/jira/browse/SPARK-16951?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15413753#comment-15413753
]
Nattavut Sutyanyong edited comment on SPARK-16951 at 8/9/16 3:55 PM:
---------------------------------------------------------------------
Thanks, @hvanhovelll, for the VLDB paper. It is a joyful reading. I take it
that you mentioned to the last paragraph in section 6 before section 6.1 in the
paper:
"Suppose the subquery returns the following set of values \{7, 11, NULL\} and
T1.X has the following set of values: \{NULL, 5, 11\}, The <> ALL operation
_(@nsyca: that, in the paper, is equivalent to NOT IN)_ can be expressed as
T1.x <> 7 AND T1.x <> 11 AND T1.x <> NULL. This evaluates to UNKNOWN
irrespective of the value of T1.x. Thus, for this set of values, Q23 will
return no rows."
The point here is "what is the correct interpretation of the result from an
empty subquery?" Take this example:
{noformat}
scala> Seq(1,2).toDF("c1").createOrReplaceTempView("t1")
scala> Seq(1).toDF("c2").createOrReplaceTempView("t2")
scala> sql("select * from t2 where 1=2").createOrReplaceTempView("empty")
scala> sql("select * from t1 where c1 in (select c2 from empty").show
+---+
| c1|
+---+
+---+
{noformat}
The result from the IN subquery statement, running from the Spark master trunk,
returns no rows. With that, I would conclude that the NOT IN subquery should
also return no rows.
was (Author: nsyca):
Thanks, @hvanhovelll, for the VLDB paper. It is a joyful reading. I take it
that you mentioned to the last paragraph in section 6 before section 6.1 in the
paper:
"Suppose the subquery returns the following set of values \x 7, 11, NULL} and
T1.X has the following set of values: {NULL, 5, 11}, The <> ALL operation
_(@nsyca: that, in the paper, is equivalent to NOT IN)_ can be expressed as
T1.x <> 7 AND T1.x <> 11 AND T1.x <> NULL. This evaluates to UNKNOWN
irrespective of the value of T1.x. Thus, for this set of values, Q23 will
return no rows."
> Alternative implementation of NOT IN to Anti-join
> -------------------------------------------------
>
> Key: SPARK-16951
> URL: https://issues.apache.org/jira/browse/SPARK-16951
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 2.0.0
> Reporter: Nattavut Sutyanyong
>
> A transformation currently used to process {{NOT IN}} subquery is to rewrite
> to a form of Anti-join with null-aware property in the Logical Plan and then
> translate to a form of {{OR}} predicate joining the parent side and the
> subquery side of the {{NOT IN}}. As a result, the presence of {{OR}}
> predicate is limited to the nested-loop join execution plan, which will have
> a major performance implication if both sides' results are large.
> This JIRA sketches an idea of changing the OR predicate to a form similar to
> the technique used in the implementation of the Existence join that addresses
> the problem of {{EXISTS (..) OR ..}} type of queries.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
