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

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


The following commit(s) were added to refs/heads/branch-4.x by this push:
     new a793e7c8d577 [SPARK-60822][SQL] Fix ALTER TABLE ADD CHECK CONSTRAINT 
altering the underlying table when the target is a view
a793e7c8d577 is described below

commit a793e7c8d577c54e77f5d2bf24127227b2eaee10
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Tue Nov 3 10:20:43 2026 -0800

    [SPARK-60822][SQL] Fix ALTER TABLE ADD CHECK CONSTRAINT altering the 
underlying table when the target is a view
    
    ### What changes were proposed in this pull request?
    
    This PR makes `ALTER TABLE ... ADD CONSTRAINT ... CHECK (...)` fail with 
`EXPECT_TABLE_NOT_VIEW.NO_ALTERNATIVE` when the target is a view. `UNIQUE`, 
`PRIMARY KEY`, and `FOREIGN KEY` already fail this way.
    
    `AddCheckConstraint` reads its target to validate the existing data. For 
that reason the parser gives it an `UnresolvedRelation` instead of the 
`UnresolvedTable` that the other constraint types use, and that relation can 
resolve to a view. `ResolveRelations` now rejects the target as soon as the 
relation is resolved:
    
    - `SubqueryAlias(_, View)` for a temporary, global temporary, or persistent 
view, including a view in a V2 catalog.
    - `MetricViewPlaceholder` for a metric view, which is not wrapped in a 
`SubqueryAlias`.
    
    The check is in `ResolveRelations` rather than `CheckAnalysis` because 
`ResolveSessionCatalog` runs before `CheckAnalysis` and fails on the table 
under the view.
    
    ### Why are the changes needed?
    
    Spark did not reject a view here, so the outcome depended on what the view 
reads. When the view only projects or filters a Data Source V2 table, the 
statement added an enforced constraint to that table:
    
    ```sql
    CREATE TABLE testcat.ns.t (id INT) USING foo;
    INSERT INTO testcat.ns.t VALUES (-1), (10);
    CREATE TEMPORARY VIEW tv AS SELECT * FROM testcat.ns.t WHERE id > 5;
    
    -- Issue 1: It should fail because `tv` is a view, but it succeeds and adds
    -- `CHECK (id > 0)` to `testcat.ns.t`, which has the row -1.
    ALTER TABLE tv ADD CONSTRAINT c CHECK (id > 0);
    ```
    
    The constraint was validated only against the rows of the view. A view that 
renames a column caused a worse result: the table stored a condition on a 
column it does not have, and every later write to the table failed with 
`UNRESOLVED_COLUMN`.
    
    Other views failed with errors that did not describe the problem:
    
    | The view reads | Error before this PR |
    |---|---|
    | A V1 table | `UNSUPPORTED_FEATURE.TABLE_OPERATION` naming the underlying 
table |
    | A file path, through a DataFrame temporary view | 
`java.util.NoSuchElementException: None.get` |
    | Anything else, such as `range()` or a join | `INTERNAL_ERROR` (`No plan 
for AddCheckConstraint`) |
    | A V2 table, through a metric view | `INTERNAL_ERROR` (`No plan for 
AddCheckConstraint`) |
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes. All of the statements above now fail with 
`EXPECT_TABLE_NOT_VIEW.NO_ALTERNATIVE`:
    
    ```
    [EXPECT_TABLE_NOT_VIEW.NO_ALTERNATIVE] 'ALTER TABLE ... ADD CONSTRAINT' 
expects a table but `tv` is a view. SQLSTATE: 42809
    ```
    
    This is a change compared to the released Spark 4.1, 4.2, and 4.3, which 
have the same parser and planner code. A statement that used to succeed on a 
view over a Data Source V2 table now fails, so this PR adds an entry to the SQL 
migration guide. Constraints that were already added this way stay on the table.
    
    If a view's definition no longer resolves, for example because its table 
was dropped, `CHECK` still reports `TABLE_OR_VIEW_NOT_FOUND`. This is because 
the view body is resolved before the target is checked.
    
    ### How was this patch tested?
    
    Pass the CIs with the newly added test cases in `CheckConstraintSuite`. 
Without this change, the view test fails because no exception is thrown, and 
the metric view test fails with `INTERNAL_ERROR`.
    
    ```
    build/sbt 'sql/testOnly *command.v2.CheckConstraintSuite'
    ```
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Opus 5.5
    
    Closes #60040 from dongjoon-hyun/SPARK-60822.
    
    Authored-by: Dongjoon Hyun <[email protected]>
    Signed-off-by: Dongjoon Hyun <[email protected]>
    (cherry picked from commit 5eaa53ad744934d140a6194223fe87e7e25b8421)
    Signed-off-by: Dongjoon Hyun <[email protected]>
---
 docs/sql-migration-guide.md                        |  1 +
 .../spark/sql/catalyst/analysis/Analyzer.scala     | 10 +++++
 .../command/v2/CheckConstraintSuite.scala          | 47 ++++++++++++++++++++++
 3 files changed, 58 insertions(+)

diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index ebf71c04ce4b..73cdc3cc6c5f 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -68,6 +68,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 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, `ALTER TABLE ... ADD CONSTRAINT` with a CHECK constraint 
fails with `EXPECT_TABLE_NOT_VIEW.NO_ALTERNATIVE` when the target is a view, as 
it does with the other constraint types. Previously, when the view only 
projected or filtered a Data Source V2 table, the statement added the 
constraint to that table after validating only the rows of the view 
(SPARK-60822). To add the constraint, run the statement on the table.
 - 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  [...]
 - Since Spark 4.4, a function created by `CREATE FUNCTION ... AS 'class'` 
