This is an automated email from the ASF dual-hosted git repository.

dongjoon-hyun pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/master by this push:
     new 319cc71bf953 [SPARK-60800][SQL] Reject aggregate and window functions 
in DSv2 CHECK constraints
319cc71bf953 is described below

commit 319cc71bf953b147387d9518fc40c61e9ca7fb94
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Mon Nov 2 08:30:27 2026 -0800

    [SPARK-60800][SQL] Reject aggregate and window functions in DSv2 CHECK 
constraints
    
    ### What changes were proposed in this pull request?
    
    This PR makes `CREATE TABLE` and `REPLACE TABLE` on a Data Source V2 table 
reject a CHECK constraint whose condition calls an aggregate or window 
function. They fail with the new error condition 
`AGGREGATE_OR_WINDOW_FUNCTION_IN_CHECK_CONSTRAINT`, and the query context of 
the error points at the call.
    
    1. `ResolveTableSpec` looks for these expressions in the resolved condition:
       - `AggregateExpression`: built-in and user-defined aggregate functions, 
including Hive and V2 aggregate functions.
       - `Grouping` and `GroupingID`: the aggregate functions `grouping` and 
`grouping_id`.
       - `WindowExpression`: any function with an `OVER` clause.
       - `WindowFunction`: a window function without an `OVER` clause, such as 
`row_number()`.
    2. `ResolveTableSpec` does this before 
`CheckConstraint.failIfCallsUserDefinedFunction`, so a user-defined aggregate 
function fails like a built-in one. The `V2Aggregator` branch of that method is 
removed because `CREATE TABLE` and `REPLACE TABLE` now fail earlier, and `ALTER 
TABLE` already fails at its `Filter`.
    3. The SQL migration guide describes this change.
    
    `ALTER TABLE ... ADD CONSTRAINT` already rejects these conditions, and this 
PR does not change its errors. The check is in the DDL only, so a write to a 
table with an existing constraint fails as before.
    
    The errors of `ALTER TABLE` are not reused because they describe a `WHERE` 
clause that the statement does not have, and the first one shows the internal 
form of the condition:
    
    ```
    [INVALID_WHERE_CONDITION] The WHERE condition "(NOT (max(id) > 0))" 
contains invalid expressions: max(cat.ns.t.id).
    [WINDOW_FUNCTION_NOT_ALLOWED_IN_CLAUSE] It is not allowed to use window 
functions inside WHERE clause.
    ```
    
    ### Why are the changes needed?
    
    The `Check` Javadoc says that a condition cannot contain aggregates, but 
`CREATE TABLE` and `REPLACE TABLE` accept them. No write to the table can 
succeed after that. A window function fails in the same way:
    
    ```sql
    CREATE TABLE cat.ns.t1 (id INT, CONSTRAINT c1 CHECK (max(id) > 0)) USING 
foo;  -- succeeds
    INSERT INTO cat.ns.t1 VALUES (1);  -- INVALID_WHERE_CONDITION
    
    CREATE TABLE cat.ns.t2 (id INT, CONSTRAINT c1 CHECK (row_number() OVER 
(ORDER BY id) > 0)) USING foo;  -- succeeds
    INSERT INTO cat.ns.t2 VALUES (1);  -- WINDOW_FUNCTION_NOT_ALLOWED_IN_CLAUSE
    ```
    
    https://github.com/apache/spark/pull/59992 rejected user-defined functions 
in these statements, but not built-in aggregate and window functions.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes.
    
    - Before, `CREATE TABLE` and `REPLACE TABLE` accepted these conditions, and 
every later write to the table failed. After, the statements fail:
    
      ```
      [AGGREGATE_OR_WINDOW_FUNCTION_IN_CHECK_CONSTRAINT] The check constraint 
`max(id) > 0` calls an aggregate or window function. Check constraints apply to 
each row and cannot call these functions. SQLSTATE: 42621
      ```
    
    - Compared to the unreleased master branch, a user-defined aggregate 
function in `CREATE TABLE` and `REPLACE TABLE` fails with this error instead of 
`USER_DEFINED_FUNCTION_IN_CHECK_CONSTRAINT`.
    - `CREATE TABLE` on a V1 table with such a condition fails with this error 
instead of `UNSUPPORTED_FEATURE.TABLE_OPERATION`.
    
    Existing constraints that call these functions are not rejected, and a 
