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

szehon-ho 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 aaf85747f70d [SPARK-58271][SDP] Allow AUTO CDC clauses in any order
aaf85747f70d is described below

commit aaf85747f70da2a5600ae2d9a154d8dabe7f549e
Author: Andreas Neumann <[email protected]>
AuthorDate: Wed Jul 29 11:42:35 2026 -0700

    [SPARK-58271][SDP] Allow AUTO CDC clauses in any order
    
    ### What changes were proposed in this pull request?
    
    The `AUTO CDC` command previously required its clauses in a fixed order 
after `KEYS (...)`: `APPLY AS DELETE WHEN`, then `SEQUENCE BY`, then `COLUMNS`, 
then `STORED AS SCD TYPE`, then `TRACK HISTORY ON`. This relaxes the grammar so 
the optional clauses form an unordered set, accepted in any order.
    
    - **Grammar** (`SqlBaseParser.g4`): `autoCdcParameters` now matches the 
five clauses as a repeatable alternation `(autoCdcDeleteClause | 
autoCdcSequenceByClause | autoCdcColumnsClause | autoCdcStoredAsClause | 
autoCdcTrackHistoryClause)*` instead of a fixed positional sequence.
    - **`AstBuilder.parseAutoCdcParams`**: reads each clause from the resulting 
list; rejects a clause supplied more than once with `DUPLICATE_CLAUSES` via the 
shared `checkDuplicateClauses` helper; and enforces the still-mandatory 
`SEQUENCE BY` explicitly (the grammar no longer requires it positionally) with 
a targeted error.
    
    ### Why are the changes needed?
    
    As more AUTO CDC options are added, a fixed clause order is hard for users 
to remember. Allowing arbitrary ordering makes the syntax easier to use, 
consistent with how other Spark commands (e.g. `CREATE TABLE`) accept their 
optional clauses in any order.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes, a SQL syntax relaxation.
    - Before: the AUTO CDC clauses had to be written in one fixed order; any 
other order failed with `PARSE_SYNTAX_ERROR`.
    - After: the optional clauses may be written in any order. Supplying the 
same clause twice now fails with `DUPLICATE_CLAUSES` ("Found duplicate clauses: 
<clauseName>."), and omitting the required `SEQUENCE BY` fails with a clear 
"AUTO CDC requires a SEQUENCE BY clause." message. Previously valid statements 
continue to parse unchanged.
    
    This is a relaxation within the unreleased AUTO CDC feature on master; no 
released behavior changes.
    
    ### How was this patch tested?
    
    Updated `AutoCdcParserSuite` (67 tests, all passing):
    
    - The former "wrong clause order" negative tests are now positive tests 
asserting the clauses parse in the new orders, including one statement with a 
fully reversed clause order.
    - Added a duplicate-clause rejection test for each of the five clauses 
(asserting `DUPLICATE_CLAUSES`).
    - Updated the `SEQUENCE BY is required` tests to assert the new targeted 
error.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Generated-by: Claude Code (Opus 4.8)
    
    Closes #57612 from anew/spark-58271-flexible-autocdc-clause-order.
    
    Authored-by: Andreas Neumann <[email protected]>
    Signed-off-by: Szehon Ho <[email protected]>
    (cherry picked from commit 4d670c377ac7eded97012ac6fcf3ed63c8cf6421)
    Signed-off-by: Szehon Ho <[email protected]>
---
 .../spark/sql/catalyst/parser/SqlBaseParser.g4     |  10 +-
 .../spark/sql/catalyst/parser/AstBuilder.scala     |  26 ++-
 .../execution/command/v2/AutoCdcParserSuite.scala  | 218 ++++++++++++++++-----
 3 files changed, 190 insertions(+), 64 deletions(-)

diff --git 
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4 
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
index c591dae38da5..bc6c437a42e2 100644
--- 
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
+++ 
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
@@ -771,11 +771,11 @@ autoCdcBody
 autoCdcParameters
     : FROM source=relationPrimary
         KEYS LEFT_PAREN keys=identifierSeq RIGHT_PAREN
-        autoCdcDeleteClause?
-        autoCdcSequenceByClause
-        autoCdcColumnsClause?
-        autoCdcStoredAsClause?
-        autoCdcTrackHistoryClause?
+        (autoCdcDeleteClause
+        | autoCdcSequenceByClause
+        | autoCdcColumnsClause
+        | autoCdcStoredAsClause
+        | autoCdcTrackHistoryClause)*
     ;
 
 autoCdcDeleteClause
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
index 10c27ad8200c..9920df24b6bc 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
@@ -1416,11 +1416,27 @@ class AstBuilder extends DataTypeAstBuilder
         }
       }
       val keys = 