whose class implements 
`org.apache.spark.sql.connector.catalog.functions.UnboundFunction` and extends 
either a Hive UDF class (such as `UDF` or `GenericUDF`) or 
`org.apache.spark.sql.expressions.UserDefinedAggregateFunction` runs as a 
DataSource V2 function instead of a Hive UDF or Spark UDAF, including functions 
created in earlier versions. Its result type, argument coercion, and null 
handling are defined by the V [...]
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
index a6bbb75d22af..b377ac1176aa 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
@@ -63,6 +63,7 @@ import 
org.apache.spark.sql.execution.datasources.v2.{ChangelogTable, DataSource
 import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.internal.SQLConf.{PartitionOverwriteMode, 
StoreAssignmentPolicy}
 import org.apache.spark.sql.internal.connector.{SchemaAlignmentConfig, 
SupportsConfigurableSchemaAlignment, V1Function}
+import org.apache.spark.sql.metricview.logical.MetricViewPlaceholder
 import org.apache.spark.sql.types._
 import org.apache.spark.sql.util.CaseInsensitiveStringMap
 import org.apache.spark.util.ArrayImplicits._
@@ -1359,6 +1360,15 @@ class Analyzer(
           case table => table
         }.getOrElse(u)
 
+      // The child of `AddCheckConstraint` reads its target to validate the 
existing data, so the
+      // target is an `UnresolvedRelation` that can resolve to a view, unlike 
`UnresolvedTable`.
+      case a @ AddCheckConstraint(Filter(_, SubqueryAlias(_, v: View)), _) =>
+        throw QueryCompilationErrors.expectTableNotViewError(
+          v.desc.fullIdent, "ALTER TABLE ... ADD CONSTRAINT", 
suggestAlternative = false, a)
+      case a @ AddCheckConstraint(Filter(_, m: MetricViewPlaceholder), _) =>
+        throw QueryCompilationErrors.expectTableNotViewError(
+          m.metadata.fullIdent, "ALTER TABLE ... ADD CONSTRAINT", 
suggestAlternative = false, a)
+
       case u @ UnresolvedView(identifier, cmd, allowTemp, suggestAlternative) 
=>
         lookupTableOrView(identifier, viewOnly = true).map {
           case _: ResolvedTempView if !allowTemp =>
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 783df3e94127..96049e1d180d 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
@@ -33,6 +33,7 @@ import 
org.apache.spark.sql.connector.catalog.functions.{ScalarFunction, Unbound
 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.metricview.serde.{AssetSource, Column, 
DimensionExpression, MeasureExpression, MetricView, MetricViewFactory}
 import org.apache.spark.sql.types.{DataType, StringType}
 import org.apache.spark.unsafe.types.UTF8String
 
@@ -629,6 +630,52 @@ class CheckConstraintSuite extends QueryTest with 
CommandSuiteBase with DDLComma
     }
   }
 
+  private def checkAddToViewError(view: String, viewName: String): Unit = {
+    val query = s"ALTER TABLE $view ADD CONSTRAINT c1 CHECK (id > 0)"
+    checkError(
+      exception = intercept[AnalysisException] {
+        sql(query)
+      },
+      condition = "EXPECT_TABLE_NOT_VIEW.NO_ALTERNATIVE",
+      sqlState = "42809",
+      parameters = Map("viewName" -> viewName, "operation" -> "ALTER TABLE ... 
ADD CONSTRAINT"),
+      context = ExpectedContext(fragment = query, start = 0, stop = 
query.length - 1))
+  }
+
+  test("SPARK-60822: Check constraint cannot be added to a view") {
+    withNamespaceAndTable("ns", "tbl", nonPartitionCatalog) { t =>
+      sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+      sql(s"INSERT INTO $t VALUES (-1), (10)")
+      withTempView("tv") {
+        withView("v") {
+          // A view that hides the violating row of its table, and one that no 
table backs.
+          sql(s"CREATE TEMPORARY VIEW tv AS SELECT * FROM $t WHERE id > 5")
+          sql("CREATE VIEW v AS SELECT id FROM range(3)")
+          checkAddToViewError("tv", "`tv`")
+          checkAddToViewError("v", "`spark_catalog`.`default`.`v`")
+        }
+      }
+      assert(loadTable(nonPartitionCatalog, "ns", "tbl").constraints.isEmpty)
+    }
+  }
+
+  test("SPARK-60822: Check constraint cannot be added to a metric view") {
+    withNamespaceAndTable("ns", "tbl", nonPartitionCatalog) { t =>
+      sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+      val yaml = MetricViewFactory.toYAML(MetricView(
+        "0.1",
+        AssetSource(t),
+        None,
+        Seq(
+          Column("id", DimensionExpression("id"), 0),
+          Column("cnt", MeasureExpression("count(1)"), 1))))
+      withView("mv") {
+        sql(s"CREATE VIEW mv WITH METRICS LANGUAGE YAML AS $$$$\n$yaml\n$$$$")
+        checkAddToViewError("mv", "`spark_catalog`.`default`.`mv`")
+      }
+    }
+  }
+
   test("Check constraint violation on table insert - top level column") {
     withNamespaceAndTable("ns", "tbl", nonPartitionCatalog) { t =>
       sql(s"CREATE TABLE $t (id INT, CONSTRAINT positive_id CHECK (id > 0), " +


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

Reply via email to