[jira] [Updated] (SPARK-58481) InSubqueryExec declares incorrect nullability, which can cause incorrect query results

["Josh Rosen (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/SPARK-58481?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Josh Rosen updated SPARK-58481:
-------------------------------
    Summary: InSubqueryExec declares incorrect nullability, which can cause incorrect query results  (was: FULL OUTER JOIN with a NOT IN subquery condition returns the cross product)

> InSubqueryExec declares incorrect nullability, which can cause incorrect query results
> --------------------------------------------------------------------------------------
>
>                 Key: SPARK-58481
>                 URL: https://issues.apache.org/jira/browse/SPARK-58481
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 4.0.0
>            Reporter: Josh Rosen
>            Priority: Major
>              Labels: correctness
>
> The following query returns incorrect results in Spark 3.4.0 and all later versions (I haven't tested earlier versions):
> {code:sql}
> CREATE TABLE t0(c0 INT) USING PARQUET;
> INSERT INTO t0 VALUES (1), (2), (3);
> CREATE TABLE t1(c0 INT) USING PARQUET;
> INSERT INTO t1 VALUES (10), (20), (30);
> CREATE TABLE t3(c0 INT) USING PARQUET;
> INSERT INTO t3 VALUES (99), (CAST(NULL AS INT));
> SELECT t0.c0, t1.c0 FROM t1 FULL OUTER JOIN t0 ON (5 NOT IN (SELECT t3.c0 FROM t3));
> {code}
> This returns 9 rows, the full cross product of tables {{t0}} and {{{}t1{}}}.
> The correct answer is 6 rows, because {{5 NOT IN (99, NULL)}} evaluates to UNKNOWn and a join condition that is not TRUE matches no rows, so a FULL OUTER JOIN must emit 3 + 3 = 6 null-padded rows.
> Setting {{spark.sql.codegen.factoryMode=NO_CODEGEN}} causes Spark to return the correct 6 rows.
> Controls on the same tables:
> ||ON condition||rows||
> |{{ON false}}|6|
> |{{ON CAST(NULL AS BOOLEAN)}}|6|
> |{{ON true}}|9|
> |{{ON (5 NOT IN (SELECT c0 FROM t3))}}|9, should be 6|
> h3. Suspected root cause
> The cause is a nullability declaration that does not match the values the expression can produce:
>  * {{InSubqueryExec.nullable}} is [defined|https://github.com/apache/spark/blob/4dcdd4dba544210898180a410b8b30e0b85157cb/sql/core/src/main/scala/org/apache/spark/sql/execution/subquery.scala#L128] as {{child.nullable}} .
>  * In our repro query, the child is the literal {{{}5{}}}, which is not nullable. But {{x IN (subquery)}} evaluates to UNKNOWN whenever no match is found and the subquery result contains NULL, and the declaration does not account for that.
>  * {{{}InSet{}}}, which {{InSubqueryExec}} delegates its own {{eval}} and {{doGenCode}} to, [derives it correctly |https://github.com/apache/spark/blame/4dcdd4dba544210898180a410b8b30e0b85157cb/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala#L669]as {{child.nullable || hasNull}} , so the wrapper and its delegate disagree.
>  * {{Not}} inherits the incorrect declaration. {{UnaryExpression.nullSafeCodeGen}} therefore takes its non-nullable branch, emitting {{isNull = FalseLiteral}} and computing {{!value }}unconditionally. The generated {{InSet}} code does the right thing: it leaves {{value}} at its default of {{false}} and raises {{{}isNull{}}}. Nothing reads {{{}isNull{}}}, so the condition is computed as {{{}!false{}}}, which is TRUE.
>  * The interpreted path checks the runtime value for null regardless of the declared nullability, which is why the two evaluators disagree.
> h3. Potential fix
> Derive nullability from the subquery output as well as the child, for example
> {{{}child.nullable || plan.output.head.nullable{}}}. The delegate's {{child.nullable || hasNull}} cannot be reused directly because {{hasNull}} needs the executed result; the expression above is the planning-time over-approximation. No codegen change is needed, since {{InSet{}}}'s generated code already models the NULL case.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

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