visitIdentifierSeq(params.keys).map(UnresolvedAttribute.quoted)
-      val deleteCondition = Option(params.autoCdcDeleteClause())
+
+      // The optional clauses may appear in any order after `KEYS (...)`, so 
the grammar accepts
+      // each any number of times; reject an accidental repeat here rather 
than silently taking
+      // the last occurrence.
+      checkDuplicateClauses(params.autoCdcDeleteClause(), "APPLY AS DELETE 
WHEN", params)
+      checkDuplicateClauses(params.autoCdcSequenceByClause(), "SEQUENCE BY", 
params)
+      checkDuplicateClauses(params.autoCdcColumnsClause(), "COLUMNS", params)
+      checkDuplicateClauses(params.autoCdcStoredAsClause(), "STORED AS SCD 
TYPE", params)
+      checkDuplicateClauses(params.autoCdcTrackHistoryClause(), "TRACK HISTORY 
ON", params)
+
+      val deleteCondition = params.autoCdcDeleteClause().asScala.headOption
         .map(c => expression(c.deleteCondition))
-      val sequencing = expression(params.autoCdcSequenceByClause().sequence)
 
-      val columnsClause = Option(params.autoCdcColumnsClause())
+      // SEQUENCE BY is mandatory, but the grammar accepts the clauses as an 
unordered set, so
+      // require it explicitly here.
+      val sequenceByClause = 
params.autoCdcSequenceByClause().asScala.headOption.getOrElse {
+        throw QueryParsingErrors.missingClausesForOperation(params, "SEQUENCE 
BY", "AUTO CDC")
+      }
+      val sequencing = expression(sequenceByClause.sequence)
+
+      val columnsClause = params.autoCdcColumnsClause().asScala.headOption
       val includeColumns = columnsClause.collect {
         case c if c.columns != null =>
           visitIdentifierSeq(c.columns).map(UnresolvedAttribute.quoted)
@@ -1434,7 +1450,7 @@ class AstBuilder extends DataTypeAstBuilder
       // supported; reject anything else (including oversized numeric 
literals) with a clear
       // error rather than a generic parse failure or NumberFormatException. 
Match on the token
       // text rather than parsing to Int so an overflowing literal cannot 
throw.
-      val storedAsScdType = Option(params.autoCdcStoredAsClause()) match {
+      val storedAsScdType = params.autoCdcStoredAsClause().asScala.headOption 
match {
         case Some(c) =>
           c.scdType.getText match {
             case "1" => 1
@@ -1447,7 +1463,7 @@ class AstBuilder extends DataTypeAstBuilder
         case None => 1
       }
 
-      val trackHistoryClause = Option(params.autoCdcTrackHistoryClause())
+      val trackHistoryClause = 
params.autoCdcTrackHistoryClause().asScala.headOption
       val trackHistoryColumns = trackHistoryClause.collect {
         case c if c.trackCols != null =>
           visitIdentifierSeq(c.trackCols).map(UnresolvedAttribute.quoted)
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/AutoCdcParserSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/AutoCdcParserSuite.scala
index 34642497eb57..15c756bd888c 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/AutoCdcParserSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/AutoCdcParserSuite.scala
@@ -307,16 +307,18 @@ class AutoCdcParserSuite extends CommandSuiteBase with 
AnalysisTest {
     assert(cst.trackHistoryExceptColumns.get.map(_.name) == Seq("op"))
   }
 
-  test("AUTO CDC - TRACK HISTORY before STORED AS is not allowed") {
-    intercept[ParseException] {
-      parser.parsePlan(
-        """CREATE FLOW f AS AUTO CDC INTO target
-          |FROM STREAM(source)
-          |KEYS (id)
-          |SEQUENCE BY ts
-          |TRACK HISTORY ON (val)
-          |STORED AS SCD TYPE 2""".stripMargin)
-    }
+  test("AUTO CDC - TRACK HISTORY before STORED AS is allowed") {
+    val plan = parser.parsePlan(
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)
+        |SEQUENCE BY ts
+        |TRACK HISTORY ON (val)
+        |STORED AS SCD TYPE 2""".stripMargin)
+
+    val cdc = 
plan.asInstanceOf[CreateFlowCommand].flowOperation.asInstanceOf[AutoCdcInto]
+    assert(cdc.storedAsScdType == 2)
+    assert(cdc.trackHistoryColumns.get.map(_.name) == Seq("val"))
   }
 
   test("AUTO CDC - STORED AS SCD TYPE 3 is not allowed") {
@@ -592,70 +594,178 @@ class AutoCdcParserSuite extends CommandSuiteBase with 
AnalysisTest {
   // 
---------------------------------------------------------------------------
 
   test("CREATE FLOW AS AUTO CDC INTO - SEQUENCE BY is required") {
+    val sql =
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)""".stripMargin
     checkError(
-      intercept[ParseException] {
-        parser.parsePlan(
-          """CREATE FLOW f AS AUTO CDC INTO target
-            |FROM STREAM(source)
-            |KEYS (id)""".stripMargin)
-      },
-      condition = "PARSE_SYNTAX_ERROR",
-      sqlState = "42601",
-      parameters = Map("error" -> "end of input", "hint" -> "")
+      intercept[ParseException] { parser.parsePlan(sql) },
+      condition = "MISSING_CLAUSES_FOR_OPERATION",
+      parameters = Map("clauses" -> "SEQUENCE BY", "operation" -> "AUTO CDC"),
+      queryContext = Array(ExpectedContext(autoCdcParams(sql), 
sql.indexOf("FROM"), sql.length - 1))
     )
   }
 
   test("CREATE STREAMING TABLE FLOW AUTO CDC - SEQUENCE BY is required") {
+    val sql =
+      """CREATE STREAMING TABLE target
+        |FLOW AUTO CDC
+        |FROM STREAM(source)
+        |KEYS (id)""".stripMargin
     checkError(
-      intercept[ParseException] {
-        parser.parsePlan(
-          """CREATE STREAMING TABLE target
-            |FLOW AUTO CDC
-            |FROM STREAM(source)
-            |KEYS (id)""".stripMargin)
-      },
-      condition = "PARSE_SYNTAX_ERROR",
-      sqlState = "42601",
-      parameters = Map("error" -> "end of input", "hint" -> "")
+      intercept[ParseException] { parser.parsePlan(sql) },
+      condition = "MISSING_CLAUSES_FOR_OPERATION",
+      parameters = Map("clauses" -> "SEQUENCE BY", "operation" -> "AUTO CDC"),
+      queryContext = Array(ExpectedContext(autoCdcParams(sql), 
sql.indexOf("FROM"), sql.length - 1))
     )
   }
 
   // 
---------------------------------------------------------------------------
-  // Error cases: wrong clause order
+  // Clause ordering: the optional clauses may appear in any order
   // 
---------------------------------------------------------------------------
 
-  test("SEQUENCE BY before APPLY AS DELETE is not allowed") {
-    checkError(
-      intercept[ParseException] {
-        parser.parsePlan(
-          """CREATE FLOW f AS AUTO CDC INTO target
-            |FROM STREAM(source)
-            |KEYS (id)
-            |SEQUENCE BY ts
-            |APPLY AS DELETE WHEN a = 1""".stripMargin)
-      },
-      condition = "PARSE_SYNTAX_ERROR",
-      sqlState = "42601",
-      parameters = Map("error" -> "'APPLY'", "hint" -> "")
-    )
+  test("AUTO CDC - SEQUENCE BY before APPLY AS DELETE is allowed") {
+    val plan = parser.parsePlan(
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)
+        |SEQUENCE BY ts
+        |APPLY AS DELETE WHEN a = 1""".stripMargin)
+
+    val cdc = 
plan.asInstanceOf[CreateFlowCommand].flowOperation.asInstanceOf[AutoCdcInto]
+    assert(cdc.sequenceByExpr == UnresolvedAttribute("ts"))
+    assert(cdc.deleteCondition.isDefined)
+    assert(cdc.deleteCondition.get.sql.contains("a"))
   }
 
-  test("COLUMNS before SEQUENCE BY is not allowed") {
+  test("AUTO CDC - COLUMNS before SEQUENCE BY is allowed") {
+    val plan = parser.parsePlan(
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)
+        |COLUMNS (a, b)
+        |SEQUENCE BY ts""".stripMargin)
+
+    val cdc = 
plan.asInstanceOf[CreateFlowCommand].flowOperation.asInstanceOf[AutoCdcInto]
+    assert(cdc.includeColumns.get.map(_.name) == Seq("a", "b"))
+    assert(cdc.sequenceByExpr == UnresolvedAttribute("ts"))
+  }
+
+  test("AUTO CDC - clauses supplied in fully reversed order are all honored") {
+    val plan = parser.parsePlan(
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)
+        |TRACK HISTORY ON (val)
+        |STORED AS SCD TYPE 2
+        |COLUMNS (id, val, ts)
+        |SEQUENCE BY ts
+        |APPLY AS DELETE WHEN is_deleted""".stripMargin)
+
+    val cdc = 
plan.asInstanceOf[CreateFlowCommand].flowOperation.asInstanceOf[AutoCdcInto]
+    assert(cdc.deleteCondition.isDefined)
+    assert(cdc.deleteCondition.get.sql.contains("is_deleted"))
+    assert(cdc.sequenceByExpr == UnresolvedAttribute("ts"))
+    assert(cdc.includeColumns.get.map(_.name) == Seq("id", "val", "ts"))
+    assert(cdc.storedAsScdType == 2)
+    assert(cdc.trackHistoryColumns.get.map(_.name) == Seq("val"))
+  }
+
+  test("CREATE STREAMING TABLE FLOW AUTO CDC - clauses supplied in reversed 
order are honored") {
+    val plan = parser.parsePlan(
+      """CREATE STREAMING TABLE target
+        |FLOW AUTO CDC
+        |FROM STREAM(source)
+        |KEYS (id)
+        |TRACK HISTORY ON (val)
+        |STORED AS SCD TYPE 2
+        |COLUMNS (id, val, ts)
+        |SEQUENCE BY ts
+        |APPLY AS DELETE WHEN is_deleted""".stripMargin)
+
+    val cmd = plan.asInstanceOf[CreateStreamingTableAutoCdc]
+    assert(cmd.deleteCondition.isDefined)
+    assert(cmd.deleteCondition.get.sql.contains("is_deleted"))
+    assert(cmd.sequenceByExpr == UnresolvedAttribute("ts"))
+    assert(cmd.includeColumns.get.map(_.name) == Seq("id", "val", "ts"))
+    assert(cmd.storedAsScdType == 2)
+    assert(cmd.trackHistoryColumns.get.map(_.name) == Seq("val"))
+  }
+
+  // 
---------------------------------------------------------------------------
+  // Error cases: a clause supplied more than once
+  // 
---------------------------------------------------------------------------
+
+  /** Assert a statement fails with DUPLICATE_CLAUSES naming `clauseName`. */
+  private def assertDuplicateClause(sql: String, clauseName: String): Unit = {
     checkError(
       intercept[ParseException] {
-        parser.parsePlan(
-          """CREATE FLOW f AS AUTO CDC INTO target
-            |FROM STREAM(source)
-            |KEYS (id)
-            |COLUMNS a, b
-            |SEQUENCE BY ts""".stripMargin)
+        parser.parsePlan(sql)
       },
-      condition = "PARSE_SYNTAX_ERROR",
-      sqlState = "42601",
-      parameters = Map("error" -> "'COLUMNS'", "hint" -> "")
+      condition = "DUPLICATE_CLAUSES",
+      parameters = Map("clauseName" -> clauseName),
+      queryContext = Array(ExpectedContext(autoCdcParams(sql), 
sql.indexOf("FROM"), sql.length - 1))
     )
   }
 
+  /** The `autoCdcParameters` fragment of `sql`: from `FROM` to the end of the 
statement. */
+  private def autoCdcParams(sql: String): String = 
sql.substring(sql.indexOf("FROM"))
+
+  test("AUTO CDC - duplicate SEQUENCE BY is rejected") {
+    assertDuplicateClause(
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)
+        |SEQUENCE BY ts
+        |SEQUENCE BY ts2""".stripMargin,
+      "SEQUENCE BY")
+  }
+
+  test("AUTO CDC - duplicate COLUMNS is rejected") {
+    assertDuplicateClause(
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)
+        |SEQUENCE BY ts
+        |COLUMNS (a)
+        |COLUMNS (b)""".stripMargin,
+      "COLUMNS")
+  }
+
+  test("AUTO CDC - duplicate APPLY AS DELETE WHEN is rejected") {
+    assertDuplicateClause(
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)
+        |APPLY AS DELETE WHEN a = 1
+        |APPLY AS DELETE WHEN b = 2
+        |SEQUENCE BY ts""".stripMargin,
+      "APPLY AS DELETE WHEN")
+  }
+
+  test("AUTO CDC - duplicate STORED AS SCD TYPE is rejected") {
+    assertDuplicateClause(
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)
+        |SEQUENCE BY ts
+        |STORED AS SCD TYPE 1
+        |STORED AS SCD TYPE 2""".stripMargin,
+      "STORED AS SCD TYPE")
+  }
+
+  test("AUTO CDC - duplicate TRACK HISTORY ON is rejected") {
+    assertDuplicateClause(
+      """CREATE FLOW f AS AUTO CDC INTO target
+        |FROM STREAM(source)
+        |KEYS (id)
+        |SEQUENCE BY ts
+        |STORED AS SCD TYPE 2
+        |TRACK HISTORY ON (a)
+        |TRACK HISTORY ON (b)""".stripMargin,
+      "TRACK HISTORY ON")
+  }
+
   // 
---------------------------------------------------------------------------
   // Error cases: standalone form not supported
   // 
---------------------------------------------------------------------------


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

Reply via email to