You are viewing a plain text version of this content. The canonical link for it is here.

Posted to reviews@spark.apache.org by "longvu-db (via GitHub)" <gi...@apache.org> on 2026/06/08 13:23:31 UTC

[PR] [SPARK-54971][DOCS] Document WITH SCHEMA EVOLUTION syntax for SQL INSERT [spark]

longvu-db opened a new pull request, #56370:
URL: https://github.com/apache/spark/pull/56370

### What changes were proposed in this pull request?

[SPARK-54971](https://issues.apache.org/jira/browse/SPARK-54971) (#53732) added the `WITH SCHEMA EVOLUTION` syntax to the SQL `INSERT` command, but did not add user-facing documentation for it. This PR documents the new clause on the `INSERT TABLE` SQL reference page (`docs/sql-ref-syntax-dml-insert-table.md`):

- Adds the optional `[ WITH SCHEMA EVOLUTION ]` clause to both syntax forms, mirroring the grammar where the clause appears right after `INSERT` and before `INTO` / `OVERWRITE`.
- Adds a `WITH SCHEMA EVOLUTION` entry to the Parameters section describing the behavior.

### Why are the changes needed?

The `WITH SCHEMA EVOLUTION` syntax for SQL `INSERT` was introduced without documentation. This page is the reference for the `INSERT` statement, so the new clause should be documented there.

### Does this PR introduce _any_ user-facing change?

No. Documentation-only update.

### How was this patch tested?

Docs-only change. Reviewed the rendered Markdown for correctness.

### Was this patch authored or co-authored using generative AI tooling?

Generated-by: Claude Code (Anthropic)

--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org

Re: [PR] [SPARK-57316][DOCS] Document WITH SCHEMA EVOLUTION and BY NAME for SQL INSERT [spark]

Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.

szehon-ho commented on code in PR #56370:
URL: https://github.com/apache/spark/pull/56370#discussion_r3383033518


##########
docs/sql-ref-syntax-dml-insert-table.md:
##########
@@ -259,6 +270,77 @@ SELECT * FROM students WHERE student_id = 11215017;
 +------------+----------------------+----------+
 ```
 
+##### Insert By Name Using a SELECT Statement
+
+```sql
+CREATE TABLE target (n INT, text STRING, s STRUCT<a INT, b INT>);
+
+-- BY NAME matches the top-level columns by name, so they may be listed in any order.
+-- Nested struct fields are matched by position, so the struct field order in the query
+-- must match the target schema (STRUCT<a INT, b INT>).

Review Comment:
   This note isn't correct: with `BY NAME`, nested struct fields are matched **by name**, not by position. The by-name mode propagates recursively into nested structs (and array-element / map structs), so it's not "top-level by name, nested by position."
   
   In `TableOutputResolver`, a `BY NAME` write goes through `reorderColumnsByName`, and a matched struct column recurses with `byName = true` into `resolveStructType`, which again calls `reorderColumnsByName` on the nested fields. This matches the behavior of the schema-evolution path (`ResolveSchemaEvolution`), which threads the same `isByName` flag into nested structs.
   
   This is verified by `V2WriteAnalysisSuite` "check fields of struct type column": with target struct `{a, b}` and source struct `{x, y}`, the `byName` case *fails* with `INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_FIND_DATA` on `` `col`.`a` `` (it looked up the nested field by name), while the `byPosition` case succeeds mapping `x->a, y->b`.
   
   Also, as written the example can't actually demonstrate the rule, since the source struct `named_struct('a', 1, 'b', 2)` has the same field names *and* order as the target — the result `{1, 2}` is identical either way. Suggest fixing the comment and reordering the nested fields so it's illustrative:
   
   ```sql
   -- BY NAME matches both top-level columns and nested struct fields by name,
   -- so they may be listed in any order in the source query.
   INSERT INTO target BY NAME
       SELECT named_struct('b', 2, 'a', 1) AS s, 0 AS n, 'data' AS text;
   -- s is {1, 2}: nested fields matched by name, not position
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org

Re: [PR] [SPARK-57316][DOCS] Document WITH SCHEMA EVOLUTION syntax for SQL INSERT [spark]

Posted by "johanl-db (via GitHub)" <gi...@apache.org>.

johanl-db commented on PR #56370:
URL: https://github.com/apache/spark/pull/56370#issuecomment-4657314238

   > it looks good, but should we add some more detail?
   > 
   > * Only works with DSV2 data sources supporting this feature
   > * Should we document more specifics like type widening, add new column, and missing source column handling?
   > * And also the behavior for by position vs by name
   
   +1, this should call out that it allows automatically evolving schema to add new columns and change data types based on the connector capability. I wouldn't necessarily explicitly call out it requires DSv2 (since users probably don't know / care about DSv1 vs. DSv2) but just let that fall under 'connector capability'
   
   Also call that columns and nested fields are matched by position between the input and target schema, unless `BY NAME` is specified in which case columns and fields are matched by name.
   
   We can leave more detailed info about e.g. missing columns undocumented for now imo


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org

Re: [PR] [SPARK-57316][DOCS] Document WITH SCHEMA EVOLUTION and BY NAME for SQL INSERT [spark]

Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.

szehon-ho commented on code in PR #56370:
URL: https://github.com/apache/spark/pull/56370#discussion_r3383692063


##########
docs/sql-ref-syntax-dml-insert-table.md:
##########
@@ -259,6 +270,77 @@ SELECT * FROM students WHERE student_id = 11215017;
 +------------+----------------------+----------+
 ```
 
+##### Insert By Name Using a SELECT Statement
+
+```sql
+CREATE TABLE target (n INT, text STRING, s STRUCT<a INT, b INT>);
+
+-- BY NAME matches both top-level columns and nested struct fields by name,
+-- so they may be listed in any order in the source query.
+INSERT INTO target BY NAME
+    SELECT named_struct('b', 2, 'a', 1) AS s, 0 AS n, 'data' AS text;
+-- s is {1, 2}: nested fields matched by name, not position.
+
+SELECT * FROM target;
++---+----+------+
+|  n|text|     s|
++---+----+------+
+|  0|data|{1, 2}|
++---+----+------+
+
+CREATE OR REPLACE TABLE target (n INT, arr ARRAY<STRUCT<a INT, b INT>>);
+
+-- A missing top-level column is filled with its default value (NULL here).
+INSERT INTO target BY NAME
+    SELECT array(named_struct('a', 1, 'b', 2)) AS arr, 0 AS n;
+INSERT INTO target BY NAME
+    SELECT array(named_struct('a', 1, 'b', 2)) AS arr;

Review Comment:
   Minor: this comment sits above both `INSERT` statements, but only the second one (without `n`) actually omits a column — the first supplies both `arr` and `n`. Consider moving it directly above the second `INSERT` so it's clear which statement demonstrates the default fill.



##########
docs/sql-ref-syntax-dml-insert-table.md:
##########
@@ -259,6 +270,77 @@ SELECT * FROM students WHERE student_id = 11215017;
 +------------+----------------------+----------+
 ```
 
+##### Insert By Name Using a SELECT Statement
+
+```sql
+CREATE TABLE target (n INT, text STRING, s STRUCT<a INT, b INT>);
+
+-- BY NAME matches both top-level columns and nested struct fields by name,
+-- so they may be listed in any order in the source query.
+INSERT INTO target BY NAME
+    SELECT named_struct('b', 2, 'a', 1) AS s, 0 AS n, 'data' AS text;
+-- s is {1, 2}: nested fields matched by name, not position.
+
+SELECT * FROM target;
++---+----+------+
+|  n|text|     s|
++---+----+------+
+|  0|data|{1, 2}|
++---+----+------+
+
+CREATE OR REPLACE TABLE target (n INT, arr ARRAY<STRUCT<a INT, b INT>>);
+
+-- A missing top-level column is filled with its default value (NULL here).
+INSERT INTO target BY NAME
+    SELECT array(named_struct('a', 1, 'b', 2)) AS arr, 0 AS n;
+INSERT INTO target BY NAME
+    SELECT array(named_struct('a', 1, 'b', 2)) AS arr;
+
+SELECT * FROM target;
++----+--------+
+|   n|     arr|
++----+--------+
+|   0|[{1, 2}]|
+|NULL|[{1, 2}]|
++----+--------+
+
+-- A source column whose name does not match any target column is an error.
+INSERT INTO target BY NAME
+    SELECT array(named_struct('a', 1, 'b', 2)) AS arr, 0 AS badname;
+Error
+
+-- Duplicate source column names that resolve to the same target column are an error.
+INSERT INTO target BY NAME
+    SELECT array(named_struct('a', 1, 'b', 2)) AS arr, 0 AS n, 1 AS n;
+Error: INSERT_COLUMN_ARITY_MISMATCH.TOO_MANY_DATA_COLUMNS

Review Comment:
   I'd suggest just removing this example. It doesn't demonstrate duplicate-name resolution: the arity check runs before any name matching, so with 3 source columns against the 2-column target you get `TOO_MANY_DATA_COLUMNS` purely on column count — the duplicate `n` is never evaluated. The identical error occurs for a positional insert (`INSERT INTO target SELECT array(...), 0, 1`), so it's neither `BY NAME`-specific nor illustrative of what the comment claims.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org

Re: [PR] [SPARK-57316][DOCS] Document WITH SCHEMA EVOLUTION and BY NAME for SQL INSERT [spark]

Posted by "johanl-db (via GitHub)" <gi...@apache.org>.

johanl-db commented on code in PR #56370:
URL: https://github.com/apache/spark/pull/56370#discussion_r3381711001


##########
docs/sql-ref-syntax-dml-insert-table.md:
##########
@@ -55,6 +61,11 @@ INSERT INTO [ TABLE ] table_identifier REPLACE WHERE boolean_expression query
     - All specified columns should exist in the table and not be duplicated from each other. It includes all columns except the static partition columns.
     - The size of the column list should be exactly the size of the data from `VALUES` clause or query.
 
+* **BY NAME**
+
+    Columns and nested fields are matched by position between the source query and the target
+    table, unless `BY NAME` is specified, in which case columns and fields are matched by name.

Review Comment:
   ```suggestion
       By default, columns and nested fields are matched by position between the source query and the target
       table. With `BY NAME`, columns and nested fields are matched by name, allowing them to be ordered differently in the source query and target table.
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org

Re: [PR] [SPARK-57316][DOCS] Document WITH SCHEMA EVOLUTION and BY NAME for SQL INSERT [spark]

Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.

szehon-ho closed pull request #56370: [SPARK-57316][DOCS] Document WITH SCHEMA EVOLUTION and BY NAME for SQL INSERT
URL: https://github.com/apache/spark/pull/56370


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org

Re: [PR] [SPARK-57316][DOCS] Document WITH SCHEMA EVOLUTION and BY NAME for SQL INSERT [spark]

Posted by "szehon-ho (via GitHub)" <gi...@apache.org>.

szehon-ho commented on PR #56370:
URL: https://github.com/apache/spark/pull/56370#issuecomment-4665286805

   Merged to master, 4.x, thanks!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org