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 6db73ce133cc [SPARK-60857][SQL] Fix DSv2 CREATE/REPLACE TABLE 
accepting a generation expression whose struct field names differ from the 
column type
6db73ce133cc is described below

commit 6db73ce133cc442e1f99c5261ef61df3f63b06dc
Author: Dongjoon Hyun <[email protected]>
AuthorDate: Wed Nov 4 09:48:13 2026 -0800

    [SPARK-60857][SQL] Fix DSv2 CREATE/REPLACE TABLE accepting a generation 
expression whose struct field names differ from the column type
    
    ### What changes were proposed in this pull request?
    
    `GeneratedColumnExpression.validate` now rejects a generation expression 
whose data type names its struct fields differently from the column type, in 
name, including letter case, or in order. It compares the two types with 
`DataType.equalsStructurallyByName`, so the check also covers a struct nested 
in another struct, an array, or a map. It compares the names exactly instead of 
with the resolver of the session that creates the table, because the session 
that writes to the table can be [...]
    
    ```sql
    CREATE TABLE testcat.t (a INT, b INT,
      s STRUCT<x: INT, y: INT> GENERATED ALWAYS AS (named_struct('y', a, 'x', 
b))) USING foo;
    ```
    
    ```
    [UNSUPPORTED_EXPRESSION_GENERATED_COLUMN] Cannot create generated column s 
with generation expression named_struct('y', a, 'x', b) because generation 
expression data type struct<y:int,x:int> names its struct fields differently 
from column data type struct<x:int,y:int>. SQLSTATE: 42621
    ```
    
    An expression that names the fields as the column type does, such as 
`named_struct('x', b, 'y', a)`, is accepted as before. To line the fields up by 
position, cast the expression to the column type, for example `CAST(struct(a, 
b) AS STRUCT<x: INT, y: INT>)`.
    
    ### Why are the changes needed?
    
    A write reads a struct generation expression in two ways that line up the 
fields differently. When Spark auto-fills a generated column of a Data Source 
V2 table, store assignment in `TableOutputResolver` matches the fields of the 
struct to the column type by name, under the resolver of the writing session. 
When the write provides the value, the check that `ResolveTableConstraints` 
adds compares it with the expression by position. `validate` only checks 
`Cast.canUpCast`, which ignores  [...]
    
    ```sql
    INSERT INTO testcat.t (a, b) VALUES (1, 2);                         -- 
auto-fills s = {x: 2, y: 1}
    INSERT INTO testcat.t SELECT * FROM testcat.t;                      -- 
CHECK_CONSTRAINT_VIOLATION
    INSERT INTO testcat.t VALUES (5, 6, named_struct('x', 6, 'y', 5));  -- 
CHECK_CONSTRAINT_VIOLATION
    INSERT INTO testcat.t VALUES (5, 6, named_struct('y', 5, 'x', 6));  -- 
passes, stores s = {x: 5, y: 6}
    ```
    
    A row that Spark auto-filled fails the check when a later write carries it, 
and the check accepts only the value lined up by position, which is not the 
value Spark auto-fills for the same `a` and `b`. A struct nested in another 
struct or in an array behaves the same. If the expression uses other field 
names, such as `struct(a, b)` for the column above, every auto-fill already 
fails with `INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_FIND_DATA`, while the check 
accepts a provided value. A name th [...]
    
    The other way to make the two agree is to line up the fields by name in the 
