You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "MaxGekk (via GitHub)" <gi...@apache.org> on 2026/10/17 08:12:15 UTC

[PR] [SPARK-60315][SQL] Evaluate the conjuncts after a NULL one in whole-stage codegen of a filter [spark]

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

   ### What changes were proposed in this pull request?
   
   Whole-stage codegen of a filter condition (`FilterExec`) and of the `FILTER` clause of an aggregate function (`AggregateCodegenSupport`) generates each top-level conjunct separately and drops the row as soon as one of them is FALSE **or NULL** (`if (isNull || !value) continue;`). The interpreted path evaluates the whole condition with `And.eval`, which stops only at FALSE. So when an earlier conjunct is NULL and a later one raises on that row, the query fails with codegen off and returns rows with it on.
   
   This PR changes the generated code so that only a FALSE conjunct stops the evaluation:
   
   - `GeneratePredicateHelper` gets three helpers: `nullConjunctFlag` (a fresh flag name, or `None`), `conjunctCheck` (the per-conjunct check) and `withNullConjunctFlag` (declares the flag before the checks and drops the row after them if it is set).
   - A NULL conjunct sets the flag, and the remaining conjuncts are still evaluated: `if (isNull) { flag = true; } else if (!value) continue;`. The row is dropped after all of them: `if (flag) continue;`.
   - The flag is generated only when some conjunct can be NULL after binding. The `IsNotNull` checks are never NULL and keep their early `continue`, so the tightened-nullability binding of the later conjuncts, and of the CSE precomputes, stays safe.
   - Both code paths are changed: `generatePredicateCode`, which the aggregate `FILTER` clause also uses, and the CSE branch of `FilterExec.doConsume`, which the code comments require to stay in step with it.
   - New legacy config `spark.sql.legacy.filterShortCircuitOnNullConjunct` (internal, default `false`, version 4.4.0). Set to `true`, it restores the previous codegen behavior. The interpreted path is unchanged in both modes.
   
   Join conditions and `MergeRowsExec` generate the whole condition through `And.doGenCode`, which already evaluates the right side after a NULL left side, so they are not changed.
   
   #### SQL standard
   
   - 7.11 `<where clause>`, GR 2 (and 10.9 `<aggregate function>`, GR 4a, for `<filter clause>`): the search condition is effectively evaluated for each row, and the result keeps the rows for which it is True.
   - 6.35 `<boolean value expression>`, Table 11: Unknown AND True is Unknown, Unknown AND False is False. After an Unknown conjunct the result of the search condition still depends on the remaining ones.
   - 9075-1 "Rule evaluation order" (SQL-92 3.3.4.4): only the *inessential* parts, those not needed to determine the result of the expression or search condition, may be skipped, and whether their exceptions are raised is implementation-dependent. After a NULL conjunct the rest is essential. The order of evaluation is implementation-dependent too, but in every order the raising conjunct is either evaluated first or is still essential, so the exception is raised. Only a FALSE conjunct makes the rest inessential.
   
   #### Other DBMSes
   
   Table `t(a INT, b INT, y)` with rows `(NULL, 1, <raises>)` and `(0, 1, <ok>)`. `<E>` raises on the first row:
   - `y + 1 > 0` with `y` at the maximum of its type: `INT`, or `BIGINT` on MySQL and MariaDB, which widen `INT + INT`;
   - `10 / y > 0` with `y = 0` on Oracle, whose `NUMBER` does not overflow at 32 bits;
   - `abs(y) > 0` with `y` at the minimum `BIGINT` on SQLite, where `+` switches to floating point.
   
   The images and credentials are those of `connector/docker-integration-tests`.
   
   | Query | PostgreSQL 18.2 | MySQL 9.6.0 | MariaDB 12.2.2 | SQL Server 2022 | Oracle Free 23.26 | DB2 11.5.9 | H2 2.3.232 | Derby 10.16 | SQLite 3.46 |
   |---|---|---|---|---|---|---|---|---|---|
   | `WHERE NOT (a > 0 AND b > 0) AND <E>` | skip | skip | skip | **raise** | skip | **raise** | **raise** | skip | skip |
   | `WHERE <E> AND NOT (a > 0 AND b > 0)` | raise | raise | raise | raise | **skip** | raise | raise | **skip** | raise |
   | `WHERE a > 0 AND <E>` | skip | skip | skip | skip | skip | **raise** | **raise** | skip | skip |
   | `WHERE b < 0 AND <E>` (FALSE first) | - | - | - | - | - | raise | - | - | - |
   | `count(a) FILTER (WHERE NOT (a > 0 AND b > 0) AND <E>)` | **raise** | n/a | n/a | n/a | **raise** | n/a | **raise** | n/a | skip |
   
   "-" means no error and an empty result; "n/a" means the `FILTER` clause is not supported.
   
   - In WHERE, most engines drop the row at the NULL conjunct. Oracle and Derby do it even when the raising conjunct is written first, so they reorder.
   - DB2 and H2 raise. SQL Server raises for the JIRA's shape but not for a plain `a > 0` first.
   - For the aggregate `FILTER` clause, PostgreSQL, Oracle and H2 evaluate the whole `AND` and raise, as this PR does.
   
   We considered the opposite fix: make the interpreted path stop at the first NULL conjunct too. It matches most engines' WHERE behavior, but it departs from the standard's text and from what Spark already does with codegen off. That path is kept available through the legacy config.
   
   ### Why are the changes needed?
   
   The same query must not raise with whole-stage codegen off and return rows with it on (SPARK-60315). Reproducer (ANSI mode):
   
   ```sql
   CREATE TABLE t USING parquet AS
     SELECT * FROM VALUES (NULL, 1, 2147483647), (0, 1, 0) AS v(a, b, y);
   
   SET spark.sql.codegen.wholeStage=true;
   SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0;
   -- before: 0
   -- after:  [ARITHMETIC_OVERFLOW] overflow. ... SQLSTATE: 22003
   
   SET spark.sql.codegen.wholeStage=false;
   SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0;
   -- before and after: [ARITHMETIC_OVERFLOW] overflow. ... SQLSTATE: 22003
   ```
   
   The same happens in non-ANSI mode with year-month interval arithmetic, and with the aggregate `FILTER` clause.
   
   ### Does this PR introduce _any_ user-facing change?
   
   Yes. With whole-stage codegen on, an error raised by a later conjunct on a row where an earlier conjunct is NULL now fails the query, as it already did with codegen off. A row can still be skipped before the filter, for example by Parquet row-group statistics, and then no error is raised.
   
   The later conjuncts also run on more rows than before:
   - a nondeterministic conjunct such as `rand(seed)` can return different values on the remaining rows;
   - a side-effecting UDF is called on rows that are then dropped;
   - an expensive later conjunct costs more on rows where an earlier, nullable conjunct is NULL.
   
   Rows whose earlier conjunct is FALSE are not affected. Setting `spark.sql.legacy.filterShortCircuitOnNullConjunct=true` restores the previous behavior. The migration guide has an entry for this.
   
   ### How was this patch tested?
   
   Four new tests in `WholeStageCodegenSuite`. Each runs with whole-stage codegen on and off, and with the legacy config on and off; it expects the row to be skipped only with codegen on and the legacy config set, and the error in every other combination:
   
   - the ANSI reproducer, and a variant with a common `y + 1` that takes the CSE path of `FilterExec` (checked from the generated code, with `subexpressionElimination.filterExec.enabled` on and off);
   - the non-ANSI year-month interval reproducer (`INTERVAL_ARITHMETIC_OVERFLOW`);
   - a NULL conjunct followed by a FALSE one, which must not raise; it also checks that no flag is generated when no conjunct can be NULL;
   - the aggregate `FILTER` clause.
   
   The tests use a Parquet table with filter pushdown off, so the filter is neither evaluated on a local relation by the optimizer nor skipped by row-group statistics.
   
   Also ran `WholeStageCodegenSuite`, `SQLQuerySuite`, `DataFrameSuite`, `DataFrameAggregateSuite` and `SQLQueryTestSuite` locally. The only failure is `udtf/udtf.sql - Python UDTFs`, whose golden output differs from the local Python's `ValueError` message (`'input' is not in list` vs `list.index(x): x not in list`), which is unrelated to this change.
   
   ### Was this patch authored or co-authored using generative AI tooling?
   
   Generated-by: Claude Code (Claude Opus 5.5)
   
   🤖 Generated with [Claude Code](https://claude.com/claude-code)
   


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   Merging to master/4.x. Thank you, @cloud-fan @dongjoon-hyun @LuciferYang @uros-b @HyukjinKwon @SEPURI-SAI-KRISHNA for review.


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -148,6 +148,52 @@ trait GeneratePredicateHelper extends PredicateHelper {
       nonNullAttrExprIds)
   }
 
+  /**
+   * Returns a fresh name for the flag that records a conjunct evaluated to NULL, or None if the
+   * row is dropped at the first NULL conjunct (the legacy behavior) or none of `conjuncts`, bound
+   * with the nullability they are generated with, can be NULL.
+   *
+   * A row is kept only when the whole condition is TRUE, so both FALSE and NULL drop it. Only
+   * a FALSE conjunct decides the result of the AND on its own, though: after a NULL one, the
+   * remaining conjuncts must still be evaluated, as in `And.eval`, so that an error they raise
+   * on the row is raised (SPARK-60315). The flag drops the row once all of them are evaluated.
+   */
+  protected def nullConjunctFlag(
+      ctx: CodegenContext,
+      conjuncts: Seq[Expression]): Option[String] = {
+    if (conf.legacyFilterShortCircuitOnNullConjunct || !conjuncts.exists(_.nullable)) {
+      None
+    } else {
+      Some(ctx.freshName("nullConjunct"))
+    }
+  }
+
+  /**
+   * Generates the check that drops the row when the conjunct evaluated into `ev` is FALSE, or
+   * when it is NULL and `nullFlag` is None. With `nullFlag`, a NULL conjunct sets the flag.
+   */
+  protected def conjunctCheck(ev: ExprCode, nullable: Boolean, nullFlag: Option[String]): String = {
+    (nullable, nullFlag) match {
+      case (false, _) => s"if (!${ev.value}) continue;"
+      case (true, None) => s"if (${ev.isNull} || !${ev.value}) continue;"
+      case (true, Some(flag)) =>
+        s"if (${ev.isNull}) { $flag = true; } else if (!${ev.value}) continue;"

Review Comment:
   Good catch, thanks. I reproduced it on 69318f6 with a table `(a, b, y, x) = (NULL, 1, 2147483647, NULL), (0, 1, 0, 1)` and ANSI on:
   
   | condition | codegen on, legacy off | codegen on, legacy on | codegen off |
   |---|---|---|---|
   | `NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x > 0` | ARITHMETIC_OVERFLOW | `[0]` | `[0]` |
   | `x IS NOT NULL AND NOT (a > 0 AND b > 0) AND y + 1 > 0` | ARITHMETIC_OVERFLOW | `[0]` | `[0]` |
   | `NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x IS NOT NULL` | ARITHMETIC_OVERFLOW | `[0]` | ARITHMETIC_OVERFLOW |
   | the guard-first condition in an aggregate `FILTER` | ARITHMETIC_OVERFLOW | `[1]` | `[1]` |
   
   So the first two and the `FILTER` clause are exactly the regression you describe, and the third confirms that a guard written after the overflowing conjunct has to stay there.
   
   One more data point. The lazy placement of the `IsNotNull` checks already diverges from the interpreted path without any NULL conjunct:
   
   ```sql
   SELECT a FROM t WHERE x IS NOT NULL AND y + 1 > 0 AND x > 0
   ```
   
   With codegen on this raises ARITHMETIC_OVERFLOW with the legacy flag on and off, i.e. on master too, while codegen off returns `[0]`: `isnotnull(x)` is generated just before `x > 0`, after `y + 1 > 0`. This PR did not create that class of divergence, it widened it to the shapes that used to stop at the NULL conjunct. I filed SPARK-60329 for it and mention it in the description.
   
   Fixed in 3264b00d897 the way you suggest. `splitNotNullPreds` keeps how many `IsNotNull` checks precede each other conjunct in the condition, and once a conjunct that can be NULL has been generated, `precedingNotNullChecks` returns the pending checks that precede the next conjunct, which are generated before it, ahead of its column loads and CSE precomputes, in both `generatePredicateCode` and the CSE branch. The conjuncts before the first one that can be NULL are never reached after a NULL conjunct, so the checks before them keep the lazy placement, and the generated code is unchanged with the legacy flag or when no conjunct can be NULL.
   
   One difference from your sketch: the checks on expressions, such as `isnotnull(cast(s AS INT))`, are placed the same way, not only the plain-column ones. At its own position in the condition the check runs on a subset of the rows the interpreted path evaluates it on (the lazily placed checks can only drop a row earlier), so its inner expression cannot raise where codegen off does not. Leaving it lazy keeps the inconsistency you describe for that shape, and the migration guide's "in the order they appear" would be false for it.
   
   Tests added for the inferred check, a user-written check first, a user-written check after the overflowing conjunct, a check on an expression, a NULL conjunct from `OR`, and the aggregate `FILTER` clause, in the non-CSE and CSE paths. A note on the CSE test: its raising conjunct is not the common subexpression, because the interpreted predicate evaluates its common subexpressions up front, which is a separate pre-existing difference.
   



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,144 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y), (0, 1, 0) AS v(a, b, y)")
+      f
+    }
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+      val cseQuery = "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(cseQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          cseQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkError(
+                  exception = intercept[SparkArithmeticException] {
+                    sql(query).collect()
+                  },
+                  condition = "ARITHMETIC_OVERFLOW",
+                  parameters = Map(
+                    "message" -> "overflow",
+                    "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+                    "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+                  context = ExpectedContext(
+                    fragment = "y + 1",
+                    start = query.indexOf("y + 1"),
+                    stop = query.indexOf("y + 1") + 4))
+              }
+          }
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val query = "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))