write to their tables keeps failing with the same error. Released Spark 
versions accept these conditions since Apache Spark 4.1.0.
    
    ### How was this patch tested?
    
    Pass the CIs with the newly added test case in `CheckConstraintSuite`. It 
covers `CREATE TABLE` and `REPLACE TABLE` for a built-in aggregate function, 
`grouping`, `grouping_id`, a window function with and without an `OVER` clause, 
a function that is not a window function with an `OVER` clause, a Scala 
aggregate function, and a V2 aggregate function.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Opus 5.5
    
    Closes #60005 from dongjoon-hyun/SPARK-60800.
    
    Authored-by: Dongjoon Hyun <[email protected]>
    Signed-off-by: Dongjoon Hyun <[email protected]>
---
 .../src/main/resources/error/error-conditions.json |  6 ++++
 docs/sql-migration-guide.md                        |  3 +-
 .../sql/catalyst/analysis/ResolveTableSpec.scala   | 10 ++++++
 .../sql/catalyst/expressions/constraints.scala     |  2 --
 .../command/v2/CheckConstraintSuite.scala          | 40 +++++++++++++++++++---
 5 files changed, 53 insertions(+), 8 deletions(-)

diff --git a/common/utils/src/main/resources/error/error-conditions.json 
b/common/utils/src/main/resources/error/error-conditions.json
index 8400bbdf2144..17253772da03 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -11,6 +11,12 @@
     ],
     "sqlState" : "42845"
   },
