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 a84782240ad6 [SPARK-60781][SQL] Reject subqueries in CHECK constraint
conditions
a84782240ad6 is described below
commit a84782240ad66a28cd9f0e3e9032f06d87db68e9
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Sun Nov 1 11:20:37 2026 -0800
[SPARK-60781][SQL] Reject subqueries in CHECK constraint conditions
### What changes were proposed in this pull request?
This PR rejects a CHECK constraint whose condition contains a subquery with
a new error condition, `SUBQUERY_IN_CHECK_CONSTRAINT` (SQLSTATE `42621`), in
`ALTER TABLE ... ADD CONSTRAINT`, `CREATE TABLE`, and `REPLACE TABLE`.
- `CheckConstraint.failIfContainsSubquery` raises the error.
- `ResolveTableSpec` calls it for `CREATE TABLE` and `REPLACE TABLE` before
its `childrenResolved` check.
- `CheckAnalysis` calls it for `ALTER TABLE ... ADD CONSTRAINT` in the
first top-down traversal once the table is resolved, because the parser also
puts the condition in the child `Filter` of the command, and the bottom-up
traversal visits that `Filter` first.
The write path is not changed, so a constraint that is already stored is
enforced as before.
### Why are the changes needed?
The Javadoc of `Check` says that the search condition cannot contain
subqueries, but the analyzer does not enforce it. `ResolveSubquery` does not
visit these commands, so the result depends on the shape of the subquery. The
following results are from a V2 catalog, and `REPLACE TABLE` takes the same
path as `CREATE TABLE`.
| Condition | `ALTER TABLE` | `CREATE TABLE` |
|---|---|---|
| `id > (SELECT 0)` | `INTERNAL_ERROR` | `INTERNAL_ERROR` |
| `EXISTS (SELECT 1)` | `INTERNAL_ERROR` | `INTERNAL_ERROR` |
| `id IN (SELECT 5)` | `INTERNAL_ERROR` | `INTERNAL_ERROR` |
| `EXISTS (SELECT 1 FROM t2)` where `t2` exists | `TABLE_OR_VIEW_NOT_FOUND`
| `TABLE_OR_VIEW_NOT_FOUND` |
| `id > (SELECT 0 AS a)` | Accepted | Accepted |
| `EXISTS (SELECT 1 AS a)` | `UNSUPPORTED_IN_EXISTS_SUBQUERY` | Accepted |
The internal error is `Found the unresolved operator: 'Project
[unresolvedalias(0)]`. The accepted conditions contain a subquery that is
resolved when it is parsed, so it passes the analysis and the table stores the
condition. Every later write evaluates that subquery.
### Does this PR introduce _any_ user-facing change?
Yes. A CHECK condition with a subquery now fails with the following error
instead of the results above.
```
[SUBQUERY_IN_CHECK_CONSTRAINT] The check constraint `id > (SELECT 0)`
contains a subquery. Check constraints cannot contain subqueries. SQLSTATE:
42621
```
- DDL that used to succeed, such as `CHECK (id > (SELECT 0 AS a))`, now
fails. The migration guide has an entry for this change.
- This error is now reported ahead of other errors in the condition, such
as an unresolved column. A missing table in `ALTER TABLE` is still reported
first.
- In `ALTER TABLE`, a subquery in a lambda function keeps failing with
`UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.HIGHER_ORDER_FUNCTION`.
### How was this patch tested?
Added two tests to `CheckConstraintSuite`. They fail with `INTERNAL_ERROR`
without the fix.
```
build/sbt 'sql/testOnly *command.v2.CheckConstraintSuite'
build/sbt 'core/testOnly *SparkThrowableSuite'
```
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Opus 5.5
Closes #59999 from dongjoon-hyun/SPARK-60781.
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 | 1 +
.../sql/catalyst/analysis/CheckAnalysis.scala | 6 +++
.../sql/catalyst/analysis/ResolveTableSpec.scala | 8 ++++
.../sql/catalyst/expressions/constraints.scala | 9 +++++
.../command/v2/CheckConstraintSuite.scala | 45 ++++++++++++++++++++++
6 files changed, 75 insertions(+)
diff --git a/common/utils/src/main/resources/error/error-conditions.json
b/common/utils/src/main/resources/error/error-conditions.json
index 1cd4360d4ee5..8400bbdf2144 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -8383,6 +8383,12 @@
],
"sqlState" : "22023"
},
+ "SUBQUERY_IN_CHECK_CONSTRAINT" : {
+ "message" : [
+ "The check constraint `<checkCondition>` contains a subquery. Check
constraints cannot contain subqueries."
+ ],
+ "sqlState" : "42621"
+ },
"SUM_OF_LIMIT_AND_OFFSET_EXCEEDS_MAX_INT" : {
"message" : [
"The sum of the LIMIT clause and the OFFSET clause must not be greater
than the maximum 32-bit integer value (2,147,483,647) but found limit =
<limit>, offset = <offset>."
diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index 2d4bcec04262..cdd0bb2a0dbd 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -69,6 +69,7 @@ license: |
- 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, 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 [...]
## Upgrading from Spark SQL 4.2 to 4.3
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
index 1dad9d634136..476e84127db6 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
@@ -417,6 +417,12 @@ trait CheckAnalysis extends LookupCatalog with
QueryErrorsBase with PlanToString
// Here we perform additional checks for them.
checkIfSelfReferenceIsPlacedCorrectly(unionLoop, unionLoop.id)
+ // The child `Filter` holds the same condition. Check it first so that
an error of the
+ // subquery in the `Filter` does not hide that the constraint cannot
have a subquery. A
+ // missing table is still reported first, by the bottom-up traversal.
+ case AddCheckConstraint(Filter(_, relation), check) if relation.resolved
=>
+ check.failIfContainsSubquery()
+
case _ =>
}
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 5371a87432ba..af8bd19ed9ab 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
@@ -61,6 +61,14 @@ object ResolveTableSpec extends Rule[LogicalPlan] {
input: LogicalPlan,
tableSpec: TableSpecBase,
withNewSpec: TableSpecBase => LogicalPlan): LogicalPlan = tableSpec
match {
+ // Check this before `childrenResolved`: `ResolveSubquery` skips these
commands, so the subquery
+ // may never resolve, and `toV2Constraint` below would drop one that needs
no resolution.
+ case u: UnresolvedTableSpec if
u.constraints.exists(SubqueryExpression.hasSubquery) =>
+ u.constraints.foreach {
+ case check: CheckConstraint => check.failIfContainsSubquery()
+ case _ =>
+ }
+ input
case u: UnresolvedTableSpec if u.childrenResolved =>
val newOptions: Seq[(String, String)] = u.optionExpression.options.map {
case (key: String, null) =>
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 fb910c6fc82c..c250da545a15 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
@@ -153,6 +153,15 @@ case class CheckConstraint(
}
}
+ /** Fails the analysis if the condition contains a subquery, which `Check`
does not allow. */
+ def failIfContainsSubquery(): Unit = {
+ if (SubqueryExpression.hasSubquery(child)) {
+ child.failAnalysis(
+ errorClass = "SUBQUERY_IN_CHECK_CONSTRAINT",
+ messageParameters = Map("checkCondition" -> condition))
+ }
+ }
+
def toV2Constraint: Constraint = {
val predicate = new V2ExpressionBuilder(child,
true).buildPredicate().orNull
val enforced = userProvidedCharacteristic.enforced.getOrElse(true)
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 9a286a14b8dd..583ba974de34 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
@@ -142,6 +142,51 @@ class CheckConstraintSuite extends QueryTest with
CommandSuiteBase with DDLComma
}
}
+ private def checkSubqueryError(query: String, condition: String): Unit = {
+ checkError(
+ exception = intercept[AnalysisException] {
+ sql(query)
+ },
+ condition = "SUBQUERY_IN_CHECK_CONSTRAINT",
+ sqlState = "42621",
+ parameters = Map("checkCondition" -> condition),
+ context = ExpectedContext(fragment = condition))
+ }
+
+ test("SPARK-60781: Subquery in check constraint -- alter table") {
+ withNamespaceAndTable("ns", "tbl", nonPartitionCatalog) { t =>
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ // An unresolved subquery, one that needs no resolution, and one that
fails in the child
+ // `Filter` of the command.
+ Seq("id > (SELECT 0)", "id > (SELECT 0 AS a)", "id > (SELECT 1 FROM
missing)").foreach { c =>
+ checkSubqueryError(s"ALTER TABLE $t ADD CONSTRAINT c1 CHECK ($c)", c)
+ }
+ assert(loadTable(nonPartitionCatalog, "ns", "tbl").constraints.isEmpty)
+ // A missing table is reported first.
+ val missing = s"$nonPartitionCatalog.ns.missing"
+ checkError(
+ exception = intercept[AnalysisException] {
+ sql(s"ALTER TABLE $missing ADD CONSTRAINT c1 CHECK (id > (SELECT
0))")
+ },
+ condition = "TABLE_OR_VIEW_NOT_FOUND",
+ parameters = Map("relationName" ->
s"`$nonPartitionCatalog`.`ns`.`missing`"),
+ context = ExpectedContext(fragment = missing))
+ }
+ }
+
+ test("SPARK-60781: Subquery in check constraint -- create and replace
table") {
+ // An unresolved subquery and one that needs no resolution.
+ Seq("id > (SELECT 0)", "id > (SELECT 0 AS a)").foreach { c =>
+ withNamespaceAndTable("ns", "tbl", nonPartitionCatalog) { t =>
+ val schema = s"(id INT, CONSTRAINT c1 CHECK ($c)) $defaultUsing"
+ checkSubqueryError(s"CREATE TABLE $t $schema", c)
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ checkSubqueryError(s"REPLACE TABLE $t $schema", c)
+ assert(loadTable(nonPartitionCatalog, "ns", "tbl").constraints.isEmpty)
+ }
+ }
+ }
+
private def getCheckConstraint(table: Table): Check = {
assert(table.constraints.length == 1)
assert(table.constraints.head.isInstanceOf[Check])
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]