Review Comment:
   Right, `cseQuery` only ran on the `Int.MaxValue` table, so the CSE branch's trailing `if (flag) continue;` was never checked at the result level. In 3264b00d897 the same query runs against `withNullConjunctTable(0)` with `filterExec` CSE on and is checked to return `Row(0)` in every `withNullConjunctConfs` combination. The data does not change the plan, so the existing `addExact` assertion still shows that the CSE path is taken.
   



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -156,6 +202,10 @@ trait GeneratePredicateHelper extends PredicateHelper {
       notNullPreds: Seq[Expression],
       otherPreds: Seq[Expression],
       nonNullAttrExprIds: Seq[ExprId]): String = {
+    // The IsNotNull checks are never NULL, so only `otherPreds` can need the flag.
+    val nullFlag =
+      nullConjunctFlag(ctx, otherPreds.map(BindReferences.bindReference(_, outputAttrs)))

Review Comment:
   Agreed, the argument was evaluated before the legacy check in `nullConjunctFlag`, so the bind ran in legacy mode as well. In 3264b00d897 `otherPreds` are bound once against `outputAttrs` and the bound expressions are reused when generating the bodies, as the CSE branch already does with `boundOtherPreds`. The `IsNotNull` checks bind against `inputAttrs`, so they stay as they are.
   



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   Thanks for the careful read and for checking the outer join case. You are right about that sentence: the legacy config only restores the previous whole-stage codegen behavior, and codegen off still raises. I reworded it in the PR description, which the merge script uses as the commit message, so the commit will carry the corrected version.
   


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   Thanks for the detailed write-up and the cross-engine comparison. The stop rule now matches And.eval, but I think the flag interacts badly with the deferred IsNotNull checks. Both codegen branches emit an IsNotNull just before the first conjunct that references its column, or after all conjuncts. With codegen off, the condition is evaluated in order, and the IsNotNull predicates that InferFiltersFromConstraints prepends come first. Take `WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x > 0` on a row with a NULL, b = 1, y = Int.MaxValue, x NULL. Codegen off drops the row at isnotnull(x). With this PR, codegen sets the flag, evaluates y + 1, and fails with ARITHMETIC_OVERFLOW. Before the PR, both modes returned without an error. A user-written guard written first has the same problem: in `WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND y + 1 > 0`, isnotnull(x) is emitted after all the other conjuncts. One way to fix it: once a flag is in play, emit each pending attribute-only IsNot
 Null check just before the first later conjunct it precedes in the condition. A check written after the overflowing conjunct, as in `... AND y + 1 > 0 AND x IS NOT NULL`, then stays where it is, and that query still raises in both modes. Hoisting all pending checks right after the NULL conjunct would drop that row with codegen on and bring back the original inconsistency for that shape. What do you think?
   
   <!-- SPARK_DEV_REVIEW_REPLY {"feedback_id":"conversation:6233128726","thread_id":"conversation:6233128726","verdict_sha256":"8416652c1630a56960b29dad840c9dd175c381d6716e5c914f06da2391389792"} -->


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
docs/sql-migration-guide.md:
##########
@@ -51,6 +51,7 @@ license: |
 - Since Spark 4.4, the value columns of `UNPIVOT` and `Dataset.unpivot` merge the nested nullability (array element, map value, and struct field nullability) of all unpivoted values. Previously, a value column took the nested nullability of the first listed value, so a `NULL` array element, map value, or struct field of a later value could be returned as a non-`NULL` value such as `0`, and `IS NULL` on it returned `false`. A view created by an earlier Spark version over such an `UNPIVOT` may have recorded the non-nullable nested type in its schema and can fail to read; recreate it with `CREATE OR REPLACE VIEW`. To restore the previous behavior, set `spark.sql.legacy.unpivot.useFirstValueNestedNullability` to `true`.
 - Since Spark 4.4, when `spark.sql.parquet.fieldId.read.enabled` is true, a Parquet read fails with `PARQUET_FIELD_REQUESTED_MORE_THAN_ONCE` if two or more requested fields of a struct (or of the top-level schema) resolve to the same Parquet field. This happens when one requested field is matched by field ID and another by name, or when two requested fields have the same field ID. In Spark 4.3 and earlier, such a read returned wrong values without an error: with the vectorized reader one of the fields read as non-null default values (for example `0`) or uninitialized data, and with the parquet-mr reader (`spark.sql.parquet.enableVectorizedReader` set to `false`) one of the fields read as `null`. To read the Parquet field more than once, request it once and refer to the resulting column more than once in the query.
 - Since Spark 4.4, when `spark.sql.groupByOrdinal` is true (the default), the pipe `AGGREGATE` operator rejects an aliased integer literal in its `GROUP BY` clause, such as `TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp`, with `UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE`, because a bare integer literal in the same position is an ordinal. Previously the aliased literal was grouped as a constant. Bare ordinals and aliases on other grouping expressions are not affected. To restore the previous behavior, set `spark.sql.legacy.allowAliasedPipeAggregateOrdinals` to `true`.
+- Since Spark 4.4, with whole-stage codegen enabled, a filter condition, including the `FILTER` clause of an aggregate function, evaluates the remaining top-level conjuncts of the condition on a row after one of them evaluates to `NULL`, as the SQL standard requires, and does so in the order they appear in the condition, as Spark already did with whole-stage codegen disabled. Only a `FALSE` conjunct stops the evaluation. A row whose condition is `NULL` is still dropped, but an error raised by a later conjunct on that row, such as `ARITHMETIC_OVERFLOW` in ANSI mode, now fails the query. For example, `SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0` can fail with `ARITHMETIC_OVERFLOW` on a row where `a` is `NULL`, `b` is `1`, and `y` is `2147483647`, unless the row is dropped earlier by a `FALSE` conjunct or `IS NOT NULL` check that precedes `y + 1 > 0` in the condition, or skipped before the filter, for example by data source filter pushdown such as Parquet row group statis
 tics. Previously the query skipped such a row with whole-stage codegen enabled. The later conjuncts also run on more rows than before, so a nondeterministic conjunct such as `rand(seed)` can return different values on the remaining rows, and a side-effecting user-defined function is called on rows that are dropped. To restore the previous behavior, set `spark.sql.legacy.filterShortCircuitOnNullConjunct` to `true`.

Review Comment:
   **Nit (P3):** This says that after a NULL conjunct the remaining conjuncts are evaluated "in the order they appear in the condition", and that only a `FALSE` conjunct or an `IS NOT NULL` check that precedes the raising conjunct can drop the row first. The `precedingNotNullChecks` scaladoc in `basicPhysicalOperators.scala` makes the same claim. However, both emitters still generate `IsNotNull(x)` just before the first conjunct that references `x`, even when the check is written after that conjunct, because that conjunct is bound with `x` non-nullable.
   
   For example, with ANSI on and the row `(a, b, y, x) = (NULL, 1, 2147483647, NULL)`:
   
   ```sql
   SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND (y + 1 > 0 OR x > 0) AND x IS NOT NULL
   ```
   
   Nothing is inferred from the `OR`, so the user-written check is the only one. With whole-stage codegen on, `isnotnull(x)` runs before the `OR` and drops the row. With it off, `And` evaluates the `OR` after the NULL conjunct and fails with `ARITHMETIC_OVERFLOW`. Master also drops this row, at the NULL conjunct, so the runtime result is not new. But the guide now promises a parity that the code does not provide for this shape. Could the entry and the scaladoc list it as an exception: a check on a column that the raising conjunct references runs before that conjunct, even when it is written after it?
   
   This extends an [existing discussion](https://github.com/apache/spark/pull/59573#discussion_r4309696444).
   
   **Verification:**
   - **Inspection:** The migration-guide entry and the scaladoc each state that, after a NULL conjunct, an IS NOT NULL check on a column that a later conjunct references may run before that conjunct. Neither promises evaluation strictly in the order of the condition.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -137,47 +137,146 @@ trait GeneratePredicateHelper extends PredicateHelper {
       condition: Expression,
       inputAttrs: Seq[Attribute],
       inputExprCode: Seq[ExprCode]): String = {
-    val (notNullPreds, otherPreds) = splitConjunctivePredicates(condition).partition {
-      case IsNotNull(a) => isNullIntolerant(a) && a.references.subsetOf(AttributeSet(inputAttrs))
-      case _ => false
-    }
+    val (notNullPreds, otherPreds, precedingNotNulls) =
+      splitNotNullPreds(condition, AttributeSet(inputAttrs))
     val nonNullAttrExprIds = notNullPreds.flatMap(_.references).distinct.map(_.exprId)
     val outputAttrs = outputWithNullability(inputAttrs, nonNullAttrExprIds)
     generatePredicateCode(
       ctx, inputAttrs, inputExprCode, outputAttrs, notNullPreds, otherPreds,
+      otherPreds.map(BindReferences.bindReference(_, outputAttrs)), precedingNotNulls,
       nonNullAttrExprIds)
   }
 
+  /**
+   * Splits `condition` into the `IsNotNull` checks on null-intolerant expressions over
+   * `inputAttrs`, the other conjuncts, and for each of the other conjuncts the number of the
+   * checks that precede it in `condition`. The conjuncts keep their order within each group, so
+   * the checks before the k-th other conjunct are the first `precedingNotNulls(k)` checks.
+   */
+  protected def splitNotNullPreds(
+      condition: Expression,
+      inputAttrs: AttributeSet): (Seq[Expression], Seq[Expression], Seq[Int]) = {
+    val notNullPreds = mutable.ArrayBuffer.empty[Expression]
+    val otherPreds = mutable.ArrayBuffer.empty[Expression]
+    val precedingNotNulls = mutable.ArrayBuffer.empty[Int]
+    splitConjunctivePredicates(condition).foreach {
+      case p @ IsNotNull(a) if isNullIntolerant(a) && a.references.subsetOf(inputAttrs) =>
+        notNullPreds += p
+      case p =>
+        otherPreds += p
+        precedingNotNulls += notNullPreds.length
+    }
+    (notNullPreds.toSeq, otherPreds.toSeq, precedingNotNulls.toSeq)
+  }
+
+  /**
+   * Returns a fresh name for the flag that records a conjunct evaluated to NULL, or None if the
+   * row is dropped at the first NULL conjunct (the legacy behavior) or none of `conjuncts`, bound
+   * with the nullability they are generated with, can be NULL.
+   *
+   * A row is kept only when the whole condition is TRUE, so both FALSE and NULL drop it. Only
+   * a FALSE conjunct decides the result of the AND on its own, though: after a NULL one, the
+   * remaining conjuncts must still be evaluated, as in `And.eval`, so that an error they raise
+   * on the row is raised (SPARK-60315). The flag drops the row once all of them are evaluated.
+   */
+  protected def nullConjunctFlag(
+      ctx: CodegenContext,
+      conjuncts: Seq[Expression]): Option[String] = {
+    if (conf.legacyFilterShortCircuitOnNullConjunct || !conjuncts.exists(_.nullable)) {
+      None
+    } else {
+      Some(ctx.freshName("nullConjunct"))
+    }
+  }
+
+  /**
+   * Generates the check that drops the row when the conjunct evaluated into `ev` is FALSE, or
+   * when it is NULL and `nullFlag` is None. With `nullFlag`, a NULL conjunct sets the flag.
+   */
+  protected def conjunctCheck(ev: ExprCode, nullable: Boolean, nullFlag: Option[String]): String = {
+    (nullable, nullFlag) match {
+      case (false, _) => s"if (!${ev.value}) continue;"
+      case (true, None) => s"if (${ev.isNull} || !${ev.value}) continue;"
+      case (true, Some(flag)) =>
+        s"if (${ev.isNull}) { $flag = true; } else if (!${ev.value}) continue;"
+    }
+  }
+
+  /** Wraps the checks of all conjuncts in `code` with the declaration and test of `nullFlag`. */
+  protected def withNullConjunctFlag(code: String, nullFlag: Option[String]): String = {
+    nullFlag match {
+      case Some(flag) =>
+        s"""
+           |boolean $flag = false;
+           |$code
+           |if ($flag) continue;
+         """.stripMargin
+      case None => code
+    }
+  }
+
+  /**
+   * Returns the checks of `notNullPreds` that precede the k-th other conjunct in the condition
+   * and are not generated yet, and marks them generated, when `afterNullable` is set, or Nil.
+   *
+   * With the NULL-conjunct flag, a NULL conjunct no longer drops the row, so the conjuncts after
+   * it are evaluated in the order of the condition, as `And.eval` does: a FALSE IsNotNull check
+   * drops the row before an error a later conjunct raises on it (SPARK-60315). `afterNullable`
+   * is set once a conjunct that can be NULL has been generated. The conjuncts before it are
+   * never reached after a NULL conjunct, so the checks before them keep the lazy placement.
+   */
+  protected def precedingNotNullChecks(
+      notNullPreds: Seq[Expression],
+      precedingNotNulls: Seq[Int],
+      k: Int,
+      afterNullable: Boolean,
+      generated: Array[Boolean]): Seq[Expression] = {
+    if (afterNullable) {
+      notNullPreds.take(precedingNotNulls(k)).zipWithIndex.collect {
+        case (p, idx) if !generated(idx) =>
+          generated(idx) = true
+          p
+      }
+    } else {
+      Nil
+    }
+  }
+
   protected def generatePredicateCode(
       ctx: CodegenContext,
       inputAttrs: Seq[Attribute],
       inputExprCode: Seq[ExprCode],
       outputAttrs: Seq[Attribute],
       notNullPreds: Seq[Expression],
       otherPreds: Seq[Expression],
+      boundOtherPreds: Seq[Expression],

Review Comment:
   **Nit (P3):** With `boundOtherPreds` passed in, nothing in this method reads `outputAttrs` (the parameter above) anymore. `genPredicate` takes the bound expression, and `genNotNull` binds against `inputAttrs`. Both callers, the 4-argument overload and `FilterExec.doConsume`, still compute and pass it, so the signature suggests a binding the method no longer does. Could you drop `outputAttrs` here and at both call sites, and change the comment below to say that callers bind `boundOtherPreds` against the tightened output?
   
   **Verification:**
   - **Compile:** The SQL core module compiles with the parameter removed from the shared helper and from both of its callers.
   - **Compatibility:** Filter and aggregate FILTER results and errors are unchanged across whole-stage codegen on and off, the legacy config on and off, and the filterExec CSE path on and off.



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,219 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y, NULL, 'x'), (0, 1, 0, 1, '1') AS v(a, b, y, x, s)")
+      f
+    }
+  }
+
+  // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+  private val nullConjunctCSEQuery =
+    "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+
+  private def checkArithmeticOverflow(query: String): Unit = {
+    checkError(
+      exception = intercept[SparkArithmeticException] {
+        sql(query).collect()
+      },
+      condition = "ARITHMETIC_OVERFLOW",
+      parameters = Map(
+        "message" -> "overflow",
+        "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+        "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+      context = ExpectedContext(
+        fragment = "y + 1",
+        start = query.indexOf("y + 1"),
+        stop = query.indexOf("y + 1") + 4))
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(nullConjunctCSEQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          nullConjunctCSEQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkArithmeticOverflow(query)
+              }
+          }
+        }
+      }
+    }
+    // A NULL conjunct from OR. The second row does not pass it.
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT a FROM t WHERE (a > 0 OR b < 0) AND y + 1 > 0"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Seq.empty)
+        } else {
+          checkArithmeticOverflow(query)
+        }
+      }
+    }
+    // Without an error, the CSE path drops the first row after its last conjunct is evaluated.
+    withNullConjunctTable(0) {
+      withNullConjunctConfs(
+          SQLConf.ANSI_ENABLED.key -> "true",
+          SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> "true") { _ =>
+        checkAnswer(sql(nullConjunctCSEQuery), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val condition = "NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      val query = s"SELECT a FROM t WHERE $condition"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+        // A check on an expression, not on a column, that precedes the NULL conjunct:
+        // `cast(s AS INT)` is NULL on the first row, so the check is FALSE and drops the row
+        // before the overflowing conjunct, as with codegen off.
+        checkAnswer(
+          sql(s"SELECT a FROM t WHERE cast(s AS INT) IS NOT NULL AND $condition"), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))
+        // FALSE decides the AND, so the overflowing conjunct after it is not evaluated.
+        checkAnswer(
+          sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND b < 0 AND y + 1 > 0"),
+          Seq.empty)
+      }
+      // `b > 0` cannot be NULL after the inferred `isnotnull(b)`, so no flag is generated.
+      val df = sql("SELECT a FROM t WHERE b > 0 AND y > -1")
+      checkAnswer(df, Seq(Row(null), Row(0)))
+      assert(!codegenString(df.queryExecution.executedPlan).contains("nullConjunct"))
+    }
+  }
+
+  test("SPARK-60315: a filter checks IS NOT NULL before the conjuncts it precedes") {
+    withNullConjunctTable(Int.MaxValue) {
+      // `x` is NULL on the first row. With codegen off, a FALSE `isnotnull(x)` drops the row
+      // before a conjunct after it in the condition is evaluated. With codegen on, the check is
+      // generated just before the first conjunct that references `x`, or after all of them, so
+      // after a NULL conjunct it has to be generated before the conjuncts it precedes.
+      //
+      // The common `b * 2` takes the CSE path of FilterExec. The overflowing `y + 1` is not
+      // common because the interpreted predicate evaluates its common subexpressions up front.

Review Comment:
   **Nit (P3):** `InterpretedPredicate` evaluates common subexpressions lazily, through `ExpressionProxy` and the `SubExprEvaluationRuntime` cache. The predicate that evaluates them up front is `GeneratePredicate`, which runs `subexprFunctionsCode` before the condition. That is what `FilterExec` gets from `Predicate.create` with whole-stage codegen off, under the default factory mode.
   
   ```suggestion
         // common because `GeneratePredicate` (codegen off) evaluates common subexpressions first.
   ```
   
   **Verification:**
   - **Inspection:** The comment that explains why the overflowing addition is not a common subexpression attributes the up-front evaluation to the generated predicate used when whole-stage codegen is off, not to the interpreted predicate.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -148,6 +148,52 @@ trait GeneratePredicateHelper extends PredicateHelper {
       nonNullAttrExprIds)
   }
 
+  /**
+   * Returns a fresh name for the flag that records a conjunct evaluated to NULL, or None if the
+   * row is dropped at the first NULL conjunct (the legacy behavior) or none of `conjuncts`, bound
+   * with the nullability they are generated with, can be NULL.
+   *
+   * A row is kept only when the whole condition is TRUE, so both FALSE and NULL drop it. Only
+   * a FALSE conjunct decides the result of the AND on its own, though: after a NULL one, the
+   * remaining conjuncts must still be evaluated, as in `And.eval`, so that an error they raise
+   * on the row is raised (SPARK-60315). The flag drops the row once all of them are evaluated.
+   */
+  protected def nullConjunctFlag(
+      ctx: CodegenContext,
+      conjuncts: Seq[Expression]): Option[String] = {
+    if (conf.legacyFilterShortCircuitOnNullConjunct || !conjuncts.exists(_.nullable)) {
+      None
+    } else {
+      Some(ctx.freshName("nullConjunct"))
+    }
+  }
+
+  /**
+   * Generates the check that drops the row when the conjunct evaluated into `ev` is FALSE, or
+   * when it is NULL and `nullFlag` is None. With `nullFlag`, a NULL conjunct sets the flag.
+   */
+  protected def conjunctCheck(ev: ExprCode, nullable: Boolean, nullFlag: Option[String]): String = {
+    (nullable, nullFlag) match {
+      case (false, _) => s"if (!${ev.value}) continue;"
+      case (true, None) => s"if (${ev.isNull} || !${ev.value}) continue;"
+      case (true, Some(flag)) =>
+        s"if (${ev.isNull}) { $flag = true; } else if (!${ev.value}) continue;"

Review Comment:
   Thanks, I checked 3264b00d897. After a nullable conjunct, the checks written before each later conjunct now run before it in both emitters. The inferred, guard-first, and aggregate FILTER cases drop the row, and a guard written after the overflowing conjunct still raises. Placing expression checks the same way makes sense: at their own position they run only on rows codegen off also evaluates them on. One remaining wording point: the migration guide and the precedingNotNullChecks scaladoc still promise condition-order evaluation, but a check written after a conjunct that references its column is still generated before that conjunct. See the review comment on docs/sql-migration-guide.md.
   
   <!-- SPARK_DEV_REVIEW_REPLY {"feedback_id":"inline:4309696444","thread_id":"inline:4309696444","verdict_sha256":"c6775db45c1326a69ec4232e7dc0b7dcfc9209f4dde7e4ed49ed9b2788d14034"} -->



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   The current source confirms this interaction: both generators defer plain-column IsNotNull checks while NULL now falls through. Preserving each guard's original condition position when a NULL flag is active would address the reported new codegen-only failure; a guard written after the overflowing conjunct must stay after it. Both the shared aggregate helper path and FilterExec CSE branch need the same treatment.
   
   <!-- SPARK_DEV_REVIEW_REPLY {"feedback_id":"conversation:6257890434","thread_id":"conversation:6257890434","verdict_sha256":"c67c9688cb3705344ad50d53860217547c98a4a83f37ac47569ed33e4f63866d"} -->


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -186,13 +290,24 @@ trait GeneratePredicateHelper extends PredicateHelper {
     // This has the property of not doing redundant IsNotNull checks and taking better advantage of
     // short-circuiting, not loading attributes until they are needed.
     // This is very perf sensitive.
+    // With the NULL-conjunct flag, once a predicate that can be NULL is generated, the IsNotNull
+    // checks that precede a later predicate in the condition are generated before it, see
+    // `precedingNotNullChecks`, and a check on an expression that is written after the predicate
+    // is not generated before it, see the nested checks below.
     // NOTE: `FilterExec.doConsume`'s CSE branch inlines the same interleaving so it can
     // emit CSE state precomputes between the IsNotNull checks and each otherPred body.
     // Any change to this algorithm must be mirrored there (and vice versa).
     // TODO: revisit this. We can consider reordering predicates as well.
     val generatedIsNotNullChecks = new Array[Boolean](notNullPreds.length)
     val extraIsNotNullAttrs = mutable.Set[Attribute]()
-    val generated = otherPreds.map { c =>
+    var nullableSeen = false
+    val generated = otherPreds.zip(boundOtherPreds).zipWithIndex.map { case ((c, bound), k) =>
+      val afterNullable = nullFlag.isDefined && nullableSeen

Review Comment:
   Right, both `afterNullable` and the nested skip are decided at codegen time, so once a conjunct can be NULL, the checks after it are placed in the order of the condition for every row, and a row with no NULL conjunct can change too, in both directions, always toward the codegen-off result. In f81f1c81082 the migration guide and the legacy config doc say so, the "Rows whose earlier conjunct is FALSE are not affected" sentence is replaced in the PR description, and your first query is pinned in the expression-check test with `CAST_INVALID_INPUT` expected unless the legacy config skips the row. The SPARK-60377 example in the description is now `WHERE coalesce(b, cast(s AS INT)) < 0 AND cast(s AS INT) IS NOT NULL` on `(b, s) = (1, 'x')`, and the JIRA is updated the same way.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -137,47 +137,151 @@ trait GeneratePredicateHelper extends PredicateHelper {
       condition: Expression,
       inputAttrs: Seq[Attribute],
       inputExprCode: Seq[ExprCode]): String = {
-    val (notNullPreds, otherPreds) = splitConjunctivePredicates(condition).partition {
-      case IsNotNull(a) => isNullIntolerant(a) && a.references.subsetOf(AttributeSet(inputAttrs))
-      case _ => false
-    }
+    val (notNullPreds, otherPreds, precedingNotNulls) =
+      splitNotNullPreds(condition, AttributeSet(inputAttrs))
     val nonNullAttrExprIds = notNullPreds.flatMap(_.references).distinct.map(_.exprId)
     val outputAttrs = outputWithNullability(inputAttrs, nonNullAttrExprIds)
     generatePredicateCode(
-      ctx, inputAttrs, inputExprCode, outputAttrs, notNullPreds, otherPreds,
+      ctx, inputAttrs, inputExprCode, notNullPreds, otherPreds,
+      otherPreds.map(BindReferences.bindReference(_, outputAttrs)), precedingNotNulls,
       nonNullAttrExprIds)
   }
 
+  /**
+   * Splits `condition` into the `IsNotNull` checks on null-intolerant expressions over
+   * `inputAttrs`, the other conjuncts, and for each of the other conjuncts the number of the
+   * checks that precede it in `condition`. The conjuncts keep their order within each group, so
+   * the checks before the k-th other conjunct are the first `precedingNotNulls(k)` checks.
+   */
+  protected def splitNotNullPreds(
+      condition: Expression,
+      inputAttrs: AttributeSet): (Seq[Expression], Seq[Expression], Seq[Int]) = {
+    val notNullPreds = mutable.ArrayBuffer.empty[Expression]
+    val otherPreds = mutable.ArrayBuffer.empty[Expression]
+    val precedingNotNulls = mutable.ArrayBuffer.empty[Int]
+    splitConjunctivePredicates(condition).foreach {
+      case p @ IsNotNull(a) if isNullIntolerant(a) && a.references.subsetOf(inputAttrs) =>
+        notNullPreds += p
+      case p =>
+        otherPreds += p
+        precedingNotNulls += notNullPreds.length
+    }
+    (notNullPreds.toSeq, otherPreds.toSeq, precedingNotNulls.toSeq)
+  }
+
+  /**
+   * Returns a fresh name for the flag that records a conjunct evaluated to NULL, or None if the
+   * row is dropped at the first NULL conjunct (the legacy behavior) or none of `conjuncts`, bound
+   * with the nullability they are generated with, can be NULL.
+   *
+   * A row is kept only when the whole condition is TRUE, so both FALSE and NULL drop it. Only
+   * a FALSE conjunct decides the result of the AND on its own, though: after a NULL one, the
+   * remaining conjuncts must still be evaluated, as in `And.eval`, so that an error they raise
+   * on the row is raised (SPARK-60315). The flag drops the row once all of them are evaluated.
+   */
+  protected def nullConjunctFlag(
+      ctx: CodegenContext,
+      conjuncts: Seq[Expression]): Option[String] = {
+    if (conf.legacyFilterShortCircuitOnNullConjunct || !conjuncts.exists(_.nullable)) {

Review Comment:
   Good point, the hoisted checks decode their columns on every row that reaches the next conjunct, and common `OR`/`IN` filters pay for it although nothing after them can raise. In f81f1c81082 `nullConjunctFlag` returns `None` unless a conjunct after the first nullable one, or one of the `IsNotNull` checks, fails `ExprUtils.canEvaluateUnconditionally` on the unbound expression, and `precedingNotNullChecks` generates the pending checks before the k-th conjunct only when that conjunct or one of them fails it. Since the helper returns false for nondeterministic expressions and UDFs, the user-facing note about `rand` and side-effecting UDFs still holds, and your `(a > 5 OR b > 5) AND c = 1 AND d = 2` shape generates master's code again. The "stops at a FALSE one" test now checks that the flag is absent for `(a > 0 OR b > 0) AND x = 1 AND y = 0` and present with `y + 1 > 0` instead.



##########
docs/sql-migration-guide.md:
##########
@@ -51,6 +51,7 @@ license: |
 - Since Spark 4.4, the value columns of `UNPIVOT` and `Dataset.unpivot` merge the nested nullability (array element, map value, and struct field nullability) of all unpivoted values. Previously, a value column took the nested nullability of the first listed value, so a `NULL` array element, map value, or struct field of a later value could be returned as a non-`NULL` value such as `0`, and `IS NULL` on it returned `false`. A view created by an earlier Spark version over such an `UNPIVOT` may have recorded the non-nullable nested type in its schema and can fail to read; recreate it with `CREATE OR REPLACE VIEW`. To restore the previous behavior, set `spark.sql.legacy.unpivot.useFirstValueNestedNullability` to `true`.
 - Since Spark 4.4, when `spark.sql.parquet.fieldId.read.enabled` is true, a Parquet read fails with `PARQUET_FIELD_REQUESTED_MORE_THAN_ONCE` if two or more requested fields of a struct (or of the top-level schema) resolve to the same Parquet field. This happens when one requested field is matched by field ID and another by name, or when two requested fields have the same field ID. In Spark 4.3 and earlier, such a read returned wrong values without an error: with the vectorized reader one of the fields read as non-null default values (for example `0`) or uninitialized data, and with the parquet-mr reader (`spark.sql.parquet.enableVectorizedReader` set to `false`) one of the fields read as `null`. To read the Parquet field more than once, request it once and refer to the resulting column more than once in the query.
 - Since Spark 4.4, when `spark.sql.groupByOrdinal` is true (the default), the pipe `AGGREGATE` operator rejects an aliased integer literal in its `GROUP BY` clause, such as `TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp`, with `UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE`, because a bare integer literal in the same position is an ordinal. Previously the aliased literal was grouped as a constant. Bare ordinals and aliases on other grouping expressions are not affected. To restore the previous behavior, set `spark.sql.legacy.allowAliasedPipeAggregateOrdinals` to `true`.
+- Since Spark 4.4, with whole-stage codegen enabled, a filter condition, including the `FILTER` clause of an aggregate function, evaluates the remaining top-level conjuncts of the condition on a row after one of them evaluates to `NULL`, as the SQL standard requires, and does so in the order they appear in the condition, as Spark already did with whole-stage codegen disabled. Only a `FALSE` conjunct stops the evaluation. The one exception is an `IS NOT NULL` check on a column that a later conjunct references: it is still evaluated before that conjunct, even when it is written after it, and can drop the row before an error the conjunct raises. A row whose condition is `NULL` is still dropped, but an error raised by a later conjunct on that row, such as `ARITHMETIC_OVERFLOW` in ANSI mode, now fails the query. For example, `SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0` can fail with `ARITHMETIC_OVERFLOW` on a row where `a` is `NULL`, `b` is `1`, and `y` is `2147483647`, un
 less the row is dropped earlier by a `FALSE` conjunct or `IS NOT NULL` check that precedes `y + 1 > 0` in the condition, or skipped before the filter, for example by data source filter pushdown such as Parquet row group statistics. Previously the query skipped such a row with whole-stage codegen enabled. The later conjuncts also run on more rows than before, so a nondeterministic conjunct such as `rand(seed)` can return different values on the remaining rows, and a side-effecting user-defined function is called on rows that are dropped. To restore the previous behavior, set `spark.sql.legacy.filterShortCircuitOnNullConjunct` to `true`.

Review Comment:
   Agreed on all three, thanks for the queries. In f81f1c81082 the entry and the `precedingNotNullChecks` scaladoc describe the exception as: a check on a column, or on an expression over it, is evaluated before the first later conjunct that references the column and before every conjunct up to the check's own position, so it can drop the row before an error raised by any of them, and the first conjunct that can be NULL keeps the checks on the expressions it contains. The SPARK-60377 bullet in the description now says "before the first conjunct that can be NULL, including that conjunct itself, or with the legacy config" instead of "Without a NULL conjunct", and case 3 is in that JIRA's description.



##########
docs/sql-migration-guide.md:
##########
@@ -51,6 +51,7 @@ license: |
 - Since Spark 4.4, the value columns of `UNPIVOT` and `Dataset.unpivot` merge the nested nullability (array element, map value, and struct field nullability) of all unpivoted values. Previously, a value column took the nested nullability of the first listed value, so a `NULL` array element, map value, or struct field of a later value could be returned as a non-`NULL` value such as `0`, and `IS NULL` on it returned `false`. A view created by an earlier Spark version over such an `UNPIVOT` may have recorded the non-nullable nested type in its schema and can fail to read; recreate it with `CREATE OR REPLACE VIEW`. To restore the previous behavior, set `spark.sql.legacy.unpivot.useFirstValueNestedNullability` to `true`.
 - Since Spark 4.4, when `spark.sql.parquet.fieldId.read.enabled` is true, a Parquet read fails with `PARQUET_FIELD_REQUESTED_MORE_THAN_ONCE` if two or more requested fields of a struct (or of the top-level schema) resolve to the same Parquet field. This happens when one requested field is matched by field ID and another by name, or when two requested fields have the same field ID. In Spark 4.3 and earlier, such a read returned wrong values without an error: with the vectorized reader one of the fields read as non-null default values (for example `0`) or uninitialized data, and with the parquet-mr reader (`spark.sql.parquet.enableVectorizedReader` set to `false`) one of the fields read as `null`. To read the Parquet field more than once, request it once and refer to the resulting column more than once in the query.
 - Since Spark 4.4, when `spark.sql.groupByOrdinal` is true (the default), the pipe `AGGREGATE` operator rejects an aliased integer literal in its `GROUP BY` clause, such as `TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp`, with `UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE`, because a bare integer literal in the same position is an ordinal. Previously the aliased literal was grouped as a constant. Bare ordinals and aliases on other grouping expressions are not affected. To restore the previous behavior, set `spark.sql.legacy.allowAliasedPipeAggregateOrdinals` to `true`.
+- Since Spark 4.4, with whole-stage codegen enabled, a filter condition, including the `FILTER` clause of an aggregate function, evaluates the remaining top-level conjuncts of the condition on a row after one of them evaluates to `NULL`, as the SQL standard requires, and does so in the order they appear in the condition, as Spark already did with whole-stage codegen disabled. Only a `FALSE` conjunct stops the evaluation. The one exception is an `IS NOT NULL` check on a column that a later conjunct references: it is still evaluated before that conjunct, even when it is written after it, and can drop the row before an error the conjunct raises. A row whose condition is `NULL` is still dropped, but an error raised by a later conjunct on that row, such as `ARITHMETIC_OVERFLOW` in ANSI mode, now fails the query. For example, `SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0` can fail with `ARITHMETIC_OVERFLOW` on a row where `a` is `NULL`, `b` is `1`, and `y` is `2147483647`, un
 less the row is dropped earlier by a `FALSE` conjunct or `IS NOT NULL` check that precedes `y + 1 > 0` in the condition, or skipped before the filter, for example by data source filter pushdown such as Parquet row group statistics. Previously the query skipped such a row with whole-stage codegen enabled. The later conjuncts also run on more rows than before, so a nondeterministic conjunct such as `rand(seed)` can return different values on the remaining rows, and a side-effecting user-defined function is called on rows that are dropped. To restore the previous behavior, set `spark.sql.legacy.filterShortCircuitOnNullConjunct` to `true`.

Review Comment:
   Right, with codegen off `GeneratePredicate` evaluates the common subexpressions before the condition, so a conjunct after a FALSE one can still raise through a subexpression it shares with another conjunct, and the same holds for the generated projection of an aggregate `FILTER`. That is pre-existing and independent of the NULL conjunct, so I filed SPARK-60392 for it and listed it under "Not fixed here". In f81f1c81082 the sentence says "as Spark already did with whole-stage codegen disabled, which also evaluates a subexpression shared by several conjuncts before the condition".



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,274 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y, NULL, 'x'), (0, 1, 0, 1, '1') AS v(a, b, y, x, s)")
+      f
+    }
+  }
+
+  // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+  private val nullConjunctCSEQuery =
+    "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+
+  private def checkArithmeticOverflow(query: String): Unit = {
+    checkError(
+      exception = intercept[SparkArithmeticException] {
+        sql(query).collect()
+      },
+      condition = "ARITHMETIC_OVERFLOW",
+      parameters = Map(
+        "message" -> "overflow",
+        "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+        "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+      context = ExpectedContext(
+        fragment = "y + 1",
+        start = query.indexOf("y + 1"),
+        stop = query.indexOf("y + 1") + 4))
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(nullConjunctCSEQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          nullConjunctCSEQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkArithmeticOverflow(query)
+              }
+          }
+        }
+      }
+    }
+    // A NULL conjunct from OR. The second row does not pass it.
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT a FROM t WHERE (a > 0 OR b < 0) AND y + 1 > 0"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Seq.empty)
+        } else {
+          checkArithmeticOverflow(query)
+        }
+      }
+    }
+    // Without an error, the CSE path drops the first row after its last conjunct is evaluated.
+    withNullConjunctTable(0) {
+      withNullConjunctConfs(
+          SQLConf.ANSI_ENABLED.key -> "true",
+          SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> "true") { _ =>
+        checkAnswer(sql(nullConjunctCSEQuery), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val condition = "NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      val query = s"SELECT a FROM t WHERE $condition"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+        // A check on an expression, not on a column, that precedes the NULL conjunct:
+        // `cast(s AS INT)` is NULL on the first row, so the check is FALSE and drops the row
+        // before the overflowing conjunct, as with codegen off.
+        checkAnswer(
+          sql(s"SELECT a FROM t WHERE cast(s AS INT) IS NOT NULL AND $condition"), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))
+        // FALSE decides the AND, so the overflowing conjunct after it is not evaluated.
+        checkAnswer(
+          sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND b < 0 AND y + 1 > 0"),
+          Seq.empty)
+      }
+      // `b > 0` cannot be NULL after the inferred `isnotnull(b)`, so no flag is generated.
+      val df = sql("SELECT a FROM t WHERE b > 0 AND y > -1")
+      checkAnswer(df, Seq(Row(null), Row(0)))
+      assert(!codegenString(df.queryExecution.executedPlan).contains("nullConjunct"))
+    }
+  }
+
+  test("SPARK-60315: a filter checks IS NOT NULL before the conjuncts it precedes") {
+    withNullConjunctTable(Int.MaxValue) {
+      // `x` is NULL on the first row. With codegen off, a FALSE `isnotnull(x)` drops the row
+      // before a conjunct after it in the condition is evaluated. With codegen on, the check is
+      // generated just before the first conjunct that references `x`, or after all of them, so
+      // after a NULL conjunct it has to be generated before the conjuncts it precedes.
+      //
+      // The common `b * 2` takes the CSE path of FilterExec. The overflowing `y + 1` is not
+      // common because `GeneratePredicate` (codegen off) evaluates common subexpressions first.
+      val cseQuery = "SELECT a FROM t WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND " +
+        "y + 1 > 0 AND b * 2 > 0 AND b * 2 > -1"
+      def multiplyExactCount(filterExecCSE: Boolean): Int = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(cseQuery).queryExecution.executedPlan)
+          "multiplyExact".r.findAllIn(code).length
+        }
+      }
+      assert(multiplyExactCount(filterExecCSE = true) < multiplyExactCount(filterExecCSE = false))
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        withNullConjunctConfs(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+          skipsAtNull =>
+            // `isnotnull(x)` is inferred from `x > 0` and precedes the overflowing conjunct.
+            checkAnswer(
+              sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x > 0"),
+              Row(0))
+            // A user-written check that no other conjunct references.
+            checkAnswer(
+              sql("SELECT a FROM t WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND y + 1 > 0"),
+              Row(0))
+            // The same with a common subexpression.
+            checkAnswer(sql(cseQuery), Row(0))
+            // A check written after the overflowing conjunct stays after it.
+            val checkAfter =
+              "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x IS NOT NULL"

Review Comment:
   Right, only `cseQuery` ran them through the CSE branch. In f81f1c81082 the test also checks `checkAfter + " AND b * 2 > 0 AND b * 2 > -1"` with the same `skipsAtNull` expectation, so the per-conjunct placement with `precedingNotNulls = [2, 2, 3, 3]` is pinned in that branch too.



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -215,42 +323,44 @@ trait GeneratePredicateHelper extends PredicateHelper {
             if !generatedIsNotNullChecks(idx) && !n.isInstanceOf[Attribute] &&

Review Comment:
   This nested branch (from SPARK-51356) generates an `IsNotNull(expr)` on a non-attribute `expr` just before the first conjunct that contains `expr`, even when the check is written after that conjunct. Before this PR, a row with an earlier NULL conjunct never got here. With the flag it does, and `precedingNotNullChecks` only handles checks written before the conjunct.
   
   On `withNullConjunctTable(Int.MaxValue)` with ANSI on and Parquet filter pushdown off, `WHERE NOT (a > 0 AND b > 0) AND coalesce(b, cast(s AS INT)) < 0 AND cast(s AS INT) IS NOT NULL` returns no rows with codegen off and with the legacy config, but raises `CAST_INVALID_INPUT` with codegen on and the legacy config off, although the second conjunct is FALSE.
   
   I tried skipping this branch, here and in the CSE branch, when `afterNullable` is true (`nullableSeen` before `nullableSeen ||= bound.nullable`, so the first conjunct that can be NULL keeps its check). The checks written before the conjunct are generated by then, and the direct or synthetic `IsNotNull(r)` still covers the binding. These queries then match codegen off, and the SPARK-60315 and SPARK-51356 tests pass. Could you make that change, with a test for this shape?



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,312 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`, `(a > 0 OR b < 0)`
+  // and `coalesce(b, cast(s AS INT)) < 0`. The rows can be

Review Comment:
   Right, the first row's file was skipped by its statistics in a multi-core session. Added `SET spark.sql.parquet.filterPushdown=false;` after the CTAS in the PR description, with a one-line comment on why.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -137,47 +137,182 @@ trait GeneratePredicateHelper extends PredicateHelper {
       condition: Expression,
       inputAttrs: Seq[Attribute],
       inputExprCode: Seq[ExprCode]): String = {
-    val (notNullPreds, otherPreds) = splitConjunctivePredicates(condition).partition {
-      case IsNotNull(a) => isNullIntolerant(a) && a.references.subsetOf(AttributeSet(inputAttrs))
-      case _ => false
-    }
+    val (notNullPreds, otherPreds, precedingNotNulls) =
+      splitNotNullPreds(condition, AttributeSet(inputAttrs))
     val nonNullAttrExprIds = notNullPreds.flatMap(_.references).distinct.map(_.exprId)
     val outputAttrs = outputWithNullability(inputAttrs, nonNullAttrExprIds)
     generatePredicateCode(
-      ctx, inputAttrs, inputExprCode, outputAttrs, notNullPreds, otherPreds,
+      ctx, inputAttrs, inputExprCode, notNullPreds, otherPreds,
+      otherPreds.map(BindReferences.bindReference(_, outputAttrs)), precedingNotNulls,
       nonNullAttrExprIds)
   }
 
+  /**
+   * Splits `condition` into the `IsNotNull` checks on null-intolerant expressions over
+   * `inputAttrs`, the other conjuncts, and for each of the other conjuncts the number of the
+   * checks that precede it in `condition`. The conjuncts keep their order within each group, so
+   * the checks before the k-th other conjunct are the first `precedingNotNulls(k)` checks.
+   */
+  protected def splitNotNullPreds(
+      condition: Expression,
+      inputAttrs: AttributeSet): (Seq[Expression], Seq[Expression], Seq[Int]) = {
+    val notNullPreds = mutable.ArrayBuffer.empty[Expression]
+    val otherPreds = mutable.ArrayBuffer.empty[Expression]
+    val precedingNotNulls = mutable.ArrayBuffer.empty[Int]
+    splitConjunctivePredicates(condition).foreach {
+      case p @ IsNotNull(a) if isNullIntolerant(a) && a.references.subsetOf(inputAttrs) =>
+        notNullPreds += p
+      case p =>
+        otherPreds += p
+        precedingNotNulls += notNullPreds.length
+    }
+    (notNullPreds.toSeq, otherPreds.toSeq, precedingNotNulls.toSeq)
+  }
+
+  /**
+   * Whether evaluating `e` on a row can be observed other than through its value: it can raise
+   * an error, is nondeterministic or may have side effects, such as a UDF. Such an expression
+   * has to be evaluated on the rows, and in the order, that `And.eval` evaluates it on.
+   */
+  protected def mayRaise(e: Expression): Boolean = !ExprUtils.canEvaluateUnconditionally(e)

Review Comment:
   Updated the bullet in the PR description: the trait gets six helpers, `splitNotNullPreds`, `mayRaise`, `nullConjunctFlag`, `conjunctCheck`, `withNullConjunctFlag` and `precedingNotNullChecks`, each with a short description.



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

Posted by "dongjoon-hyun (via GitHub)" <gi...@apache.org>.
dongjoon-hyun commented on code in PR #59573:
URL: https://github.com/apache/spark/pull/59573#discussion_r4327369276


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -186,13 +290,24 @@ trait GeneratePredicateHelper extends PredicateHelper {
     // This has the property of not doing redundant IsNotNull checks and taking better advantage of
     // short-circuiting, not loading attributes until they are needed.
     // This is very perf sensitive.
+    // With the NULL-conjunct flag, once a predicate that can be NULL is generated, the IsNotNull
+    // checks that precede a later predicate in the condition are generated before it, see
+    // `precedingNotNullChecks`, and a check on an expression that is written after the predicate
+    // is not generated before it, see the nested checks below.
     // NOTE: `FilterExec.doConsume`'s CSE branch inlines the same interleaving so it can
     // emit CSE state precomputes between the IsNotNull checks and each otherPred body.
     // Any change to this algorithm must be mirrored there (and vice versa).
     // TODO: revisit this. We can consider reordering predicates as well.
     val generatedIsNotNullChecks = new Array[Boolean](notNullPreds.length)
     val extraIsNotNullAttrs = mutable.Set[Attribute]()
-    val generated = otherPreds.map { c =>
+    var nullableSeen = false
+    val generated = otherPreds.zip(boundOtherPreds).zipWithIndex.map { case ((c, bound), k) =>
+      val afterNullable = nullFlag.isDefined && nullableSeen

Review Comment:
   **`afterNullable` is decided at codegen time, so the moved checks and the skipped nested checks also apply to rows where no conjunct is NULL.** Once some conjunct can be NULL after binding, `precedingNotNullChecks` and the nested-branch skip (L334; L530 and L560 in the CSE branch) are in the generated code for every row. So with the default config, rows without any NULL conjunct also behave differently from 4.3, in both directions, always toward the codegen-off result. With ANSI on and Parquet filter pushdown off:
   
   - On `withNullConjunctTable(Int.MaxValue)`, `SELECT a FROM t WHERE cast(s AS INT) IS NOT NULL AND (a > 0 OR b > 0) AND y < 0` returns no rows on master and with the legacy config, because `y < 0` is FALSE before the leftover cast check runs. Now it raises `CAST_INVALID_INPUT`: on the first row the `OR` is TRUE (`NULL OR TRUE`), and `isnotnull(cast(s AS INT))` is generated before `y < 0`. Codegen off raises too.
   - On a row `(a, b, s) = (0, 1, 'x')`, `WHERE NOT (a > 0 AND b > 0) AND coalesce(b, cast(s AS INT)) < 0 AND cast(s AS INT) IS NOT NULL` raised `CAST_INVALID_INPUT` on master and still does with the legacy config, but now returns no rows, as codegen off does.
   
   The migration guide ("on a row after one of them evaluates to `NULL`" ... "on that row"), the legacy config doc (`SQLConf.scala:1751`) and the user-facing section of the PR description ("Rows whose earlier conjunct is FALSE are not affected") describe only rows where a conjunct is NULL. Also, the SPARK-60377 example under "Not fixed here" in the PR description, which becomes the commit message, now raises only with the legacy config; `WHERE coalesce(b, cast(s AS INT)) < 0 AND cast(s AS INT) IS NOT NULL` on `(b, s) = (1, 'x')` still shows it with the default config.
   
   Could you add a sentence to the migration guide and the legacy config doc saying that, once a condition has a conjunct that can be NULL, the `IS NOT NULL` checks after that conjunct are evaluated in the order of the condition on every row, not only on rows where a conjunct is NULL, update that example, and pin the first query in a test?



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -137,47 +137,151 @@ trait GeneratePredicateHelper extends PredicateHelper {
       condition: Expression,
       inputAttrs: Seq[Attribute],
       inputExprCode: Seq[ExprCode]): String = {
-    val (notNullPreds, otherPreds) = splitConjunctivePredicates(condition).partition {
-      case IsNotNull(a) => isNullIntolerant(a) && a.references.subsetOf(AttributeSet(inputAttrs))
-      case _ => false
-    }
+    val (notNullPreds, otherPreds, precedingNotNulls) =
+      splitNotNullPreds(condition, AttributeSet(inputAttrs))
     val nonNullAttrExprIds = notNullPreds.flatMap(_.references).distinct.map(_.exprId)
     val outputAttrs = outputWithNullability(inputAttrs, nonNullAttrExprIds)
     generatePredicateCode(
-      ctx, inputAttrs, inputExprCode, outputAttrs, notNullPreds, otherPreds,
+      ctx, inputAttrs, inputExprCode, notNullPreds, otherPreds,
+      otherPreds.map(BindReferences.bindReference(_, outputAttrs)), precedingNotNulls,
       nonNullAttrExprIds)
   }
 
+  /**
+   * Splits `condition` into the `IsNotNull` checks on null-intolerant expressions over
+   * `inputAttrs`, the other conjuncts, and for each of the other conjuncts the number of the
+   * checks that precede it in `condition`. The conjuncts keep their order within each group, so
+   * the checks before the k-th other conjunct are the first `precedingNotNulls(k)` checks.
+   */
+  protected def splitNotNullPreds(
+      condition: Expression,
+      inputAttrs: AttributeSet): (Seq[Expression], Seq[Expression], Seq[Int]) = {
+    val notNullPreds = mutable.ArrayBuffer.empty[Expression]
+    val otherPreds = mutable.ArrayBuffer.empty[Expression]
+    val precedingNotNulls = mutable.ArrayBuffer.empty[Int]
+    splitConjunctivePredicates(condition).foreach {
+      case p @ IsNotNull(a) if isNullIntolerant(a) && a.references.subsetOf(inputAttrs) =>
+        notNullPreds += p
+      case p =>
+        otherPreds += p
+        precedingNotNulls += notNullPreds.length
+    }
+    (notNullPreds.toSeq, otherPreds.toSeq, precedingNotNulls.toSeq)
+  }
+
+  /**
+   * Returns a fresh name for the flag that records a conjunct evaluated to NULL, or None if the
+   * row is dropped at the first NULL conjunct (the legacy behavior) or none of `conjuncts`, bound
+   * with the nullability they are generated with, can be NULL.
+   *
+   * A row is kept only when the whole condition is TRUE, so both FALSE and NULL drop it. Only
+   * a FALSE conjunct decides the result of the AND on its own, though: after a NULL one, the
+   * remaining conjuncts must still be evaluated, as in `And.eval`, so that an error they raise
+   * on the row is raised (SPARK-60315). The flag drops the row once all of them are evaluated.
+   */
+  protected def nullConjunctFlag(
+      ctx: CodegenContext,
+      conjuncts: Seq[Expression]): Option[String] = {
+    if (conf.legacyFilterShortCircuitOnNullConjunct || !conjuncts.exists(_.nullable)) {

Review Comment:
   **The flag, and with it the hoisted checks, is generated whenever a conjunct can be NULL, even when nothing after it can raise an error.** This condition looks only at nullability, so any `OR`, `IN`, `CASE` or `coalesce` over nullable columns turns it on. After that conjunct, `precedingNotNullChecks` generates all pending checks before the next conjunct, including every `isnotnull(...)` that `InferFiltersFromConstraints` prepends, and on the non-CSE path each check loads its column value through `evaluateRequiredVariables` (L269). That code runs on every row that reaches the next conjunct, not only on rows where a conjunct is NULL.
   
   For example, on a Parquet table `t(a INT, b INT, c INT, d DECIMAL(38,10))`, `WHERE (a > 5 OR b > 5) AND c = 1 AND d = 2` becomes `isnotnull(c) AND isnotnull(d) AND (a > 5 OR b > 5) AND c = 1 AND d = 2`. Master decodes `d` only for rows with `c = 1`, while this PR decodes it, allocating a `BigInteger` and a `BigDecimal`, on every row where the `OR` is TRUE or NULL. No conjunct there can raise, so neither the flag nor the hoisting changes any result. This is the cost that d6036f470d7 (the SPARK-56032 follow-up for TPC-DS q28) removed from the CSE prologue, and it goes beyond the risk noted in the first review ("limited to plain-column null checks"), because the values are decoded too and common `OR`/`IN` filters trigger it.
   
   Could you gate both with `ExprUtils.canEvaluateUnconditionally`, applied to the unbound expressions? Return `None` here unless a conjunct after the first nullable one, or one of the `IsNotNull` checks, cannot be evaluated unconditionally, and in `precedingNotNullChecks` hoist before the k-th conjunct only if it or one of the pending checks cannot. `precedingNotNulls` never decreases, so the next conjunct that can raise still gets every check that precedes it, and the legacy path stays as it is.



##########
docs/sql-migration-guide.md:
##########
@@ -51,6 +51,7 @@ license: |
 - Since Spark 4.4, the value columns of `UNPIVOT` and `Dataset.unpivot` merge the nested nullability (array element, map value, and struct field nullability) of all unpivoted values. Previously, a value column took the nested nullability of the first listed value, so a `NULL` array element, map value, or struct field of a later value could be returned as a non-`NULL` value such as `0`, and `IS NULL` on it returned `false`. A view created by an earlier Spark version over such an `UNPIVOT` may have recorded the non-nullable nested type in its schema and can fail to read; recreate it with `CREATE OR REPLACE VIEW`. To restore the previous behavior, set `spark.sql.legacy.unpivot.useFirstValueNestedNullability` to `true`.
 - Since Spark 4.4, when `spark.sql.parquet.fieldId.read.enabled` is true, a Parquet read fails with `PARQUET_FIELD_REQUESTED_MORE_THAN_ONCE` if two or more requested fields of a struct (or of the top-level schema) resolve to the same Parquet field. This happens when one requested field is matched by field ID and another by name, or when two requested fields have the same field ID. In Spark 4.3 and earlier, such a read returned wrong values without an error: with the vectorized reader one of the fields read as non-null default values (for example `0`) or uninitialized data, and with the parquet-mr reader (`spark.sql.parquet.enableVectorizedReader` set to `false`) one of the fields read as `null`. To read the Parquet field more than once, request it once and refer to the resulting column more than once in the query.
 - Since Spark 4.4, when `spark.sql.groupByOrdinal` is true (the default), the pipe `AGGREGATE` operator rejects an aliased integer literal in its `GROUP BY` clause, such as `TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp`, with `UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE`, because a bare integer literal in the same position is an ordinal. Previously the aliased literal was grouped as a constant. Bare ordinals and aliases on other grouping expressions are not affected. To restore the previous behavior, set `spark.sql.legacy.allowAliasedPipeAggregateOrdinals` to `true`.
+- Since Spark 4.4, with whole-stage codegen enabled, a filter condition, including the `FILTER` clause of an aggregate function, evaluates the remaining top-level conjuncts of the condition on a row after one of them evaluates to `NULL`, as the SQL standard requires, and does so in the order they appear in the condition, as Spark already did with whole-stage codegen disabled. Only a `FALSE` conjunct stops the evaluation. The one exception is an `IS NOT NULL` check on a column that a later conjunct references: it is still evaluated before that conjunct, even when it is written after it, and can drop the row before an error the conjunct raises. A row whose condition is `NULL` is still dropped, but an error raised by a later conjunct on that row, such as `ARITHMETIC_OVERFLOW` in ANSI mode, now fails the query. For example, `SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0` can fail with `ARITHMETIC_OVERFLOW` on a row where `a` is `NULL`, `b` is `1`, and `y` is `2147483647`, un
 less the row is dropped earlier by a `FALSE` conjunct or `IS NOT NULL` check that precedes `y + 1 > 0` in the condition, or skipped before the filter, for example by data source filter pushdown such as Parquet row group statistics. Previously the query skipped such a row with whole-stage codegen enabled. The later conjuncts also run on more rows than before, so a nondeterministic conjunct such as `rand(seed)` can return different values on the remaining rows, and a side-effecting user-defined function is called on rows that are dropped. To restore the previous behavior, set `spark.sql.legacy.filterShortCircuitOnNullConjunct` to `true`.

Review Comment:
   Minor: "as Spark already did with whole-stage codegen disabled. Only a `FALSE` conjunct stops the evaluation." does not hold when the conjuncts after a FALSE one share a subexpression. With whole-stage codegen off, `FilterExec` uses `Predicate.create`, and `GeneratePredicate` evaluates the common subexpressions before the condition (`GeneratePredicate.scala:63-64`); the generated projection for an aggregate `FILTER` does the same. So on `withNullConjunctTable(Int.MaxValue)` with ANSI on and Parquet filter pushdown off, `SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND b < 0 AND y + 1 > 0 AND y + 1 > -1` returns no rows with codegen on, whatever the legacy and filterExec CSE configs, and raises `ARITHMETIC_OVERFLOW` with codegen off. This is the pre-existing difference you mentioned in the review thread, and it also happens without the NULL conjunct, so it is not a regression. Could you list it under "Not fixed here" in the PR description, or file a JIRA, and qualify this sentence?



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -451,10 +597,11 @@ case class FilterExec(condition: Expression, child: SparkPlan)
           case (p, i) if !generatedIsNotNullChecks(i) => genNotNull(p)
         }.mkString("\n")
 
-        (inputVarsEvalCode, predCode + "\n" + leftoverNotNull)
+        (inputVarsEvalCode, withNullConjunctFlag(predCode + "\n" + leftoverNotNull, nullFlag))
       } else {
         ("", generatePredicateCode(
-          ctx, child.output, input, output, notNullPreds, otherPreds, notNullAttributes))
+          ctx, child.output, input, notNullPreds, otherPreds, boundOtherPreds,

Review Comment:
   Nit: this call now passes `boundOtherPreds` to the non-CSE path, where it decides the NULL-conjunct flag and supplies the bodies of the conjuncts, but the comment on `boundOtherPreds` (L381-383) still says it is "shared between the CSE gate in `doConsume` and the CSE codegen itself". Could you update it, e.g. "shared by the CSE gate in `doConsume` and both predicate code paths (the CSE branch and `generatePredicateCode`)"?



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,274 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y, NULL, 'x'), (0, 1, 0, 1, '1') AS v(a, b, y, x, s)")
+      f
+    }
+  }
+
+  // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+  private val nullConjunctCSEQuery =
+    "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+
+  private def checkArithmeticOverflow(query: String): Unit = {
+    checkError(
+      exception = intercept[SparkArithmeticException] {
+        sql(query).collect()
+      },
+      condition = "ARITHMETIC_OVERFLOW",
+      parameters = Map(
+        "message" -> "overflow",
+        "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+        "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+      context = ExpectedContext(
+        fragment = "y + 1",
+        start = query.indexOf("y + 1"),
+        stop = query.indexOf("y + 1") + 4))
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(nullConjunctCSEQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          nullConjunctCSEQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkArithmeticOverflow(query)
+              }
+          }
+        }
+      }
+    }
+    // A NULL conjunct from OR. The second row does not pass it.
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT a FROM t WHERE (a > 0 OR b < 0) AND y + 1 > 0"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Seq.empty)
+        } else {
+          checkArithmeticOverflow(query)
+        }
+      }
+    }
+    // Without an error, the CSE path drops the first row after its last conjunct is evaluated.
+    withNullConjunctTable(0) {
+      withNullConjunctConfs(
+          SQLConf.ANSI_ENABLED.key -> "true",
+          SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> "true") { _ =>
+        checkAnswer(sql(nullConjunctCSEQuery), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val condition = "NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      val query = s"SELECT a FROM t WHERE $condition"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+        // A check on an expression, not on a column, that precedes the NULL conjunct:
+        // `cast(s AS INT)` is NULL on the first row, so the check is FALSE and drops the row
+        // before the overflowing conjunct, as with codegen off.
+        checkAnswer(
+          sql(s"SELECT a FROM t WHERE cast(s AS INT) IS NOT NULL AND $condition"), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))
+        // FALSE decides the AND, so the overflowing conjunct after it is not evaluated.
+        checkAnswer(
+          sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND b < 0 AND y + 1 > 0"),
+          Seq.empty)
+      }
+      // `b > 0` cannot be NULL after the inferred `isnotnull(b)`, so no flag is generated.
+      val df = sql("SELECT a FROM t WHERE b > 0 AND y > -1")
+      checkAnswer(df, Seq(Row(null), Row(0)))
+      assert(!codegenString(df.queryExecution.executedPlan).contains("nullConjunct"))
+    }
+  }
+
+  test("SPARK-60315: a filter checks IS NOT NULL before the conjuncts it precedes") {
+    withNullConjunctTable(Int.MaxValue) {
+      // `x` is NULL on the first row. With codegen off, a FALSE `isnotnull(x)` drops the row
+      // before a conjunct after it in the condition is evaluated. With codegen on, the check is
+      // generated just before the first conjunct that references `x`, or after all of them, so
+      // after a NULL conjunct it has to be generated before the conjuncts it precedes.
+      //
+      // The common `b * 2` takes the CSE path of FilterExec. The overflowing `y + 1` is not
+      // common because `GeneratePredicate` (codegen off) evaluates common subexpressions first.
+      val cseQuery = "SELECT a FROM t WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND " +
+        "y + 1 > 0 AND b * 2 > 0 AND b * 2 > -1"
+      def multiplyExactCount(filterExecCSE: Boolean): Int = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(cseQuery).queryExecution.executedPlan)
+          "multiplyExact".r.findAllIn(code).length
+        }
+      }
+      assert(multiplyExactCount(filterExecCSE = true) < multiplyExactCount(filterExecCSE = false))
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        withNullConjunctConfs(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+          skipsAtNull =>
+            // `isnotnull(x)` is inferred from `x > 0` and precedes the overflowing conjunct.
+            checkAnswer(
+              sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x > 0"),
+              Row(0))
+            // A user-written check that no other conjunct references.
+            checkAnswer(
+              sql("SELECT a FROM t WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND y + 1 > 0"),
+              Row(0))
+            // The same with a common subexpression.
+            checkAnswer(sql(cseQuery), Row(0))
+            // A check written after the overflowing conjunct stays after it.
+            val checkAfter =
+              "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x IS NOT NULL"

Review Comment:
   Minor: this query, like the inferred-check one at L2993, has no common subexpression, so it takes `generatePredicateCode` even when `filterExecCSE` is true. Only `cseQuery`, whose check is written first, runs the preceding checks through the CSE branch of `FilterExec.doConsume`. If a later change to that branch emitted the pending column checks right after the first nullable conjunct (the shape that cloud-fan's first review warned would bring back the original inconsistency), `... AND y + 1 > 0 AND x IS NOT NULL AND b * 2 > 0 AND b * 2 > -1` would return `[0]` with codegen on and raise with it off, and the suite would still pass; the new expression-check test only catches hoisting that includes the expression check. Could you also check `checkAfter + " AND b * 2 > 0 AND b * 2 > -1"` with the same `skipsAtNull` expectation? With `precedingNotNulls = [2, 2, 3, 3]`, it would pin the per-conjunct placement as well.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -373,29 +503,43 @@ case class FilterExec(condition: Expression, child: SparkPlan)
         // Emit an IsNotNull check, binding against child.output so the inner expression
         // is evaluated with its original nullability (the tightened-nullability `output`
         // is only appropriate inside otherPreds, where the required checks have fired).
-        // The `bound.nullable` gate below is defensive -- `IsNotNull` always produces a
-        // non-nullable Boolean, so this branch is structurally unreachable today; it
-        // mirrors the non-CSE helper's shape to keep future refactors safe.
+        // Passing `bound.nullable` to `conjunctCheck` is defensive -- `IsNotNull` always
+        // produces a non-nullable Boolean, so its NULL handling is unreachable here; the
+        // non-CSE helper generates its IsNotNull checks the same way.
         def genNotNull(pred: Expression): String = {
           val bound = BindReferences.bindReference(pred, child.output)
           val evaluated = evaluateRequiredVariables(child.output, input, pred.references)
           val ev = ExpressionCanonicalizer.execute(bound).genCode(ctx)
-          val nullCheck = if (bound.nullable) s"${ev.isNull} || " else ""
           s"""
              |$evaluated
              |${ev.code}
-             |if (${nullCheck}!${ev.value}) continue;
+             |${conjunctCheck(ev, bound.nullable, nullFlag)}

Review Comment:
   Nit: with a flag, passing `nullFlag` here turns the "defensive" NULL handling of a check into `if (isNull) { flag = true; } else if (!value) continue;`. So if a check could ever be NULL, it would no longer drop the row before the later conjuncts and CSE precomputes that are bound with its column non-nullable, which is what invariant (c) above is about. Master's `if (isNull || !value) continue;` was really defensive, and `genNotNull` in `generatePredicateCode` (L277, L283) has the same issue. Could you pass `None` for the `IsNotNull` checks in both helpers? The generated code does not change, since `IsNotNull` is never nullable.



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,274 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y, NULL, 'x'), (0, 1, 0, 1, '1') AS v(a, b, y, x, s)")
+      f
+    }
+  }
+
+  // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+  private val nullConjunctCSEQuery =
+    "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+
+  private def checkArithmeticOverflow(query: String): Unit = {
+    checkError(
+      exception = intercept[SparkArithmeticException] {
+        sql(query).collect()
+      },
+      condition = "ARITHMETIC_OVERFLOW",
+      parameters = Map(
+        "message" -> "overflow",
+        "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+        "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+      context = ExpectedContext(
+        fragment = "y + 1",
+        start = query.indexOf("y + 1"),
+        stop = query.indexOf("y + 1") + 4))
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(nullConjunctCSEQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          nullConjunctCSEQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkArithmeticOverflow(query)
+              }
+          }
+        }
+      }
+    }
+    // A NULL conjunct from OR. The second row does not pass it.
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT a FROM t WHERE (a > 0 OR b < 0) AND y + 1 > 0"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Seq.empty)
+        } else {
+          checkArithmeticOverflow(query)
+        }
+      }
+    }
+    // Without an error, the CSE path drops the first row after its last conjunct is evaluated.
+    withNullConjunctTable(0) {
+      withNullConjunctConfs(
+          SQLConf.ANSI_ENABLED.key -> "true",
+          SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> "true") { _ =>
+        checkAnswer(sql(nullConjunctCSEQuery), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val condition = "NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      val query = s"SELECT a FROM t WHERE $condition"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+        // A check on an expression, not on a column, that precedes the NULL conjunct:
+        // `cast(s AS INT)` is NULL on the first row, so the check is FALSE and drops the row
+        // before the overflowing conjunct, as with codegen off.
+        checkAnswer(
+          sql(s"SELECT a FROM t WHERE cast(s AS INT) IS NOT NULL AND $condition"), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))
+        // FALSE decides the AND, so the overflowing conjunct after it is not evaluated.
+        checkAnswer(
+          sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND b < 0 AND y + 1 > 0"),
+          Seq.empty)
+      }
+      // `b > 0` cannot be NULL after the inferred `isnotnull(b)`, so no flag is generated.
+      val df = sql("SELECT a FROM t WHERE b > 0 AND y > -1")
+      checkAnswer(df, Seq(Row(null), Row(0)))
+      assert(!codegenString(df.queryExecution.executedPlan).contains("nullConjunct"))

Review Comment:
   Nit: when the plan has no whole-stage codegen subtree, `codegenString` returns only "Found 0 WholeStageCodegen subtrees.", so this assertion would also pass if the filter were not in whole-stage codegen. The other `FilterExec` codegen tests here guard with `plan.exists(_.isInstanceOf[WholeStageCodegenExec])`, and `genCode` (L889) asserts it. Could you use `assert(genCode(df).forall(!_.body.contains("nullConjunct")))` instead?



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -201,56 +316,64 @@ trait GeneratePredicateHelper extends PredicateHelper {
         if (idx != -1 && !generatedIsNotNullChecks(idx)) {
           generatedIsNotNullChecks(idx) = true
           // Use the child's output. The nullability is what the child produced.
-          genPredicate(notNullPreds(idx), inputExprCode, inputAttrs)
+          genNotNull(notNullPreds(idx))
         } else if (nonNullAttrExprIds.contains(r.exprId) && !extraIsNotNullAttrs.contains(r)) {

Review Comment:
   Nit: when `precedingNotNullChecks` has already generated the direct `IsNotNull(c)` for a column, this branch adds a synthetic `IsNotNull(c)` for the same column right before the conjunct that references it, e.g. for `c` in `(a = 1 OR b = 2) AND c = 5`. It is harmless at runtime, but it adds code to the per-row loop and goes against "not doing redundant IsNotNull checks" (L290). Master already does this for the second conjunct that references a column, e.g. `c <= hi` of a `BETWEEN`. Could you guard this branch with `idx == -1` (and `ni == -1` at L552 in the CSE branch)? A synthetic check is only needed for a column that only a check on an expression covers. If the generated code without a flag has to stay identical to master, `idx == -1 || !afterNullable` works too.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -373,29 +503,43 @@ case class FilterExec(condition: Expression, child: SparkPlan)
         // Emit an IsNotNull check, binding against child.output so the inner expression
         // is evaluated with its original nullability (the tightened-nullability `output`
         // is only appropriate inside otherPreds, where the required checks have fired).
-        // The `bound.nullable` gate below is defensive -- `IsNotNull` always produces a
-        // non-nullable Boolean, so this branch is structurally unreachable today; it
-        // mirrors the non-CSE helper's shape to keep future refactors safe.
+        // Passing `bound.nullable` to `conjunctCheck` is defensive -- `IsNotNull` always
+        // produces a non-nullable Boolean, so its NULL handling is unreachable here; the
+        // non-CSE helper generates its IsNotNull checks the same way.
         def genNotNull(pred: Expression): String = {
           val bound = BindReferences.bindReference(pred, child.output)
           val evaluated = evaluateRequiredVariables(child.output, input, pred.references)
           val ev = ExpressionCanonicalizer.execute(bound).genCode(ctx)
-          val nullCheck = if (bound.nullable) s"${ev.isNull} || " else ""
           s"""
              |$evaluated
              |${ev.code}
-             |if (${nullCheck}!${ev.value}) continue;
+             |${conjunctCheck(ev, bound.nullable, nullFlag)}
            """.stripMargin
         }
 
         val generatedIsNotNullChecks = new Array[Boolean](notNullPreds.length)
         val extraIsNotNullAttrs = mutable.Set[Attribute]()
+        var nullableSeen = false
 
         val predCode: String = {
           val parts = new StringBuilder
           ctx.withSubExprEliminationExprs(subExprs.states) {
             otherPreds.zip(boundOtherPreds).zipWithIndex.foreach {
               case ((orig, bound), idx) =>
+                val afterNullable = nullFlag.isDefined && nullableSeen
+                precedingNotNullChecks(
+                    notNullPreds, precedingNotNulls, idx, afterNullable,
+                    generatedIsNotNullChecks).foreach { p =>
+                  // The inner expression of a check is evaluated with no CSE state, as below.

Review Comment:
   Nit: this block (L534-540) is a copy of the one below (L566-572), and its only explanation is "as below"; why a check is generated without the CSE state is not written down in the main code. Could you move it into a local helper next to `genNotNull`, e.g. `genNotNullWithoutCSE`, with a short comment on the reason? The calls stay in the same order, so the generated code does not change.



##########
docs/sql-migration-guide.md:
##########
@@ -51,6 +51,7 @@ license: |
 - Since Spark 4.4, the value columns of `UNPIVOT` and `Dataset.unpivot` merge the nested nullability (array element, map value, and struct field nullability) of all unpivoted values. Previously, a value column took the nested nullability of the first listed value, so a `NULL` array element, map value, or struct field of a later value could be returned as a non-`NULL` value such as `0`, and `IS NULL` on it returned `false`. A view created by an earlier Spark version over such an `UNPIVOT` may have recorded the non-nullable nested type in its schema and can fail to read; recreate it with `CREATE OR REPLACE VIEW`. To restore the previous behavior, set `spark.sql.legacy.unpivot.useFirstValueNestedNullability` to `true`.
 - Since Spark 4.4, when `spark.sql.parquet.fieldId.read.enabled` is true, a Parquet read fails with `PARQUET_FIELD_REQUESTED_MORE_THAN_ONCE` if two or more requested fields of a struct (or of the top-level schema) resolve to the same Parquet field. This happens when one requested field is matched by field ID and another by name, or when two requested fields have the same field ID. In Spark 4.3 and earlier, such a read returned wrong values without an error: with the vectorized reader one of the fields read as non-null default values (for example `0`) or uninitialized data, and with the parquet-mr reader (`spark.sql.parquet.enableVectorizedReader` set to `false`) one of the fields read as `null`. To read the Parquet field more than once, request it once and refer to the resulting column more than once in the query.
 - Since Spark 4.4, when `spark.sql.groupByOrdinal` is true (the default), the pipe `AGGREGATE` operator rejects an aliased integer literal in its `GROUP BY` clause, such as `TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp`, with `UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE`, because a bare integer literal in the same position is an ordinal. Previously the aliased literal was grouped as a constant. Bare ordinals and aliases on other grouping expressions are not affected. To restore the previous behavior, set `spark.sql.legacy.allowAliasedPipeAggregateOrdinals` to `true`.
+- Since Spark 4.4, with whole-stage codegen enabled, a filter condition, including the `FILTER` clause of an aggregate function, evaluates the remaining top-level conjuncts of the condition on a row after one of them evaluates to `NULL`, as the SQL standard requires, and does so in the order they appear in the condition, as Spark already did with whole-stage codegen disabled. Only a `FALSE` conjunct stops the evaluation. The one exception is an `IS NOT NULL` check on a column that a later conjunct references: it is still evaluated before that conjunct, even when it is written after it, and can drop the row before an error the conjunct raises. A row whose condition is `NULL` is still dropped, but an error raised by a later conjunct on that row, such as `ARITHMETIC_OVERFLOW` in ANSI mode, now fails the query. For example, `SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0` can fail with `ARITHMETIC_OVERFLOW` on a row where `a` is `NULL`, `b` is `1`, and `y` is `2147483647`, un
 less the row is dropped earlier by a `FALSE` conjunct or `IS NOT NULL` check that precedes `y + 1 > 0` in the condition, or skipped before the filter, for example by data source filter pushdown such as Parquet row group statistics. Previously the query skipped such a row with whole-stage codegen enabled. The later conjuncts also run on more rows than before, so a nondeterministic conjunct such as `rand(seed)` can return different values on the remaining rows, and a side-effecting user-defined function is called on rows that are dropped. To restore the previous behavior, set `spark.sql.legacy.filterShortCircuitOnNullConjunct` to `true`.

Review Comment:
   Minor: "The one exception" is narrower than what the code does. On `withNullConjunctTable(Int.MaxValue)` with ANSI on and Parquet filter pushdown off:
   
   1. A check on an expression also makes its columns non-nullable, so a synthetic `isnotnull(x)` is generated before the first later conjunct that references `x` (`basicPhysicalOperators.scala:320-322`). `SELECT count(*) FILTER (WHERE NOT (a > 0 AND b > 0) AND y + coalesce(x, 1) > 0 AND -x IS NOT NULL) FROM t` returns 1 with whole-stage codegen on and raises `ARITHMETIC_OVERFLOW` with it off; an aggregate `FILTER` gets no inferred checks.
   2. The moved check also runs before the conjuncts between that conjunct and its own position, so it can drop the row before an error raised by a conjunct that does not reference the column. `WHERE NOT (a > 0 AND b > 0) AND (x > 0 OR b > 0) AND y + 1 > 0 AND x IS NOT NULL` drops the first row with codegen on, while codegen off evaluates the `OR` (TRUE) and fails at `y + 1 > 0`. The `precedingNotNullChecks` scaladoc ("unless the conjunct raises an error on the row first") has the same gap.
   3. The first conjunct that can be NULL keeps the nested hoisting, since `afterNullable` is false there. `WHERE NOT (a > 0 AND coalesce(b, cast(s AS INT)) > 0) AND b < 0 AND cast(s AS INT) IS NOT NULL` raises `CAST_INVALID_INPUT` on the first row, whose first conjunct is NULL, with codegen on, and returns no rows with codegen off.
   
   All three behave as on master, so this is only about the text. Could you describe the exception more broadly (a check on a column, or on an expression over it, runs before the first later conjunct that references the column and every conjunct up to its own position) and mention the first nullable conjunct, or say "One exception"? The "Without a NULL conjunct" of the SPARK-60377 bullet in the PR description could cover case 3 too.



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,274 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y, NULL, 'x'), (0, 1, 0, 1, '1') AS v(a, b, y, x, s)")
+      f
+    }
+  }
+
+  // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+  private val nullConjunctCSEQuery =
+    "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+
+  private def checkArithmeticOverflow(query: String): Unit = {
+    checkError(
+      exception = intercept[SparkArithmeticException] {
+        sql(query).collect()
+      },
+      condition = "ARITHMETIC_OVERFLOW",
+      parameters = Map(
+        "message" -> "overflow",
+        "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+        "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+      context = ExpectedContext(
+        fragment = "y + 1",
+        start = query.indexOf("y + 1"),
+        stop = query.indexOf("y + 1") + 4))
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(nullConjunctCSEQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          nullConjunctCSEQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkArithmeticOverflow(query)
+              }
+          }
+        }
+      }
+    }
+    // A NULL conjunct from OR. The second row does not pass it.
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT a FROM t WHERE (a > 0 OR b < 0) AND y + 1 > 0"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Seq.empty)
+        } else {
+          checkArithmeticOverflow(query)
+        }
+      }
+    }
+    // Without an error, the CSE path drops the first row after its last conjunct is evaluated.
+    withNullConjunctTable(0) {
+      withNullConjunctConfs(
+          SQLConf.ANSI_ENABLED.key -> "true",
+          SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> "true") { _ =>
+        checkAnswer(sql(nullConjunctCSEQuery), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val condition = "NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      val query = s"SELECT a FROM t WHERE $condition"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+        // A check on an expression, not on a column, that precedes the NULL conjunct:
+        // `cast(s AS INT)` is NULL on the first row, so the check is FALSE and drops the row
+        // before the overflowing conjunct, as with codegen off.
+        checkAnswer(
+          sql(s"SELECT a FROM t WHERE cast(s AS INT) IS NOT NULL AND $condition"), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))
+        // FALSE decides the AND, so the overflowing conjunct after it is not evaluated.
+        checkAnswer(
+          sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND b < 0 AND y + 1 > 0"),
+          Seq.empty)
+      }
+      // `b > 0` cannot be NULL after the inferred `isnotnull(b)`, so no flag is generated.
+      val df = sql("SELECT a FROM t WHERE b > 0 AND y > -1")
+      checkAnswer(df, Seq(Row(null), Row(0)))
+      assert(!codegenString(df.queryExecution.executedPlan).contains("nullConjunct"))
+    }
+  }
+
+  test("SPARK-60315: a filter checks IS NOT NULL before the conjuncts it precedes") {
+    withNullConjunctTable(Int.MaxValue) {
+      // `x` is NULL on the first row. With codegen off, a FALSE `isnotnull(x)` drops the row
+      // before a conjunct after it in the condition is evaluated. With codegen on, the check is
+      // generated just before the first conjunct that references `x`, or after all of them, so
+      // after a NULL conjunct it has to be generated before the conjuncts it precedes.
+      //
+      // The common `b * 2` takes the CSE path of FilterExec. The overflowing `y + 1` is not
+      // common because `GeneratePredicate` (codegen off) evaluates common subexpressions first.
+      val cseQuery = "SELECT a FROM t WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND " +
+        "y + 1 > 0 AND b * 2 > 0 AND b * 2 > -1"
+      def multiplyExactCount(filterExecCSE: Boolean): Int = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(cseQuery).queryExecution.executedPlan)
+          "multiplyExact".r.findAllIn(code).length
+        }
+      }
+      assert(multiplyExactCount(filterExecCSE = true) < multiplyExactCount(filterExecCSE = false))
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        withNullConjunctConfs(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+          skipsAtNull =>
+            // `isnotnull(x)` is inferred from `x > 0` and precedes the overflowing conjunct.
+            checkAnswer(
+              sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x > 0"),
+              Row(0))
+            // A user-written check that no other conjunct references.
+            checkAnswer(
+              sql("SELECT a FROM t WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND y + 1 > 0"),
+              Row(0))
+            // The same with a common subexpression.
+            checkAnswer(sql(cseQuery), Row(0))
+            // A check written after the overflowing conjunct stays after it.
+            val checkAfter =
+              "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x IS NOT NULL"
+            if (skipsAtNull) {
+              checkAnswer(sql(checkAfter), Row(0))
+            } else {
+              checkArithmeticOverflow(checkAfter)
+            }
+        }
+      }
+      // The aggregate FILTER clause goes through the same code generation.
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        checkAnswer(
+          sql("SELECT count(1) FILTER (WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND " +
+            "y + 1 > 0) FROM t"),
+          Row(1))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter checks IS NOT NULL on an expression after the conjunct it follows") {
+    withNullConjunctTable(Int.MaxValue) {
+      // `cast(s AS INT)` raises on the first row. With codegen on, a check on an expression is
+      // generated just before the first conjunct that contains the expression (SPARK-51356),
+      // even when it is written after that conjunct. After a NULL conjunct, it has to stay after
+      // the conjunct, as with codegen off: `coalesce(b, cast(s AS INT)) < 0` is FALSE on the row
+      // and drops it before the check raises, while `coalesce(b, cast(s AS INT)) < 2` is TRUE and
+      // the check raises after it. The second row passes the latter condition.
+      //
+      // The common `b * 2` takes the CSE path of FilterExec.
+      def query(conjunct: String, cse: Boolean): String = {
+        val common = if (cse) "b * 2 > 0 AND b * 2 > -1 AND " else ""
+        s"SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND $common$conjunct AND " +
+          "cast(s AS INT) IS NOT NULL"
+      }
+      def multiplyExactCount(filterExecCSE: Boolean): Int = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val plan = sql(query("coalesce(b, cast(s AS INT)) < 0", cse = true)).queryExecution
+          "multiplyExact".r.findAllIn(codegenString(plan.executedPlan)).length
+        }
+      }
+      assert(multiplyExactCount(filterExecCSE = true) < multiplyExactCount(filterExecCSE = false))
+
+      Seq(true, false).foreach { cse =>
+        withNullConjunctConfs(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> cse.toString) {
+          skipsAtNull =>
+            checkAnswer(sql(query("coalesce(b, cast(s AS INT)) < 0", cse)), Seq.empty)
+            val raising = query("coalesce(b, cast(s AS INT)) < 2", cse)
+            if (skipsAtNull) {
+              checkAnswer(sql(raising), Row(0))
+            } else {
+              checkError(
+                exception = intercept[SparkNumberFormatException] {
+                  sql(raising).collect()
+                },
+                condition = "CAST_INVALID_INPUT",
+                parameters = Map(
+                  "expression" -> "'x'",
+                  "sourceType" -> "\"STRING\"",
+                  "targetType" -> "\"INT\"",
+                  "ansiConfig" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+                context = ExpectedContext(
+                  fragment = "cast(s AS INT)",
+                  start = raising.lastIndexOf("cast(s AS INT)"),
+                  stop = raising.lastIndexOf("cast(s AS INT)") + 13))
+            }
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: an aggregate FILTER evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT count(a) FILTER (WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0) FROM t"

Review Comment:
   Minor (pre-existing, not from this PR): the aggregate `FILTER` clause also differs between codegen on and off in the opposite direction. `AggregateCodegenSupport.scala:237-238` eliminates the common subexpressions of the update expressions without their `FILTER` and evaluates them before the `do { ... } while (false)` of each `FILTER` (L270-272 and L297-305 there; the with-keys paths of `HashAggregateExec` do the same), while codegen off evaluates `If(filter, updateExpr, buffer)` (`AggregationIterator.scala:166-169`). So with ANSI on, `SELECT avg(y + 1) FILTER (WHERE b < 0) FROM t` on this table raises `ARITHMETIC_OVERFLOW` with whole-stage codegen on, because `Average` uses `y + 1` twice, and returns NULL with it off, on master too. `spark.sql.subexpressionElimination.enabled=false` does not turn this off. The tests here use `count(a)` and `count(1)`, which have no common subexpression. Could you add it to "Not fixed here" or file a JIRA?



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -186,13 +290,24 @@ trait GeneratePredicateHelper extends PredicateHelper {
     // This has the property of not doing redundant IsNotNull checks and taking better advantage of
     // short-circuiting, not loading attributes until they are needed.
     // This is very perf sensitive.
+    // With the NULL-conjunct flag, once a predicate that can be NULL is generated, the IsNotNull
+    // checks that precede a later predicate in the condition are generated before it, see
+    // `precedingNotNullChecks`, and a check on an expression that is written after the predicate
+    // is not generated before it, see the nested checks below.
     // NOTE: `FilterExec.doConsume`'s CSE branch inlines the same interleaving so it can

Review Comment:
   Nit (follow-up): as cloud-fan noted in the first review, this ordering is mirrored by hand in the CSE branch of `FilterExec.doConsume`, and this PR adds two rules to both copies (the preceding checks at L305-308 and L529-541, and the nested skip at L334 and L560). SPARK-60329 and SPARK-60377 will each need the same pair of edits, and a mismatch shows only for filters with a non-cheap common subexpression, which is why three of the new tests add `y + 1 > -1` or `b * 2 > 0 AND b * 2 > -1` and count `addExact` or `multiplyExact`. Earlier CSE-only bugs came from the same split, e.g. the one fixed by a4399f0c6e7, which added this NOTE. Could a follow-up compute the order once, as a list of checks and conjuncts that both emitters consume, ideally before SPARK-60329 and SPARK-60377?



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,274 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be

Review Comment:
   Nit: the second row also fails `(a > 0 OR b < 0)` (L2903) and `coalesce(b, cast(s AS INT)) < 0` (L3051), not only `b < 0`. Could you list them, or say "unless a test says otherwise"?



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

Posted by "dongjoon-hyun (via GitHub)" <gi...@apache.org>.
dongjoon-hyun commented on code in PR #59573:
URL: https://github.com/apache/spark/pull/59573#discussion_r4329921239


##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,312 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`, `(a > 0 OR b < 0)`
+  // and `coalesce(b, cast(s AS INT)) < 0`. The rows can be

Review Comment:
   Minor: the reproducer in "Why are the changes needed?" of the PR description, which becomes the commit message, does not turn off Parquet filter pushdown, for the reason this comment gives. In a default multi-core `local[*]` session, the CTAS writes each row to its own file (`LocalTableScanExec` uses `min(rows, leafNodeDefaultParallelism)` slices), `BooleanSimplification` turns `NOT (a > 0 AND b > 0)` into `a <= 0 OR b <= 0`, which is pushed down as `or(ltEq(a, 0), ltEq(b, 0))`, and the first row's row group (all-NULL `a`, `b` with min 1) is dropped by its statistics. So both SELECTs return `0`, before and after this PR. Could you add `SET spark.sql.parquet.filterPushdown=false;` after the CTAS there?



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -137,47 +137,182 @@ trait GeneratePredicateHelper extends PredicateHelper {
       condition: Expression,
       inputAttrs: Seq[Attribute],
       inputExprCode: Seq[ExprCode]): String = {
-    val (notNullPreds, otherPreds) = splitConjunctivePredicates(condition).partition {
-      case IsNotNull(a) => isNullIntolerant(a) && a.references.subsetOf(AttributeSet(inputAttrs))
-      case _ => false
-    }
+    val (notNullPreds, otherPreds, precedingNotNulls) =
+      splitNotNullPreds(condition, AttributeSet(inputAttrs))
     val nonNullAttrExprIds = notNullPreds.flatMap(_.references).distinct.map(_.exprId)
     val outputAttrs = outputWithNullability(inputAttrs, nonNullAttrExprIds)
     generatePredicateCode(
-      ctx, inputAttrs, inputExprCode, outputAttrs, notNullPreds, otherPreds,
+      ctx, inputAttrs, inputExprCode, notNullPreds, otherPreds,
+      otherPreds.map(BindReferences.bindReference(_, outputAttrs)), precedingNotNulls,
       nonNullAttrExprIds)
   }
 
+  /**
+   * Splits `condition` into the `IsNotNull` checks on null-intolerant expressions over
+   * `inputAttrs`, the other conjuncts, and for each of the other conjuncts the number of the
+   * checks that precede it in `condition`. The conjuncts keep their order within each group, so
+   * the checks before the k-th other conjunct are the first `precedingNotNulls(k)` checks.
+   */
+  protected def splitNotNullPreds(
+      condition: Expression,
+      inputAttrs: AttributeSet): (Seq[Expression], Seq[Expression], Seq[Int]) = {
+    val notNullPreds = mutable.ArrayBuffer.empty[Expression]
+    val otherPreds = mutable.ArrayBuffer.empty[Expression]
+    val precedingNotNulls = mutable.ArrayBuffer.empty[Int]
+    splitConjunctivePredicates(condition).foreach {
+      case p @ IsNotNull(a) if isNullIntolerant(a) && a.references.subsetOf(inputAttrs) =>
+        notNullPreds += p
+      case p =>
+        otherPreds += p
+        precedingNotNulls += notNullPreds.length
+    }
+    (notNullPreds.toSeq, otherPreds.toSeq, precedingNotNulls.toSeq)
+  }
+
+  /**
+   * Whether evaluating `e` on a row can be observed other than through its value: it can raise
+   * an error, is nondeterministic or may have side effects, such as a UDF. Such an expression
+   * has to be evaluated on the rows, and in the order, that `And.eval` evaluates it on.
+   */
+  protected def mayRaise(e: Expression): Boolean = !ExprUtils.canEvaluateUnconditionally(e)

Review Comment:
   Minor: the PR description says "`GeneratePredicateHelper` gets three helpers: `nullConjunctFlag` ..., `conjunctCheck` ... and `withNullConjunctFlag` ...", which was right at 69318f6. The trait now gets six: `splitNotNullPreds`, `mayRaise`, `nullConjunctFlag`, `conjunctCheck`, `withNullConjunctFlag` and `precedingNotNullChecks`, and `mayRaise` is not named anywhere in the description. Could you update that bullet?



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   Thanks @cloud-fan, confirmed and fixed in 3264b00d897. There is also a pre-existing variant of it without a NULL conjunct, now SPARK-60329; the repro matrix and the details are in the inline thread.
   


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -215,42 +323,44 @@ trait GeneratePredicateHelper extends PredicateHelper {
             if !generatedIsNotNullChecks(idx) && !n.isInstanceOf[Attribute] &&

Review Comment:
   Done in 1fce0247cf8. In both emitters, `afterNullable` is captured before `nullableSeen` is updated, so the first conjunct that can be NULL keeps its nested check, and the nested branch is skipped once `afterNullable` is set. The checks written before the conjunct are already generated by `precedingNotNullChecks` by then, and the columns of the expression stay covered by the synthetic `IsNotNull(r)`.
   
   The new test `SPARK-60315: a filter checks IS NOT NULL on an expression after the conjunct it follows` runs your query in the non-CSE and CSE paths (`b * 2 > 0 AND b * 2 > -1` added for the latter, with the `multiplyExact` count asserted): the row is dropped in every combination when `coalesce(b, cast(s AS INT)) < 0` is FALSE, and the query raises `CAST_INVALID_INPUT` when the conjunct is `< 2` and TRUE, except with the legacy config, which skips the row at the NULL conjunct. The SPARK-51356 tests still pass.



##########
docs/sql-migration-guide.md:
##########
@@ -51,6 +51,7 @@ license: |
 - Since Spark 4.4, the value columns of `UNPIVOT` and `Dataset.unpivot` merge the nested nullability (array element, map value, and struct field nullability) of all unpivoted values. Previously, a value column took the nested nullability of the first listed value, so a `NULL` array element, map value, or struct field of a later value could be returned as a non-`NULL` value such as `0`, and `IS NULL` on it returned `false`. A view created by an earlier Spark version over such an `UNPIVOT` may have recorded the non-nullable nested type in its schema and can fail to read; recreate it with `CREATE OR REPLACE VIEW`. To restore the previous behavior, set `spark.sql.legacy.unpivot.useFirstValueNestedNullability` to `true`.
 - Since Spark 4.4, when `spark.sql.parquet.fieldId.read.enabled` is true, a Parquet read fails with `PARQUET_FIELD_REQUESTED_MORE_THAN_ONCE` if two or more requested fields of a struct (or of the top-level schema) resolve to the same Parquet field. This happens when one requested field is matched by field ID and another by name, or when two requested fields have the same field ID. In Spark 4.3 and earlier, such a read returned wrong values without an error: with the vectorized reader one of the fields read as non-null default values (for example `0`) or uninitialized data, and with the parquet-mr reader (`spark.sql.parquet.enableVectorizedReader` set to `false`) one of the fields read as `null`. To read the Parquet field more than once, request it once and refer to the resulting column more than once in the query.
 - Since Spark 4.4, when `spark.sql.groupByOrdinal` is true (the default), the pipe `AGGREGATE` operator rejects an aliased integer literal in its `GROUP BY` clause, such as `TABLE t |> AGGREGATE sum(x) AS s GROUP BY 2 AS grp`, with `UNSUPPORTED_FEATURE.PIPE_OPERATOR_AGGREGATE_UNSUPPORTED_CASE`, because a bare integer literal in the same position is an ordinal. Previously the aliased literal was grouped as a constant. Bare ordinals and aliases on other grouping expressions are not affected. To restore the previous behavior, set `spark.sql.legacy.allowAliasedPipeAggregateOrdinals` to `true`.
+- Since Spark 4.4, with whole-stage codegen enabled, a filter condition, including the `FILTER` clause of an aggregate function, evaluates the remaining top-level conjuncts of the condition on a row after one of them evaluates to `NULL`, as the SQL standard requires, and does so in the order they appear in the condition, as Spark already did with whole-stage codegen disabled. Only a `FALSE` conjunct stops the evaluation. A row whose condition is `NULL` is still dropped, but an error raised by a later conjunct on that row, such as `ARITHMETIC_OVERFLOW` in ANSI mode, now fails the query. For example, `SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0` can fail with `ARITHMETIC_OVERFLOW` on a row where `a` is `NULL`, `b` is `1`, and `y` is `2147483647`, unless the row is dropped earlier by a `FALSE` conjunct or `IS NOT NULL` check that precedes `y + 1 > 0` in the condition, or skipped before the filter, for example by data source filter pushdown such as Parquet row group statis
 tics. Previously the query skipped such a row with whole-stage codegen enabled. The later conjuncts also run on more rows than before, so a nondeterministic conjunct such as `rand(seed)` can return different values on the remaining rows, and a side-effecting user-defined function is called on rows that are dropped. To restore the previous behavior, set `spark.sql.legacy.filterShortCircuitOnNullConjunct` to `true`.

Review Comment:
   Right, thanks. In 1fce0247cf8 the entry lists it as the one exception: an `IS NOT NULL` check on a column that a later conjunct references is still evaluated before that conjunct, even when it is written after it, and can drop the row before an error the conjunct raises. The `precedingNotNullChecks` scaladoc says the same, with the reason (the conjunct is bound with the column non-nullable) and that such a check only drops a row that `And.eval` drops too, unless the conjunct raises first. The first paragraph of the PR description now says "keeps evaluating the conjuncts after a NULL one", as you suggested, and the "Not fixed here" part mentions this placement next to SPARK-60329.



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,219 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y, NULL, 'x'), (0, 1, 0, 1, '1') AS v(a, b, y, x, s)")
+      f
+    }
+  }
+
+  // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+  private val nullConjunctCSEQuery =
+    "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+
+  private def checkArithmeticOverflow(query: String): Unit = {
+    checkError(
+      exception = intercept[SparkArithmeticException] {
+        sql(query).collect()
+      },
+      condition = "ARITHMETIC_OVERFLOW",
+      parameters = Map(
+        "message" -> "overflow",
+        "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+        "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+      context = ExpectedContext(
+        fragment = "y + 1",
+        start = query.indexOf("y + 1"),
+        stop = query.indexOf("y + 1") + 4))
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(nullConjunctCSEQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          nullConjunctCSEQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkArithmeticOverflow(query)
+              }
+          }
+        }
+      }
+    }
+    // A NULL conjunct from OR. The second row does not pass it.
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT a FROM t WHERE (a > 0 OR b < 0) AND y + 1 > 0"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Seq.empty)
+        } else {
+          checkArithmeticOverflow(query)
+        }
+      }
+    }
+    // Without an error, the CSE path drops the first row after its last conjunct is evaluated.
+    withNullConjunctTable(0) {
+      withNullConjunctConfs(
+          SQLConf.ANSI_ENABLED.key -> "true",
+          SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> "true") { _ =>
+        checkAnswer(sql(nullConjunctCSEQuery), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val condition = "NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      val query = s"SELECT a FROM t WHERE $condition"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+        // A check on an expression, not on a column, that precedes the NULL conjunct:
+        // `cast(s AS INT)` is NULL on the first row, so the check is FALSE and drops the row
+        // before the overflowing conjunct, as with codegen off.
+        checkAnswer(
+          sql(s"SELECT a FROM t WHERE cast(s AS INT) IS NOT NULL AND $condition"), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))
+        // FALSE decides the AND, so the overflowing conjunct after it is not evaluated.
+        checkAnswer(
+          sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND b < 0 AND y + 1 > 0"),
+          Seq.empty)
+      }
+      // `b > 0` cannot be NULL after the inferred `isnotnull(b)`, so no flag is generated.
+      val df = sql("SELECT a FROM t WHERE b > 0 AND y > -1")
+      checkAnswer(df, Seq(Row(null), Row(0)))
+      assert(!codegenString(df.queryExecution.executedPlan).contains("nullConjunct"))
+    }
+  }
+
+  test("SPARK-60315: a filter checks IS NOT NULL before the conjuncts it precedes") {
+    withNullConjunctTable(Int.MaxValue) {
+      // `x` is NULL on the first row. With codegen off, a FALSE `isnotnull(x)` drops the row
+      // before a conjunct after it in the condition is evaluated. With codegen on, the check is
+      // generated just before the first conjunct that references `x`, or after all of them, so
+      // after a NULL conjunct it has to be generated before the conjuncts it precedes.
+      //
+      // The common `b * 2` takes the CSE path of FilterExec. The overflowing `y + 1` is not
+      // common because the interpreted predicate evaluates its common subexpressions up front.

Review Comment:
   Thanks, you are right about `GeneratePredicate`. Took the suggestion in 1fce0247cf8.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -137,47 +137,146 @@ trait GeneratePredicateHelper extends PredicateHelper {
       condition: Expression,
       inputAttrs: Seq[Attribute],
       inputExprCode: Seq[ExprCode]): String = {
-    val (notNullPreds, otherPreds) = splitConjunctivePredicates(condition).partition {
-      case IsNotNull(a) => isNullIntolerant(a) && a.references.subsetOf(AttributeSet(inputAttrs))
-      case _ => false
-    }
+    val (notNullPreds, otherPreds, precedingNotNulls) =
+      splitNotNullPreds(condition, AttributeSet(inputAttrs))
     val nonNullAttrExprIds = notNullPreds.flatMap(_.references).distinct.map(_.exprId)
     val outputAttrs = outputWithNullability(inputAttrs, nonNullAttrExprIds)
     generatePredicateCode(
       ctx, inputAttrs, inputExprCode, outputAttrs, notNullPreds, otherPreds,
+      otherPreds.map(BindReferences.bindReference(_, outputAttrs)), precedingNotNulls,
       nonNullAttrExprIds)
   }
 
+  /**
+   * Splits `condition` into the `IsNotNull` checks on null-intolerant expressions over
+   * `inputAttrs`, the other conjuncts, and for each of the other conjuncts the number of the
+   * checks that precede it in `condition`. The conjuncts keep their order within each group, so
+   * the checks before the k-th other conjunct are the first `precedingNotNulls(k)` checks.
+   */
+  protected def splitNotNullPreds(
+      condition: Expression,
+      inputAttrs: AttributeSet): (Seq[Expression], Seq[Expression], Seq[Int]) = {
+    val notNullPreds = mutable.ArrayBuffer.empty[Expression]
+    val otherPreds = mutable.ArrayBuffer.empty[Expression]
+    val precedingNotNulls = mutable.ArrayBuffer.empty[Int]
+    splitConjunctivePredicates(condition).foreach {
+      case p @ IsNotNull(a) if isNullIntolerant(a) && a.references.subsetOf(inputAttrs) =>
+        notNullPreds += p
+      case p =>
+        otherPreds += p
+        precedingNotNulls += notNullPreds.length
+    }
+    (notNullPreds.toSeq, otherPreds.toSeq, precedingNotNulls.toSeq)
+  }
+
+  /**
+   * Returns a fresh name for the flag that records a conjunct evaluated to NULL, or None if the
+   * row is dropped at the first NULL conjunct (the legacy behavior) or none of `conjuncts`, bound
+   * with the nullability they are generated with, can be NULL.
+   *
+   * A row is kept only when the whole condition is TRUE, so both FALSE and NULL drop it. Only
+   * a FALSE conjunct decides the result of the AND on its own, though: after a NULL one, the
+   * remaining conjuncts must still be evaluated, as in `And.eval`, so that an error they raise
+   * on the row is raised (SPARK-60315). The flag drops the row once all of them are evaluated.
+   */
+  protected def nullConjunctFlag(
+      ctx: CodegenContext,
+      conjuncts: Seq[Expression]): Option[String] = {
+    if (conf.legacyFilterShortCircuitOnNullConjunct || !conjuncts.exists(_.nullable)) {
+      None
+    } else {
+      Some(ctx.freshName("nullConjunct"))
+    }
+  }
+
+  /**
+   * Generates the check that drops the row when the conjunct evaluated into `ev` is FALSE, or
+   * when it is NULL and `nullFlag` is None. With `nullFlag`, a NULL conjunct sets the flag.
+   */
+  protected def conjunctCheck(ev: ExprCode, nullable: Boolean, nullFlag: Option[String]): String = {
+    (nullable, nullFlag) match {
+      case (false, _) => s"if (!${ev.value}) continue;"
+      case (true, None) => s"if (${ev.isNull} || !${ev.value}) continue;"
+      case (true, Some(flag)) =>
+        s"if (${ev.isNull}) { $flag = true; } else if (!${ev.value}) continue;"
+    }
+  }
+
+  /** Wraps the checks of all conjuncts in `code` with the declaration and test of `nullFlag`. */
+  protected def withNullConjunctFlag(code: String, nullFlag: Option[String]): String = {
+    nullFlag match {
+      case Some(flag) =>
+        s"""
+           |boolean $flag = false;
+           |$code
+           |if ($flag) continue;
+         """.stripMargin
+      case None => code
+    }
+  }
+
+  /**
+   * Returns the checks of `notNullPreds` that precede the k-th other conjunct in the condition
+   * and are not generated yet, and marks them generated, when `afterNullable` is set, or Nil.
+   *
+   * With the NULL-conjunct flag, a NULL conjunct no longer drops the row, so the conjuncts after
+   * it are evaluated in the order of the condition, as `And.eval` does: a FALSE IsNotNull check
+   * drops the row before an error a later conjunct raises on it (SPARK-60315). `afterNullable`
+   * is set once a conjunct that can be NULL has been generated. The conjuncts before it are
+   * never reached after a NULL conjunct, so the checks before them keep the lazy placement.
+   */
+  protected def precedingNotNullChecks(
+      notNullPreds: Seq[Expression],
+      precedingNotNulls: Seq[Int],
+      k: Int,
+      afterNullable: Boolean,
+      generated: Array[Boolean]): Seq[Expression] = {
+    if (afterNullable) {
+      notNullPreds.take(precedingNotNulls(k)).zipWithIndex.collect {
+        case (p, idx) if !generated(idx) =>
+          generated(idx) = true
+          p
+      }
+    } else {
+      Nil
+    }
+  }
+
   protected def generatePredicateCode(
       ctx: CodegenContext,
       inputAttrs: Seq[Attribute],
       inputExprCode: Seq[ExprCode],
       outputAttrs: Seq[Attribute],
       notNullPreds: Seq[Expression],
       otherPreds: Seq[Expression],
+      boundOtherPreds: Seq[Expression],

Review Comment:
   Removed in 1fce0247cf8, from the signature and both callers, and the comment now says that the caller binds `boundOtherPreds` against the output with the checked columns tightened to non-nullable.



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   Thanks @dongjoon-hyun, the PR description is updated for items 14 and 15. I merged master to resolve the conflict in `WholeStageCodegenSuite` with the SPARK-60403 tests; the suite passes locally with both sets of tests.
   
   @cloud-fan @LuciferYang your threads are answered in 1fce024 and f81f1c8: the preceding checks are placed in the order of the condition after a nullable conjunct, the nested check on an expression stays at its position, and the flag and the hoisting are generated only when a conjunct or check can raise. Could you take another look? If there are no further comments, I plan to merge once CI is green.


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,274 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y, NULL, 'x'), (0, 1, 0, 1, '1') AS v(a, b, y, x, s)")
+      f
+    }
+  }
+
+  // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+  private val nullConjunctCSEQuery =
+    "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+
+  private def checkArithmeticOverflow(query: String): Unit = {
+    checkError(
+      exception = intercept[SparkArithmeticException] {
+        sql(query).collect()
+      },
+      condition = "ARITHMETIC_OVERFLOW",
+      parameters = Map(
+        "message" -> "overflow",
+        "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+        "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+      context = ExpectedContext(
+        fragment = "y + 1",
+        start = query.indexOf("y + 1"),
+        stop = query.indexOf("y + 1") + 4))
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(nullConjunctCSEQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          nullConjunctCSEQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkArithmeticOverflow(query)
+              }
+          }
+        }
+      }
+    }
+    // A NULL conjunct from OR. The second row does not pass it.
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT a FROM t WHERE (a > 0 OR b < 0) AND y + 1 > 0"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Seq.empty)
+        } else {
+          checkArithmeticOverflow(query)
+        }
+      }
+    }
+    // Without an error, the CSE path drops the first row after its last conjunct is evaluated.
+    withNullConjunctTable(0) {
+      withNullConjunctConfs(
+          SQLConf.ANSI_ENABLED.key -> "true",
+          SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> "true") { _ =>
+        checkAnswer(sql(nullConjunctCSEQuery), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val condition = "NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      val query = s"SELECT a FROM t WHERE $condition"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+        // A check on an expression, not on a column, that precedes the NULL conjunct:
+        // `cast(s AS INT)` is NULL on the first row, so the check is FALSE and drops the row
+        // before the overflowing conjunct, as with codegen off.
+        checkAnswer(
+          sql(s"SELECT a FROM t WHERE cast(s AS INT) IS NOT NULL AND $condition"), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))
+        // FALSE decides the AND, so the overflowing conjunct after it is not evaluated.
+        checkAnswer(
+          sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND b < 0 AND y + 1 > 0"),
+          Seq.empty)
+      }
+      // `b > 0` cannot be NULL after the inferred `isnotnull(b)`, so no flag is generated.
+      val df = sql("SELECT a FROM t WHERE b > 0 AND y > -1")
+      checkAnswer(df, Seq(Row(null), Row(0)))
+      assert(!codegenString(df.queryExecution.executedPlan).contains("nullConjunct"))

Review Comment:
   Good catch. Changed to `genCode(sql(query)).exists(_.body.contains("nullConjunct"))` in f81f1c81082, so the assertion fails if the filter is not in a whole-stage codegen subtree; the same helper checks that the flag is present once a conjunct can raise.



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,274 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be

Review Comment:
   Listed them in f81f1c81082: the second row passes every condition except `b < 0`, `(a > 0 OR b < 0)` and `coalesce(b, cast(s AS INT)) < 0`.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -373,29 +503,43 @@ case class FilterExec(condition: Expression, child: SparkPlan)
         // Emit an IsNotNull check, binding against child.output so the inner expression
         // is evaluated with its original nullability (the tightened-nullability `output`
         // is only appropriate inside otherPreds, where the required checks have fired).
-        // The `bound.nullable` gate below is defensive -- `IsNotNull` always produces a
-        // non-nullable Boolean, so this branch is structurally unreachable today; it
-        // mirrors the non-CSE helper's shape to keep future refactors safe.
+        // Passing `bound.nullable` to `conjunctCheck` is defensive -- `IsNotNull` always
+        // produces a non-nullable Boolean, so its NULL handling is unreachable here; the
+        // non-CSE helper generates its IsNotNull checks the same way.
         def genNotNull(pred: Expression): String = {
           val bound = BindReferences.bindReference(pred, child.output)
           val evaluated = evaluateRequiredVariables(child.output, input, pred.references)
           val ev = ExpressionCanonicalizer.execute(bound).genCode(ctx)
-          val nullCheck = if (bound.nullable) s"${ev.isNull} || " else ""
           s"""
              |$evaluated
              |${ev.code}
-             |if (${nullCheck}!${ev.value}) continue;
+             |${conjunctCheck(ev, bound.nullable, nullFlag)}

Review Comment:
   Agreed, a check must keep dropping the row before the conjuncts and precomputes that are bound with its column non-nullable. In f81f1c81082 both `genNotNull` helpers pass `None`, so the defensive branch is `if (isNull || !value) continue;` again; the generated code does not change since `IsNotNull` is never nullable.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -451,10 +597,11 @@ case class FilterExec(condition: Expression, child: SparkPlan)
           case (p, i) if !generatedIsNotNullChecks(i) => genNotNull(p)
         }.mkString("\n")
 
-        (inputVarsEvalCode, predCode + "\n" + leftoverNotNull)
+        (inputVarsEvalCode, withNullConjunctFlag(predCode + "\n" + leftoverNotNull, nullFlag))
       } else {
         ("", generatePredicateCode(
-          ctx, child.output, input, output, notNullPreds, otherPreds, notNullAttributes))
+          ctx, child.output, input, notNullPreds, otherPreds, boundOtherPreds,

Review Comment:
   Updated in f81f1c81082: "shared by the CSE gate in `doConsume` and both predicate code paths, the CSE branch and `generatePredicateCode`".



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -201,56 +316,64 @@ trait GeneratePredicateHelper extends PredicateHelper {
         if (idx != -1 && !generatedIsNotNullChecks(idx)) {
           generatedIsNotNullChecks(idx) = true
           // Use the child's output. The nullability is what the child produced.
-          genPredicate(notNullPreds(idx), inputExprCode, inputAttrs)
+          genNotNull(notNullPreds(idx))
         } else if (nonNullAttrExprIds.contains(r.exprId) && !extraIsNotNullAttrs.contains(r)) {

Review Comment:
   Right. In f81f1c81082 the synthetic branch is guarded with `idx == -1 || !afterNullable` (`ni == -1 || !afterNullable` in the CSE branch), so after a nullable conjunct a synthetic check is generated only for a column that no direct check covers, and the generated code without the flag stays identical to master, including the redundant check on the `BETWEEN` shape, to keep the change within this PR.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -373,29 +503,43 @@ case class FilterExec(condition: Expression, child: SparkPlan)
         // Emit an IsNotNull check, binding against child.output so the inner expression
         // is evaluated with its original nullability (the tightened-nullability `output`
         // is only appropriate inside otherPreds, where the required checks have fired).
-        // The `bound.nullable` gate below is defensive -- `IsNotNull` always produces a
-        // non-nullable Boolean, so this branch is structurally unreachable today; it
-        // mirrors the non-CSE helper's shape to keep future refactors safe.
+        // Passing `bound.nullable` to `conjunctCheck` is defensive -- `IsNotNull` always
+        // produces a non-nullable Boolean, so its NULL handling is unreachable here; the
+        // non-CSE helper generates its IsNotNull checks the same way.
         def genNotNull(pred: Expression): String = {
           val bound = BindReferences.bindReference(pred, child.output)
           val evaluated = evaluateRequiredVariables(child.output, input, pred.references)
           val ev = ExpressionCanonicalizer.execute(bound).genCode(ctx)
-          val nullCheck = if (bound.nullable) s"${ev.isNull} || " else ""
           s"""
              |$evaluated
              |${ev.code}
-             |if (${nullCheck}!${ev.value}) continue;
+             |${conjunctCheck(ev, bound.nullable, nullFlag)}
            """.stripMargin
         }
 
         val generatedIsNotNullChecks = new Array[Boolean](notNullPreds.length)
         val extraIsNotNullAttrs = mutable.Set[Attribute]()
+        var nullableSeen = false
 
         val predCode: String = {
           val parts = new StringBuilder
           ctx.withSubExprEliminationExprs(subExprs.states) {
             otherPreds.zip(boundOtherPreds).zipWithIndex.foreach {
               case ((orig, bound), idx) =>
+                val afterNullable = nullFlag.isDefined && nullableSeen
+                precedingNotNullChecks(
+                    notNullPreds, precedingNotNulls, idx, afterNullable,
+                    generatedIsNotNullChecks).foreach { p =>
+                  // The inner expression of a check is evaluated with no CSE state, as below.

Review Comment:
   Moved into `genNotNullWithoutCSE` next to `genNotNull` in f81f1c81082, with a comment on the reason: the check is bound against the child's output with its original nullability, while the CSE states are computed over `boundOtherPreds` bound against the tightened output, and a state precomputed for the check could materialize a NULL into a shared variable, see (c). The calls stay in the same order.



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,274 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y, NULL, 'x'), (0, 1, 0, 1, '1') AS v(a, b, y, x, s)")
+      f
+    }
+  }
+
+  // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+  private val nullConjunctCSEQuery =
+    "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+
+  private def checkArithmeticOverflow(query: String): Unit = {
+    checkError(
+      exception = intercept[SparkArithmeticException] {
+        sql(query).collect()
+      },
+      condition = "ARITHMETIC_OVERFLOW",
+      parameters = Map(
+        "message" -> "overflow",
+        "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+        "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+      context = ExpectedContext(
+        fragment = "y + 1",
+        start = query.indexOf("y + 1"),
+        stop = query.indexOf("y + 1") + 4))
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(nullConjunctCSEQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          nullConjunctCSEQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkArithmeticOverflow(query)
+              }
+          }
+        }
+      }
+    }
+    // A NULL conjunct from OR. The second row does not pass it.
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT a FROM t WHERE (a > 0 OR b < 0) AND y + 1 > 0"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Seq.empty)
+        } else {
+          checkArithmeticOverflow(query)
+        }
+      }
+    }
+    // Without an error, the CSE path drops the first row after its last conjunct is evaluated.
+    withNullConjunctTable(0) {
+      withNullConjunctConfs(
+          SQLConf.ANSI_ENABLED.key -> "true",
+          SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> "true") { _ =>
+        checkAnswer(sql(nullConjunctCSEQuery), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val condition = "NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      val query = s"SELECT a FROM t WHERE $condition"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+        // A check on an expression, not on a column, that precedes the NULL conjunct:
+        // `cast(s AS INT)` is NULL on the first row, so the check is FALSE and drops the row
+        // before the overflowing conjunct, as with codegen off.
+        checkAnswer(
+          sql(s"SELECT a FROM t WHERE cast(s AS INT) IS NOT NULL AND $condition"), Row(0))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))
+        // FALSE decides the AND, so the overflowing conjunct after it is not evaluated.
+        checkAnswer(
+          sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND b < 0 AND y + 1 > 0"),
+          Seq.empty)
+      }
+      // `b > 0` cannot be NULL after the inferred `isnotnull(b)`, so no flag is generated.
+      val df = sql("SELECT a FROM t WHERE b > 0 AND y > -1")
+      checkAnswer(df, Seq(Row(null), Row(0)))
+      assert(!codegenString(df.queryExecution.executedPlan).contains("nullConjunct"))
+    }
+  }
+
+  test("SPARK-60315: a filter checks IS NOT NULL before the conjuncts it precedes") {
+    withNullConjunctTable(Int.MaxValue) {
+      // `x` is NULL on the first row. With codegen off, a FALSE `isnotnull(x)` drops the row
+      // before a conjunct after it in the condition is evaluated. With codegen on, the check is
+      // generated just before the first conjunct that references `x`, or after all of them, so
+      // after a NULL conjunct it has to be generated before the conjuncts it precedes.
+      //
+      // The common `b * 2` takes the CSE path of FilterExec. The overflowing `y + 1` is not
+      // common because `GeneratePredicate` (codegen off) evaluates common subexpressions first.
+      val cseQuery = "SELECT a FROM t WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND " +
+        "y + 1 > 0 AND b * 2 > 0 AND b * 2 > -1"
+      def multiplyExactCount(filterExecCSE: Boolean): Int = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(cseQuery).queryExecution.executedPlan)
+          "multiplyExact".r.findAllIn(code).length
+        }
+      }
+      assert(multiplyExactCount(filterExecCSE = true) < multiplyExactCount(filterExecCSE = false))
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        withNullConjunctConfs(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+          skipsAtNull =>
+            // `isnotnull(x)` is inferred from `x > 0` and precedes the overflowing conjunct.
+            checkAnswer(
+              sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x > 0"),
+              Row(0))
+            // A user-written check that no other conjunct references.
+            checkAnswer(
+              sql("SELECT a FROM t WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND y + 1 > 0"),
+              Row(0))
+            // The same with a common subexpression.
+            checkAnswer(sql(cseQuery), Row(0))
+            // A check written after the overflowing conjunct stays after it.
+            val checkAfter =
+              "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x IS NOT NULL"
+            if (skipsAtNull) {
+              checkAnswer(sql(checkAfter), Row(0))
+            } else {
+              checkArithmeticOverflow(checkAfter)
+            }
+        }
+      }
+      // The aggregate FILTER clause goes through the same code generation.
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        checkAnswer(
+          sql("SELECT count(1) FILTER (WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND " +
+            "y + 1 > 0) FROM t"),
+          Row(1))
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter checks IS NOT NULL on an expression after the conjunct it follows") {
+    withNullConjunctTable(Int.MaxValue) {
+      // `cast(s AS INT)` raises on the first row. With codegen on, a check on an expression is
+      // generated just before the first conjunct that contains the expression (SPARK-51356),
+      // even when it is written after that conjunct. After a NULL conjunct, it has to stay after
+      // the conjunct, as with codegen off: `coalesce(b, cast(s AS INT)) < 0` is FALSE on the row
+      // and drops it before the check raises, while `coalesce(b, cast(s AS INT)) < 2` is TRUE and
+      // the check raises after it. The second row passes the latter condition.
+      //
+      // The common `b * 2` takes the CSE path of FilterExec.
+      def query(conjunct: String, cse: Boolean): String = {
+        val common = if (cse) "b * 2 > 0 AND b * 2 > -1 AND " else ""
+        s"SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND $common$conjunct AND " +
+          "cast(s AS INT) IS NOT NULL"
+      }
+      def multiplyExactCount(filterExecCSE: Boolean): Int = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val plan = sql(query("coalesce(b, cast(s AS INT)) < 0", cse = true)).queryExecution
+          "multiplyExact".r.findAllIn(codegenString(plan.executedPlan)).length
+        }
+      }
+      assert(multiplyExactCount(filterExecCSE = true) < multiplyExactCount(filterExecCSE = false))
+
+      Seq(true, false).foreach { cse =>
+        withNullConjunctConfs(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> cse.toString) {
+          skipsAtNull =>
+            checkAnswer(sql(query("coalesce(b, cast(s AS INT)) < 0", cse)), Seq.empty)
+            val raising = query("coalesce(b, cast(s AS INT)) < 2", cse)
+            if (skipsAtNull) {
+              checkAnswer(sql(raising), Row(0))
+            } else {
+              checkError(
+                exception = intercept[SparkNumberFormatException] {
+                  sql(raising).collect()
+                },
+                condition = "CAST_INVALID_INPUT",
+                parameters = Map(
+                  "expression" -> "'x'",
+                  "sourceType" -> "\"STRING\"",
+                  "targetType" -> "\"INT\"",
+                  "ansiConfig" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+                context = ExpectedContext(
+                  fragment = "cast(s AS INT)",
+                  start = raising.lastIndexOf("cast(s AS INT)"),
+                  stop = raising.lastIndexOf("cast(s AS INT)") + 13))
+            }
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: an aggregate FILTER evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      val query = "SELECT count(a) FILTER (WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0) FROM t"

Review Comment:
   Confirmed: `AggregateCodegenSupport` emits the common subexpressions of the update expressions before the per-function `FILTER` block, while `AggregationIterator` wraps the update in `If(filter, ...)`, so `avg(y + 1) FILTER (WHERE b < 0)` raises only with codegen on. Pre-existing and outside the condition itself, so I filed SPARK-60393 and listed it under "Not fixed here".



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -186,13 +290,24 @@ trait GeneratePredicateHelper extends PredicateHelper {
     // This has the property of not doing redundant IsNotNull checks and taking better advantage of
     // short-circuiting, not loading attributes until they are needed.
     // This is very perf sensitive.
+    // With the NULL-conjunct flag, once a predicate that can be NULL is generated, the IsNotNull
+    // checks that precede a later predicate in the condition are generated before it, see
+    // `precedingNotNullChecks`, and a check on an expression that is written after the predicate
+    // is not generated before it, see the nested checks below.
     // NOTE: `FilterExec.doConsume`'s CSE branch inlines the same interleaving so it can

Review Comment:
   Agreed, the two emitters should consume one precomputed sequence of checks and conjuncts, and SPARK-60329 and SPARK-60377 would then be one edit each. Filed SPARK-60394 for the refactoring; I would rather land this fix first, since the sequence depends on the placement rules settled here, and base the two follow-ups on the refactoring.



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

Posted by "MaxGekk (via GitHub)" <gi...@apache.org>.
MaxGekk closed pull request #59573: [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL
URL: https://github.com/apache/spark/pull/59573


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   cc @cloud-fan @LuciferYang @srielau @dongjoon-hyun @uros-b
   This fixes a whole-stage codegen on/off inconsistency in `FilterExec` and aggregate `FILTER`. A NULL conjunct short-circuited codegen but not the interpreted path. It touches the `FilterExec` CSE branch, and the SQL-standard reasoning plus a comparison with 9 other DBMSs are in the description. Could you take a look?


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   The AI review requested by @HyukjinKwon finished.
   
   *Generated by Omnigent on Databricks*
   
   <!-- SPARK_OSS_REVIEW_REQUEST 33602823173 -->


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -215,42 +323,44 @@ trait GeneratePredicateHelper extends PredicateHelper {
             if !generatedIsNotNullChecks(idx) && !n.isInstanceOf[Attribute] &&

Review Comment:
   Thank you for catching this, @LuciferYang. I reproduced it on the first row of `withNullConjunctTable(Int.MaxValue)`: with the flag, `cast(s AS INT) IS NOT NULL` is generated before `coalesce(b, cast(s AS INT)) < 0`, although `And.eval` never reaches it when that conjunct is FALSE. I will skip the nested branch, here and in the CSE branch, once a nullable conjunct has been generated, and add a test for this shape.
   
   Note that the hoisting itself predates this PR. On a row where the first conjunct is TRUE, e.g. `(0, 1, 0, 1, 'x')`, the same query raises `CAST_INVALID_INPUT` under codegen with the legacy config on and off, while the interpreted mode returns no rows, because the check is hoisted before the second conjunct regardless of the flag. The nested branch should hoist a check only when it is written before the conjunct, i.e. only the checks with an index below `precedingNotNulls(k)`. I opened SPARK-60377 for that, to keep this PR focused on the NULL conjunct.



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

Posted by "SEPURI-SAI-KRISHNA (via GitHub)" <gi...@apache.org>.
SEPURI-SAI-KRISHNA commented on PR #59573:
URL: https://github.com/apache/spark/pull/59573#issuecomment-6239088347

   Not a committer, just read through this one closely and built it at `69318f692f3`. The fix reads correct to me. Two small notes.
   
   In "We considered the opposite fix ...", the closing "That path is kept available through the legacy config" reads as if the config gives the skip-at-NULL semantics that fix would have, but it only changes generated code: with it set the row is skipped with codegen on, while codegen off still raises. The precise version is in the first section ("The interpreted path is unchanged in both modes"), so this is only about that one sentence, which would carry into the commit message.
   
   On "Join conditions ... are not changed": an inner join pushes the single-side conjuncts into a `Filter`, so I checked this with a left outer join, where the condition stays on the join, and the legacy config makes no difference there. Matches what you describe.
   


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -148,6 +148,52 @@ trait GeneratePredicateHelper extends PredicateHelper {
       nonNullAttrExprIds)
   }
 
+  /**
+   * Returns a fresh name for the flag that records a conjunct evaluated to NULL, or None if the
+   * row is dropped at the first NULL conjunct (the legacy behavior) or none of `conjuncts`, bound
+   * with the nullability they are generated with, can be NULL.
+   *
+   * A row is kept only when the whole condition is TRUE, so both FALSE and NULL drop it. Only
+   * a FALSE conjunct decides the result of the AND on its own, though: after a NULL one, the
+   * remaining conjuncts must still be evaluated, as in `And.eval`, so that an error they raise
+   * on the row is raised (SPARK-60315). The flag drops the row once all of them are evaluated.
+   */
+  protected def nullConjunctFlag(
+      ctx: CodegenContext,
+      conjuncts: Seq[Expression]): Option[String] = {
+    if (conf.legacyFilterShortCircuitOnNullConjunct || !conjuncts.exists(_.nullable)) {
+      None
+    } else {
+      Some(ctx.freshName("nullConjunct"))
+    }
+  }
+
+  /**
+   * Generates the check that drops the row when the conjunct evaluated into `ev` is FALSE, or
+   * when it is NULL and `nullFlag` is None. With `nullFlag`, a NULL conjunct sets the flag.
+   */
+  protected def conjunctCheck(ev: ExprCode, nullable: Boolean, nullFlag: Option[String]): String = {
+    (nullable, nullFlag) match {
+      case (false, _) => s"if (!${ev.value}) continue;"
+      case (true, None) => s"if (${ev.isNull} || !${ev.value}) continue;"
+      case (true, Some(flag)) =>
+        s"if (${ev.isNull}) { $flag = true; } else if (!${ev.value}) continue;"

Review Comment:
   **Non-blocking (P2):** With the flag, a NULL conjunct no longer ends the row's evaluation. The IsNotNull checks from `notNullPreds`, however, are still emitted lazily, in both `generatePredicateCode` and the CSE branch of `doConsume`: just before the first conjunct that references their column, or after all conjuncts. With whole-stage codegen off, `And` evaluates the condition in order, and the `isnotnull(...)` filters that `InferFiltersFromConstraints` prepends come first.
   
   Example, in ANSI mode, on a row `(a, b, y, x) = (NULL, 1, 2147483647, NULL)` that reaches the filter:
   
   ```sql
   SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x > 0
   ```
   
   The optimizer prepends `isnotnull(y)` and `isnotnull(x)`. With codegen off, evaluation stops at `isnotnull(x)` = FALSE and the row is dropped. With this PR, codegen sets the flag on the NULL conjunct, passes the `isnotnull(y)` check emitted before `y + 1 > 0`, evaluates `y + 1`, and fails with `ARITHMETIC_OVERFLOW`, because `isnotnull(x)` is only checked before `x > 0`. Before the PR, both modes dropped the row without an error. A user-written guard placed first behaves the same way: in `WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND y + 1 > 0`, no other conjunct references `x`, so `isnotnull(x)` is emitted after all of them. The aggregate `FILTER` clause goes through `generatePredicateCode` and has the same problem.
   
   So with the default config, some queries now fail only with whole-stage codegen on. That is the inverse of SPARK-60315, and it contradicts the migration guide's statement that this is what Spark "already did with whole-stage codegen disabled". The new tests miss it because `NOT (a > 0 AND b > 0)` infers no `IsNotNull`, and no later conjunct references a column that is NULL on the overflowing row.
   
   This extends an [existing discussion](https://github.com/apache/spark/pull/59573#issuecomment-6233128726).
   
   **Recommended change:** In both FilterExec predicate codegen paths, generatePredicateCode (which the aggregate FILTER clause also uses) and the CSE branch of FilterExec.doConsume, when a NULL-conjunct flag is generated, emit before each conjunct that follows the first nullable conjunct, and before that conjunct's column loads or CSE precomputes, every not-yet-emitted IsNotNull check on a plain column that precedes it in the condition's conjunct order. Keep each conjunct's position from splitConjunctivePredicates when the condition is partitioned into IsNotNull checks and other conjuncts. Add whole-stage codegen filter regression tests for an inferred IsNotNull, a user-written IsNotNull written first, and a user-written IsNotNull written after the overflowing conjunct, each on a column that is NULL on the row where that conjunct overflows.
   
   **Why this works:** With codegen off, the condition is evaluated in its conjunct order, and the physical condition keeps that order: InferFiltersFromConstraints puts the inferred isnotnull(...) filters first, and a user-written IsNotNull stays where it was written. So a FALSE plain-column isnotnull drops the row before any conjunct that follows it, but not before a conjunct that comes earlier. Once a nullable conjunct can fall through, whole-stage codegen must not reach a later conjunct before the plain-column IsNotNull checks that precede it in that order, and must not run a check before a conjunct that precedes the check. Emitting the pending preceding checks just before each conjunct that follows the first nullable one meets both rules: a FALSE check placed earlier in the condition drops the row first, and a check placed after the conjunct still runs after it, so the conjunct's error is raised in both modes. Plain-column checks are never NULL and cannot throw, so running them e
 arlier changes no result. Checks already emitted, complex IsNotNull(expr) checks, and code with no flag keep their current placement.
   
   **Scope:** When a NULL-conjunct flag is in play, emit each pending plain-column IsNotNull check just before the first later conjunct that it precedes in the condition, in both lockstep FilterExec predicate codegen paths, and add regression tests for the codegen-only failure and for a check written after the failing conjunct.
   
   **Compatibility:** Outside the NULL-flag case, IsNotNull checks keep their lazy placement just before the first conjunct that references them; codegen-off evaluation is unchanged; a NULL-conjunct row with no FALSE IsNotNull ahead of a raising later conjunct in the condition still raises that conjunct's error, as the PR intends; and a plain-column IsNotNull written after that conjunct still runs after it.
   
   **Risks:** Moving the checks earlier loads their columns on rows that a later FALSE conjunct would have rejected first. The cost is limited to plain-column null checks and applies only when a flag is generated. The change must not move any complex IsNotNull(expr), whose inner expression can throw. The two lockstep paths both need each conjunct's condition position. If only one path carries it, the IsNotNull checks are reordered in only one branch, and the non-CSE and CSE paths disagree for the same filter.
   
   **Constraints:** The non-CSE and CSE paths must make the same change, because their comments require them to stay in lockstep. With spark.sql.legacy.filterShortCircuitOnNullConjunct set to true, or when no conjunct can be NULL after binding, the generated predicate code must stay exactly as the PR emits it today. Every IsNotNull check must still run before any CSE precompute or conjunct that references its column under tightened nullability. The new placement must not run a plain-column IsNotNull check before a conjunct that precedes it in the condition; the existing placement just before the first conjunct that references its column is unchanged.
   
   **Exclusions:**
   - Complex IsNotNull(expr) checks stay where they are, so one that is FALSE on a non-null input can still be reached after a raising conjunct; this narrower residual is not repaired here.
   - The older divergence where codegen checks a user-written IsNotNull earlier than its position in the condition, and therefore skips a raising conjunct that codegen off evaluates, is left unchanged.
   
   **Success:** With whole-stage codegen on and the legacy flag off, `SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x > 0` drops the row (NULL, 1, 2147483647, NULL) without an error, as with codegen off. The same holds for `WHERE x IS NOT NULL AND NOT (a > 0 AND b > 0) AND y + 1 > 0` and for the same guard in an aggregate FILTER clause. `SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND x IS NOT NULL` still fails with ARITHMETIC_OVERFLOW on the row (NULL, 1, 2147483647, NULL) with whole-stage codegen on and off, because the user-written check follows the overflowing conjunct. The existing SPARK-60315 error-versus-skip matrix and generated-code assertions still hold, and the generated code is unchanged when no conjunct can be NULL or when the legacy flag is set.
   
   **Verification:**
   - **Regression:** With whole-stage codegen on and off and the legacy flag off, a filter whose first conjunct is NULL on a row, whose later conjunct overflows on that row, and whose plain-column IsNotNull (inferred from a later comparison, or written first by the user) is FALSE on that row drops the row without an error in both the non-CSE and the filterExec CSE paths.
   - **Regression:** With whole-stage codegen on and off and the legacy flag off, a filter whose first conjunct is NULL on a row, whose later conjunct overflows on that row, and whose user-written plain-column IsNotNull follows the overflowing conjunct and is FALSE on that row still fails with the overflow error in both the non-CSE and the filterExec CSE paths.
   - **Regression:** An aggregate FILTER clause with a user-written IsNotNull guard first, a NULL second conjunct, and an overflowing third conjunct returns the same count with whole-stage codegen on as with it off, without an error.
   - **Compatibility:** With the legacy flag set, or when no conjunct can be NULL after binding, the generated predicate code matches the current output, and the existing error-versus-skip results across whole-stage codegen on/off and legacy on/off are unchanged.



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/WholeStageCodegenSuite.scala:
##########
@@ -2814,4 +2815,144 @@ class WholeStageCodegenSuite extends SharedSparkSession
       }
     }
   }
+
+  // A Parquet table, so that the filter is not evaluated by the optimizer on a local relation.
+  // On the first row `NOT (a > 0 AND b > 0)` is NULL, and no `isnotnull(a)` is inferred from it.
+  // The second row passes every condition of the tests below, except `b < 0`. The rows can be
+  // written to different files, so the filter is not pushed down, which would skip the first
+  // row's file by its statistics.
+  private def withNullConjunctTable(y: Int)(f: => Unit): Unit = {
+    withTable("t") {
+      sql("CREATE TABLE t USING parquet AS " +
+        s"SELECT * FROM VALUES (NULL, 1, $y), (0, 1, 0) AS v(a, b, y)")
+      f
+    }
+  }
+
+  // Runs `f` with whole-stage codegen on and off and with the legacy behavior on and off, and
+  // passes it whether the filter drops a row at its first NULL conjunct.
+  private def withNullConjunctConfs(otherConfs: (String, String)*)(f: Boolean => Unit): Unit = {
+    for (codegen <- Seq(true, false); legacy <- Seq(true, false)) {
+      withSQLConf(otherConfs ++ Seq(
+          SQLConf.PARQUET_FILTER_PUSHDOWN_ENABLED.key -> "false",
+          SQLConf.WHOLESTAGE_CODEGEN_ENABLED.key -> codegen.toString,
+          SQLConf.LEGACY_FILTER_SHORT_CIRCUIT_ON_NULL_CONJUNCT.key -> legacy.toString): _*) {
+        withClue(s"wholeStage=$codegen, legacy=$legacy: ") {
+          f(codegen && legacy)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one") {
+    withNullConjunctTable(Int.MaxValue) {
+      // A common subexpression after the NULL conjunct takes the CSE path of FilterExec.
+      val cseQuery = "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0 AND y + 1 > -1"
+      // The CSE path evaluates the common `y + 1` once, the non-CSE path at each use. Both
+      // generate the flag that keeps the row's evaluation going after a NULL conjunct.
+      def cseQueryCode(filterExecCSE: Boolean): String = {
+        withSQLConf(
+            SQLConf.ANSI_ENABLED.key -> "true",
+            SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE.toString) {
+          val code = codegenString(sql(cseQuery).queryExecution.executedPlan)
+          assert(code.contains("nullConjunct"))
+          code
+        }
+      }
+      assert("addExact".r.findAllIn(cseQueryCode(filterExecCSE = true)).length <
+        "addExact".r.findAllIn(cseQueryCode(filterExecCSE = false)).length)
+
+      Seq("true", "false").foreach { filterExecCSE =>
+        Seq(
+          "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y + 1 > 0",
+          cseQuery
+        ).foreach { query =>
+          withNullConjunctConfs(
+              SQLConf.ANSI_ENABLED.key -> "true",
+              SQLConf.SUBEXPRESSION_ELIMINATION_FILTER_EXEC_ENABLED.key -> filterExecCSE) {
+            skipsAtNull =>
+              if (skipsAtNull) {
+                checkAnswer(sql(query), Row(0))
+              } else {
+                checkError(
+                  exception = intercept[SparkArithmeticException] {
+                    sql(query).collect()
+                  },
+                  condition = "ARITHMETIC_OVERFLOW",
+                  parameters = Map(
+                    "message" -> "overflow",
+                    "alternative" -> " Use 'try_add' to tolerate overflow and return NULL instead.",
+                    "config" -> toSQLConf(SQLConf.ANSI_ENABLED.key)),
+                  context = ExpectedContext(
+                    fragment = "y + 1",
+                    start = query.indexOf("y + 1"),
+                    stop = query.indexOf("y + 1") + 4))
+              }
+          }
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter evaluates the conjuncts after a NULL one in non-ANSI mode") {
+    // Year-month interval arithmetic raises on overflow in every mode.
+    withNullConjunctTable(178956970) {
+      val query = "SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND " +
+        "make_ym_interval(y, 0) + INTERVAL '1' YEAR > INTERVAL '0' YEAR"
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "false") { skipsAtNull =>
+        if (skipsAtNull) {
+          checkAnswer(sql(query), Row(0))
+        } else {
+          checkError(
+            exception = intercept[SparkArithmeticException] {
+              sql(query).collect()
+            },
+            condition = "INTERVAL_ARITHMETIC_OVERFLOW.WITH_SUGGESTION",
+            parameters = Map("functionName" -> toSQLId("try_add")))
+        }
+      }
+    }
+  }
+
+  test("SPARK-60315: a filter drops a row with a NULL conjunct and stops at a FALSE one") {
+    withNullConjunctTable(Int.MaxValue) {
+      withNullConjunctConfs(SQLConf.ANSI_ENABLED.key -> "true") { _ =>
+        // The first row is dropped after its last conjunct is evaluated.
+        checkAnswer(sql("SELECT a FROM t WHERE NOT (a > 0 AND b > 0) AND y > -1"), Row(0))

Review Comment:
   **Nit (P3):** This drop assertion, like the FALSE-stop one after it, has no common subexpression, so it only exercises `generatePredicateCode`. The only query that takes the FilterExec CSE branch is `cseQuery` in the first test, and it runs only on the `Int.MaxValue` table. With the legacy config off, it raises before the CSE branch's own trailing `if (flag) continue;` (`withNullConjunctFlag(predCode + "\n" + leftoverNotNull, nullFlag)` in `doConsume`). With it on, no flag is generated. The `nullConjunct` substring check only shows that the flag exists. If that branch stopped dropping a row whose condition is NULL, the suite would still pass, even though the comments require the branch to be kept in lockstep with `generatePredicateCode` by hand. To close the gap, run `cseQuery` against `withNullConjunctTable(0)` with filterExec CSE on, and check that it returns `Row(0)` in every `withNullConjunctConfs` combination.
   
   **Verification:**
   - **Behavior:** With filterExec subexpression elimination on, a filter with a NULL first conjunct followed by conjuncts sharing a non-cheap common subexpression returns, on non-overflowing data, only the rows whose condition is TRUE in every combination of whole-stage codegen and the legacy flag, and the default configuration's generated code takes the CSE path.



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   **Merge Summary:**
   - merged into master https://github.com/apache/spark/commit/930c571628d7ca0bd94e455b1532e3fc2bbdca3a
   - merged into branch-4.x https://github.com/apache/spark/commit/ba2dec7f05df27ef6b1a8d6427bc7be6ffdc8542
   
   *Posted by `merge_spark_pr.py`*


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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/basicPhysicalOperators.scala:
##########
@@ -156,6 +202,10 @@ trait GeneratePredicateHelper extends PredicateHelper {
       notNullPreds: Seq[Expression],
       otherPreds: Seq[Expression],
       nonNullAttrExprIds: Seq[ExprId]): String = {
+    // The IsNotNull checks are never NULL, so only `otherPreds` can need the flag.
+    val nullFlag =
+      nullConjunctFlag(ctx, otherPreds.map(BindReferences.bindReference(_, outputAttrs)))

Review Comment:
   **Nit (P3):** This map binds every other predicate against outputAttrs, but genPredicate binds each one against the same schema again. BindReferences traverses the expression and initializes an AttributeSeq ordinal map for each attribute-referencing bind, so the extra pass adds driver CPU and allocation that scales with both conjunct count and schema width. It runs even when the legacy setting makes nullConjunctFlag return None. Could we retain these bound expressions for predicate emission, pairing them with the originals needed for reference and null-check interleaving? This concerns code generation overhead in the helper, including aggregate FILTER, rather than per-row evaluation or CSE reuse.
   
   **Verification:**
   - **Inspection:** Inspect preparation and emission to verify the same bound predicate is used for nullable inspection and body generation, with no second per-predicate output-schema bind.
   - **Compatibility:** Compare generated predicate semantics and output/error behavior for legacy true/false and nullable/nonnullable conditions, including aggregate FILTER; CSE route behavior remains unchanged.



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


Re: [PR] [SPARK-60315][SQL] Fix inconsistent filter behavior with whole-stage codegen on and off when a conjunct is NULL [spark]

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

   Thank you @MaxGekk and @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