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]