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]

Reply via email to