+  "AGGREGATE_OR_WINDOW_FUNCTION_IN_CHECK_CONSTRAINT" : {
+    "message" : [
+      "The check constraint `<checkCondition>` calls an aggregate or window 
function. Check constraints apply to each row and cannot call these functions."
+    ],
+    "sqlState" : "42621"
+  },
   "AGGREGATE_OUT_OF_MEMORY" : {
     "message" : [
       "No enough memory for aggregation"
diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index 5df6b23eae28..fdde6419834a 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -70,7 +70,8 @@ license: |
 - Since Spark 4.4, when a format is specified, `to_timestamp`, 
`try_to_timestamp`, and `to_timestamp_ltz` cast a numeric first argument to a 
string and parse it with the format, for example `to_timestamp(20201231, 
'yyyyMMdd')` returns `2020-12-31 00:00:00`. This matches the existing behavior 
when `spark.sql.timestampType` is `TIMESTAMP_NTZ`. Previously, with the default 
`TIMESTAMP_LTZ` timestamp type, such calls failed with `INTERNAL_ERROR`. Note 
that the string form of the number is par [...]
 - Since Spark 4.4, the Spark Thrift Server logs a warning at startup when 
`hive.server2.enable.doAs` is `true` and `hive.server2.authentication` verifies 
user identities (any recognized type other than `NONE`/`NOSASL`), because 
executor-side data access still runs as the server's own service identity 
rather than the connecting user (SPARK-5159). Set 
`spark.sql.hive.thriftServer.allowIneffectiveDoAs` to `true` to acknowledge the 
limitation and silence the warning. Spark 5.0 is expected to [...]
 - Since Spark 4.4, `UPDATE` and `MERGE` on a Data Source V2 table fail with 
`DATATYPE_MISMATCH.INVALID_ROW_LEVEL_OPERATION_ASSIGNMENTS` when they assign to 
a metadata column of the table. This includes `UPDATE SET *` and `INSERT *` in 
`MERGE WITH SCHEMA EVOLUTION` when the source has a column with the name of a 
metadata column of the target table. Previously, these assignments passed 
analysis and were usually ignored. To restore the previous behavior, set 
`spark.sql.legacy.ignoreMetadata [...]
-- Since Spark 4.4, `CREATE TABLE`, `REPLACE TABLE`, and `ALTER TABLE ... ADD 
CONSTRAINT` on a Data Source V2 table fail with 
`USER_DEFINED_FUNCTION_IN_CHECK_CONSTRAINT` when the condition of a CHECK 
constraint calls a user-defined function, including a Data Source V2 function. 
`ALTER TABLE ... ADD CONSTRAINT` keeps failing with `UNSUPPORTED_SQL_UDF_USAGE` 
on a SQL function and with `INVALID_WHERE_CONDITION` on an aggregate function. 
Previously, a write could resolve the function name in  [...]
+- Since Spark 4.4, `CREATE TABLE`, `REPLACE TABLE`, and `ALTER TABLE ... ADD 
CONSTRAINT` on a Data Source V2 table fail with 
`USER_DEFINED_FUNCTION_IN_CHECK_CONSTRAINT` when the condition of a CHECK 
constraint calls a user-defined scalar function, including a Data Source V2 
function. `ALTER TABLE ... ADD CONSTRAINT` keeps failing with 
`UNSUPPORTED_SQL_UDF_USAGE` on a SQL function. Previously, a write could 
resolve the function name in its own session, so that it enforced another rule 
or  [...]
+- Since Spark 4.4, `CREATE TABLE` and `REPLACE TABLE` on a Data Source V2 
table fail with `AGGREGATE_OR_WINDOW_FUNCTION_IN_CHECK_CONSTRAINT` when the 
condition of a CHECK constraint calls an aggregate or window function, 
including a user-defined aggregate function, `grouping`, and `grouping_id`. 
`ALTER TABLE ... ADD CONSTRAINT` keeps failing with its existing errors, such 
as `INVALID_WHERE_CONDITION` and `WINDOW_FUNCTION_NOT_ALLOWED_IN_CLAUSE`. 
Previously, these statements succeeded and  [...]
 - Since Spark 4.4, a write to a Data Source V2 table that parses the SQL text 
of a CHECK constraint binds an unqualified built-in function name to 
`system.builtin`, regardless of the SQL path and 
`spark.sql.functionResolution.sessionOrder` (SPARK-60777). Previously, an 
existing constraint could call another function with the same name.
 - Since Spark 4.4, `CREATE TABLE`, `REPLACE TABLE`, and `ALTER TABLE ... ADD 
CONSTRAINT` on a Data Source V2 table fail with `SUBQUERY_IN_CHECK_CONSTRAINT` 
when the condition of a CHECK constraint contains a subquery. `ALTER TABLE ... 
ADD CONSTRAINT` keeps failing with 
`UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.HIGHER_ORDER_FUNCTION` on a subquery 
in a lambda function. Previously, most of these statements failed with 
`INTERNAL_ERROR`, but a subquery that needed no resolution, such as `id  [...]
 
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveTableSpec.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveTableSpec.scala
index af8bd19ed9ab..59c9f40ebe8e 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveTableSpec.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/ResolveTableSpec.scala
@@ -19,6 +19,7 @@ package org.apache.spark.sql.catalyst.analysis
 
 import org.apache.spark.SparkThrowable
 import org.apache.spark.sql.catalyst.expressions._
+import org.apache.spark.sql.catalyst.expressions.aggregate.AggregateExpression
 import org.apache.spark.sql.catalyst.optimizer.ComputeCurrentTime
 import org.apache.spark.sql.catalyst.plans.logical._
 import org.apache.spark.sql.catalyst.rules.Rule
@@ -103,6 +104,15 @@ object ResolveTableSpec extends Rule[LogicalPlan] {
               messageParameters = Map("checkCondition" -> check.condition)
             )
           }
+          check.child.collectFirst {
+            case e @ (_: AggregateExpression | _: Grouping | _: GroupingID |
+                _: WindowExpression | _: WindowFunction) => e
+          }.foreach { call =>
+            call.failAnalysis(
+              errorClass = "AGGREGATE_OR_WINDOW_FUNCTION_IN_CHECK_CONSTRAINT",
+              messageParameters = Map("checkCondition" -> check.condition)
+            )
+          }
           check.failIfCallsUserDefinedFunction()
         case _ =>
       }
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/constraints.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/constraints.scala
index c250da545a15..306ddc9ad825 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/constraints.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/constraints.scala
@@ -20,7 +20,6 @@ import java.util.UUID
 
 import org.apache.spark.sql.catalyst.InternalRow
 import org.apache.spark.sql.catalyst.analysis.{AnalysisErrorAt, 
SQLFunctionExpression, TypeCheckResult, UnresolvedException}
-import org.apache.spark.sql.catalyst.expressions.aggregate.V2Aggregator
 import org.apache.spark.sql.catalyst.expressions.codegen.{Block, 
CodegenContext, ExprCode, FalseLiteral, TrueLiteral}
 import org.apache.spark.sql.catalyst.expressions.codegen.Block.BlockHelper
 import org.apache.spark.sql.catalyst.expressions.objects.{Invoke, StaticInvoke}