check, as store assignment does. That needs the resolved type of the 
expression, but the check is built unresolved and analyzed later, so it would 
need a new expression or rule and a copy of the by-name logic of 
`TableOutputResolver`. Even then, an expression with other field names could 
not be auto-filled. Rejecting the expression in `validate` reports the problem 
at `CREATE TABLE` instead of on a later write [...]
    
    This was reported in [a review comment on 
#59965](https://github.com/apache/spark/pull/59965#discussion_r4453208371).
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes. `CREATE TABLE` and `REPLACE TABLE` fail with 
`UNSUPPORTED_EXPRESSION_GENERATED_COLUMN` on such a generation expression, 
which Spark 4.3 and earlier accept. Like the other checks of `validate`, this 
applies to any catalog that supports generated columns. Spark 4.3.0 already 
auto-fills a generated column of a Data Source V2 table with the 
`GENERATE_COLUMN_VALUES_ON_WRITE` capability and checks a provided value, so a 
table that 4.3.0 created can hold such an auto-filled row. Such an [...]
    
    ### How was this patch tested?
    
    Added a test to `GeneratedColumnWriteSuite`. `CREATE TABLE` fails on an 
expression with the fields in another order and on one with the field names in 
another letter case, and with the fields named as in the column type, an 
auto-filled row passes the check when `INSERT INTO ... SELECT * FROM` writes it 
again. Without the fix, the test fails because the `CREATE TABLE` succeeds.
    
    Also checked on `testcat` before and after the fix:
    
    | Generation expression | Column type | Before | After |
    |---|---|---|---|
    | `named_struct('y', a, 'x', b)` | `STRUCT<x: INT, y: INT>` | auto-fill 
`[2,1]`, rewrite fails the check | rejected |
    | same, with `writeTo(...).append()` | same | auto-fill `[2,1]` | rejected |
    | `array(named_struct('y', a, 'x', b))` | `ARRAY<STRUCT<x: INT, y: INT>>` | 
auto-fill `[[2,1]]`, rewrite fails the check | rejected |
    | `named_struct('n', named_struct('y', a, 'x', b))` | `STRUCT<n: STRUCT<x: 
INT, y: INT>>` | auto-fill `[[2,1]]`, rewrite fails the check | rejected |
    | `map(a, named_struct('y', a, 'x', b))` | `MAP<INT, STRUCT<x: INT, y: 
INT>>` | auto-fill `{1 -> [2,1]}`, rewrite fails with `INVALID_ORDERING_TYPE` | 
rejected |
    | `struct(a, b)` or `named_struct('p', a, 'q', b)` | `STRUCT<x: INT, y: 
INT>` | auto-fill fails with `CANNOT_FIND_DATA` | rejected |
    | `named_struct('y', a, 'x', b)` | `STRUCT<x: BIGINT, y: BIGINT>` | 
auto-fill `[2,1]`, rewrite fails with `BINARY_OP_DIFF_TYPES` | rejected |
    | `named_struct('X', b, 'Y', a)` | `STRUCT<x: INT, y: INT>` | auto-fill 
`[2,1]` and rewrite passes in a case-insensitive session; auto-fill fails with 
`CANNOT_FIND_DATA` in a case-sensitive one | rejected |
    | `named_struct('x', b, 'y', a)` | same | auto-fill `[2,1]`, rewrite passes 
| same |
    | `CAST(named_struct('y', a, 'x', b) AS STRUCT<x: INT, y: INT>)` | same | 
auto-fill `[1,2]`, rewrite passes | same |
    
    Related suites pass: `GeneratedColumnExpressionSuite`, `DDLParserSuite`, 
`ColumnSuite`, `DataTypeSuite` in `catalyst`, and `GeneratedColumnWriteSuite`, 
`DataSourceV2SQLSuiteV1Filter`, `DataSourceV2SQLSuiteV2Filter`, 
`DataSourceV2DataFrameSuite`, `CollationSuite` in `sql`.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Opus 5.5
    
    Closes #60075 from dongjoon-hyun/SPARK-60857.
    
    Authored-by: Dongjoon Hyun <[email protected]>
    Signed-off-by: Dongjoon Hyun <[email protected]>
---
 docs/sql-migration-guide.md                        |  1 +
 .../plans/logical/GeneratedColumnExpression.scala  | 10 ++++++
 .../sql/connector/GeneratedColumnWriteSuite.scala  | 37 ++++++++++++++++++++++
 3 files changed, 48 insertions(+)

diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index 828be69dc31c..9288c532b66e 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -78,6 +78,7 @@ license: |
 - Since Spark 4.4, `CREATE TABLE` and `REPLACE TABLE` fail with 
`UNSUPPORTED_EXPRESSION_GENERATED_COLUMN` on a generation expression that the 
session reads differently than a write does, for example with 
`spark.sql.parser.escapedStringLiterals=true` or 
`spark.sql.ansi.enforceReservedKeywords=true`. This also applies to a table 
without the `GENERATE_COLUMN_VALUES_ON_WRITE` capability, which computes the 
values itself (SPARK-60757). To restore the previous behavior, set 
`spark.sql.legacy.u [...]
 - 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, `CREATE TABLE` and `REPLACE TABLE` on a Data Source V2 
table fail with `GENERATOR_IN_CHECK_CONSTRAINT` when the condition of a CHECK 
constraint calls a generator function, such as `explode`, `posexplode`, and 
`inline`. `ALTER TABLE ... ADD CONSTRAINT` keeps failing with 
`UNSUPPORTED_GENERATOR.OUTSIDE_SELECT`. Previously, these statements succeeded 
and every later write to the table failed (SPARK-60821). To run these 
statements, remove the function from the condition. E [...]
+- Since Spark 4.4, `CREATE TABLE` and `REPLACE TABLE` fail with 
`UNSUPPORTED_EXPRESSION_GENERATED_COLUMN` when the data type of a generation 
expression names its struct fields differently from the column type, in name, 
including letter case, or in order, for example `named_struct('y', a, 'x', b)`, 
`named_struct('X', b, 'Y', a)`, or `struct(a, b)` for a `STRUCT<x: INT, y: 
INT>` column. Previously, such an expression was accepted, although Spark lines 
up the struct fields by name, with the [...]
 - 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  [...]
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/GeneratedColumnExpression.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/GeneratedColumnExpression.scala
index 9a9ad6d713ae..079a97aa8528 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/GeneratedColumnExpression.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/GeneratedColumnExpression.scala
@@ -56,6 +56,7 @@ case class GeneratedColumnExpression(
    * - The expression cannot reference other generated columns
    * - The expression must be deterministic
    * - The expression data type can be safely up-cast to the destination 
column data type
+   * - Struct field names match those of the column data type exactly, in order
    * - No subquery expressions
    * - No references to session (temporary) variables
    * - No non-UTF8 binary collation
@@ -121,6 +122,15 @@ case class GeneratedColumnExpression(
           s"is incompatible with column data type 
${targetDataType.simpleString}")
     }
 
+    // Store assignment lines up the struct fields of an auto-filled value 
with the column type by
+    // name, while the check of a provided value lines them up by position, so 
the two must agree.
+    // The names are compared exactly, since store assignment uses the 
writer's resolver.
+    if (!DataType.equalsStructurallyByName(child.dataType, targetDataType, _ 
== _)) {
+      throw unsupportedExpressionError(
+        s"generation expression data type ${child.dataType.simpleString} names 
its struct " +
+          s"fields differently from column data type 
${targetDataType.simpleString}")
+    }
+
     if (child.exists(e => SchemaUtils.hasNonUTF8BinaryCollation(e.dataType))) {
       throw unsupportedExpressionError(
         "generation expression cannot contain non utf8 binary collated string 
type")
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/GeneratedColumnWriteSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/GeneratedColumnWriteSuite.scala
index 525b02bfb98f..6161a1562409 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/GeneratedColumnWriteSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/GeneratedColumnWriteSuite.scala
@@ -1109,6 +1109,43 @@ class GeneratedColumnWriteSuite extends QueryTest with 
DatasourceV2SQLBase {
     }
   }
 
+  test("SPARK-60857: CREATE TABLE rejects a struct generation expression with 
other field names") {
+    val tblName = "my_tab"
+    withTable(s"testcat.$tblName") {
+      // An auto-filled value lines up the struct fields with the column type 
by name under the
+      // writer's resolver, while the check of a provided value lines them up 
by position. So a row
+      // that Spark auto-filled from the first expression would fail the check 
when a later write
+      // carries it, and a case-sensitive writer could not auto-fill the 
second one.
+      Seq(
+        "named_struct('y', a, 'x', b)" -> "struct<y:int,x:int>",
+        "named_struct('X', b, 'Y', a)" -> "struct<X:int,Y:int>").foreach { 
case (expr, exprType) =>
+        checkError(
+          exception = intercept[AnalysisException] {
+            sql(s"""CREATE TABLE testcat.$tblName(
+                   |  a INT,
+                   |  b INT,
+                   |  s STRUCT<x: INT, y: INT> GENERATED ALWAYS AS ($expr)
+                   |) USING foo""".stripMargin)
+          },
+          condition = "UNSUPPORTED_EXPRESSION_GENERATED_COLUMN",
+          parameters = Map(
+            "fieldName" -> "s",
+            "expressionStr" -> expr,
+            "reason" -> (s"generation expression data type $exprType names its 
struct " +
+              "fields differently from column data type struct<x:int,y:int>")))
+      }
+
+      sql(s"""CREATE TABLE testcat.$tblName(
+             |  a INT,
+             |  b INT,
+             |  s STRUCT<x: INT, y: INT> GENERATED ALWAYS AS 
(named_struct('x', b, 'y', a))
+             |) USING foo""".stripMargin)
+      sql(s"INSERT INTO testcat.$tblName(a, b) VALUES (1, 2)")
+      sql(s"INSERT INTO testcat.$tblName SELECT * FROM testcat.$tblName")
+      checkAnswer(spark.table(s"testcat.$tblName"), Seq.fill(2)(Row(1, 2, 
Row(2, 1))))
+    }
+  }
+
   test("generation expression is not exposed in the read schema") {
     val tblName = "my_tab"
     withTable(s"testcat.$tblName") {


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

Reply via email to