You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "jchen5 (via GitHub)" <gi...@apache.org> on 2023/07/14 16:33:08 UTC

[GitHub] [spark] jchen5 opened a new pull request, #42007: [SPARK-44431][SQL] Fix behavior of null IN (empty list) in optimization rules

jchen5 opened a new pull request, #42007:
URL: https://github.com/apache/spark/pull/42007

   ### What changes were proposed in this pull request?
   `null IN (empty list)` incorrectly evaluates to null, when it should evaluate to false. (The reason it should be false is because a IN (b1, b2) is defined as a = b1 OR a = b2, and an empty IN list is treated as an empty OR which is false. This is specified by ANSI SQL.)
   
   Many places in Spark execution (In, InSet, InSubquery) and optimization (OptimizeIn, NullPropagation) implemented this wrong behavior. This is a longstanding correctness issue which has existed since null support for IN expressions was first added to Spark.
   
   This PR fixes the optimization rules OptimizeIn and NullPropagation, which followed the preexisting, incorrect execution behavior. The execution fixes will be in the next PR.
   
   The behavior is under a flag, which will be available to revert to the legacy behavior if needed. This flag is set to disable the new behavior until all of the fix PRs are complete.
   
   See [this doc](https://docs.google.com/document/d/15ttcB3OjGx5_WFKHB2COjQUbFHj5LrfNQv_d26o-wmI/edit) for more information.
   
   ### Why are the changes needed?
   Fix wrong SQL semantics
   
   ### Does this PR introduce _any_ user-facing change?
   Not yet, but will fix wrong SQL semantics when enabled
   
   ### How was this patch tested?
   Add unit tests and sql tests.


-- 
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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


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


[GitHub] [spark] cloud-fan commented on a diff in pull request #42007: [SPARK-44431][SQL] Fix behavior of null IN (empty list) in optimization rules

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #42007:
URL: https://github.com/apache/spark/pull/42007#discussion_r1264806089


##########
sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-null-semantics.sql:
##########
@@ -0,0 +1,57 @@
+create temp view v (c) as values (1), (null);
+create temp view v_empty (e) as select 1 where false;
+
+-- Note: tables and temp views hit different optimization/execution codepaths
+create table t(c int) using parquet;

Review Comment:
   nit: if file format doesn't matter, probably json/csv is better as it's faster to create and read small tables.



-- 
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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


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


[GitHub] [spark] jchen5 commented on pull request #42007: [SPARK-44431][SQL] Fix behavior of null IN (empty list) in optimization rules

Posted by "jchen5 (via GitHub)" <gi...@apache.org>.
jchen5 commented on PR #42007:
URL: https://github.com/apache/spark/pull/42007#issuecomment-1642057004

   @cloud-fan PR is updated


-- 
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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


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


[GitHub] [spark] cloud-fan closed pull request #42007: [SPARK-44431][SQL] Fix behavior of null IN (empty list) in optimization rules

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan closed pull request #42007: [SPARK-44431][SQL] Fix behavior of null IN (empty list) in optimization rules
URL: https://github.com/apache/spark/pull/42007


-- 
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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


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


[GitHub] [spark] jchen5 commented on pull request #42007: [SPARK-44431][SQL] Fix behavior of null IN (empty list) in optimization rules

Posted by "jchen5 (via GitHub)" <gi...@apache.org>.
jchen5 commented on PR #42007:
URL: https://github.com/apache/spark/pull/42007#issuecomment-1636103261

   @cloud-fan 


-- 
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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


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


[GitHub] [spark] jchen5 commented on a diff in pull request #42007: [SPARK-44431][SQL] Fix behavior of null IN (empty list) in optimization rules

Posted by "jchen5 (via GitHub)" <gi...@apache.org>.
jchen5 commented on code in PR #42007:
URL: https://github.com/apache/spark/pull/42007#discussion_r1264806758


##########
sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-null-semantics.sql:
##########
@@ -0,0 +1,57 @@
+create temp view v (c) as values (1), (null);
+create temp view v_empty (e) as select 1 where false;
+
+-- Note: tables and temp views hit different optimization/execution codepaths
+create table t(c int) using parquet;

Review Comment:
   👍 will update



##########
sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-null-semantics.sql:
##########
@@ -0,0 +1,57 @@
+create temp view v (c) as values (1), (null);
+create temp view v_empty (e) as select 1 where false;
+
+-- Note: tables and temp views hit different optimization/execution codepaths
+create table t(c int) using parquet;

Review Comment:
   👍 will update



-- 
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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


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


[GitHub] [spark] cloud-fan commented on pull request #42007: [SPARK-44431][SQL] Fix behavior of null IN (empty list) in optimization rules

Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on PR #42007:
URL: https://github.com/apache/spark/pull/42007#issuecomment-1643037992

   thanks, merging to master/3.5!


-- 
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.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


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