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]