Brian Wu created SPARK-60026:
--------------------------------
Summary: IN subquery returns false instead of NULL (UNKNOWN) when
the subquery result set contains NULL
Key: SPARK-60026
URL: https://issues.apache.org/jira/browse/SPARK-60026
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.1.1
Reporter: Brian Wu
When a value is tested with IN (<subquery>) and the subquery result set
contains NULL, and the value is not among the concrete (non-NULL) results,
Spark returns false. Per Spark's own documented NULL semantics, the result
should be UNKNOWN (NULL).
This also contradicts Spark's own IN-list behavior, which correctly returns
NULL for the identical set.
Reproduction:
{code:java}
-- IN subquery: value 1 is not among the concrete results {2},
-- and the subquery result set contains NULL.
SELECT 1 IN (SELECT c FROM (SELECT CAST(NULL AS INT) AS c UNION ALL SELECT 2)
t) AS r;
-- actual: false
-- expected: null (UNKNOWN)
-- IN list: the identical logical test over {2, NULL}.
SELECT 1 IN (2, CAST(NULL AS INT)) AS r;
-- actual: null {code}
Spark's own NULL Semantics reference specifies the expected behavior
(https://spark.apache.org/docs/latest/sql-ref-null-semantics.html#innot-in-subquery):
- TRUE is returned when the non-NULL value in question is found in the list
- FALSE is returned when the non-NULL value is not found in the list and the
list does not contain NULL values
- UNKNOWN is returned when the value is NULL, or the non-NULL value is not
found in the list and the list contains at least one NULL value
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
