[GitHub] [spark] dilipbiswal opened a new pull request #22141: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions.

[GitBox <gi...@apache.org>]

dilipbiswal opened a new pull request #22141: [SPARK-25154][SQL] Support NOT IN sub-queries inside nested OR conditions.
URL: https://github.com/apache/spark/pull/22141
 
 
   ## What changes were proposed in this pull request?
   Currently NOT IN subqueries (predicated null aware subquery) are not allowed inside OR expressions. We currently catch this condition in checkAnalysis and throw an error.
   
   This PR enhances the subquery rewrite to support this type of queries.
   
   Query
   ```SQL
   SELECT * FROM s1 WHERE a > 5 or b NOT IN (SELECT c FROM s2);
   ```
   Optimized Plan
   ```SQL
   == Optimized Logical Plan ==
   Project [a#3, b#4]
   +- Filter ((a#3 > 5) || NOT exists#7)
      +- Join ExistenceJoin(exists#7), ((b#4 = c#5) || isnull((b#4 = c#5)))
         :- HiveTableRelation `default`.`s1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [a#3, b#4]
         +- Project [c#5]
            +- HiveTableRelation `default`.`s2`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [c#5, d#6]
   
   
   ```
   ## How was this patch tested?
   Added new testsin SQLQueryTestSuite, RewriteSubquerySuite and SubquerySuite.
   
   Output from DB2 as a reference:
   
   [nested-not-db2.txt](https://github.com/apache/spark/files/2299945/nested-not-db2.txt)
   

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org