[ https://issues.apache.org/jira/browse/SPARK-16951?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15413773#comment-15413773 ]
Nattavut Sutyanyong commented on SPARK-16951: --------------------------------------------- I found this [document|https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684] is the closest I can find discussing this very fine point in the reply to a question at October 02, 2002 - 10:25 am UTC {noformat} Because NULL means -- gee, I don't know. (litterally, null means Unknown) So, the predicate where x not in ( NULL ) evaluates to neither TRUE, nor FALSE ops$tk...@ora817dev.us.oracle.com> select * from dual where dummy not in ( NULL ); no rows selected {noformat} > 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