@@ -145,7 +144,6 @@ case class CheckConstraint(
         (i, toSQLId(Seq(i.scalarFunction.get.name)))
       case i @ Invoke(Literal(f: ScalarFunction[_], _), _, _, _, _, _, _, _) =>
         (i, toSQLId(Seq(f.name)))
-      case a: V2Aggregator[_, _] => (a, toSQLId(Seq(a.aggrFunc.name)))
     }.foreach { case (call, functionName) =>
       call.failAnalysis(
         errorClass = "USER_DEFINED_FUNCTION_IN_CHECK_CONSTRAINT",
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/CheckConstraintSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/CheckConstraintSuite.scala
index 583ba974de34..783df3e94127 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/CheckConstraintSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/CheckConstraintSuite.scala
@@ -21,7 +21,7 @@ import 
test.org.apache.spark.sql.connector.catalog.functions.JavaAverage
 import 
test.org.apache.spark.sql.connector.catalog.functions.JavaStrLen.JavaStrLenStaticMagic
 
 import org.apache.spark.SparkRuntimeException
-import org.apache.spark.sql.{AnalysisException, QueryTest, Row}
+import org.apache.spark.sql.{AnalysisException, MapTypeBufferAgg, QueryTest, 
Row}
 import org.apache.spark.sql.catalyst.InternalRow
 import org.apache.spark.sql.catalyst.plans.logical.Filter
 import org.apache.spark.sql.catalyst.util.AttributeNameParser
@@ -31,6 +31,7 @@ import org.apache.spark.sql.connector.catalog.{Identifier, 
InMemoryCatalog, Tabl
 import org.apache.spark.sql.connector.catalog.constraints.{Check, Constraint}
 import org.apache.spark.sql.connector.catalog.functions.{ScalarFunction, 
UnboundFunction}
 import org.apache.spark.sql.execution.command.DDLCommandTestUtils
+import org.apache.spark.sql.functions.udaf
 import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.types.{DataType, StringType}
 import org.apache.spark.unsafe.types.UTF8String
@@ -295,11 +296,40 @@ class CheckConstraintSuite extends QueryTest with 
CommandSuiteBase with DDLComma
     }
   }
 
-  test("SPARK-60777: Check constraint cannot call a V2 aggregate function") {
+  test("SPARK-60800: Check constraint cannot call an aggregate or window 
function") {
+    def checkRejected(call: String): Unit = {
+      withNamespaceAndTable("ns", "tbl", nonPartitionCatalog) { t =>
+        sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+        val cond = s"$call > 0"
+        Seq(
+          s"CREATE TABLE ${t}_2 (id INT, CONSTRAINT c1 CHECK ($cond)) 
$defaultUsing",
+          s"REPLACE TABLE $t (id INT, CONSTRAINT c1 CHECK ($cond)) 
$defaultUsing"
+        ).foreach { query =>
+          checkError(
+            exception = intercept[AnalysisException](sql(query)),
+            condition = "AGGREGATE_OR_WINDOW_FUNCTION_IN_CHECK_CONSTRAINT",
+            sqlState = "42621",
+            parameters = Map("checkCondition" -> cond),
+            context = ExpectedContext(fragment = call))
+        }
+      }
+    }
+
+    checkRejected("max(id)")
+    // These are aggregate functions, but not an `AggregateExpression`.
+    checkRejected("grouping(id)")
+    checkRejected("grouping_id()")
+    checkRejected("row_number() OVER (ORDER BY id)")
+    // `row_number()` has no `WindowExpression`, and `abs(id)` is not a 
`WindowFunction`.
+    checkRejected("row_number()")
+    checkRejected("abs(id) OVER ()")
+    // A user-defined aggregate function fails in the same way as a built-in 
one.
+    withUserDefinedFunction("tmp_agg" -> true) {
+      spark.udf.register("tmp_agg", udaf(MapTypeBufferAgg))
+      checkRejected("tmp_agg(id)")
+    }
     withV2Function("avg", new JavaAverage) {
-      // ALTER TABLE keeps failing with the error for an aggregate in a filter.
-      val call = s"fun_$catalog.ns.avg(CAST(id AS DOUBLE))"
-      checkUserDefinedFunctionRejected(s"$call > 0", call, "`davg`", inAlter = 
false)
+      checkRejected(s"fun_$catalog.ns.avg(CAST(id AS DOUBLE))")
     }
   }
 


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to