You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "finchxxia (via GitHub)" <gi...@apache.org> on 2026/07/13 05:04:16 UTC

[PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

finchxxia opened a new pull request, #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403

   ### Summary
   This PR adds support for Databricks INSERT ... BY NAME syntax.
   - Adds a by_name field to the Insert AST node.
   - Parses BY NAME for the Databricks and Generic dialects.
   - Preserves BY NAME when formatting the AST back to SQL.
   - Adds test coverage for INSERT INTO ... BY NAME with both SELECT and WITH queries.
   - Updates existing Insert AST construction sites with the default by_name: false.
   
   ### Examples
   ```
   INSERT INTO target BY NAME
   SELECT 1 AS a;
   ```
   ```
   INSERT INTO TABLE target BY NAME
   WITH source AS (
       SELECT 1 AS event_data_id
   )
   SELECT event_data_id FROM source;
   ```
   
   ### Testing
   cargo test --test sqlparser_databricks test_databricks_insert_by_name
   Relevant PostgreSQL INSERT regression tests


-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#issuecomment-5565103047

   @finchxxia could you take a look to resolve the conflicts on this branch, otherwise I think this looks good
   


-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#discussion_r3727259871


##########
src/parser/mod.rs:
##########
@@ -18404,7 +18404,9 @@ impl<'a> Parser<'a> {
             let table = self.parse_keyword(Keyword::TABLE);
             let table_object = self.parse_table_object()?;
 
+            // `BY NAME` is an INSERT clause, not a table alias.
             let table_alias = if self.dialect.supports_insert_table_alias()
+                && !self.peek_keywords(&[Keyword::BY, Keyword::NAME])

Review Comment:
   hmm yeah I think it might depend on how that `BY NAME` syntax works. Can we add a test demonstrating the behavior of `INSERT INTO BY NAME SELECT 1 AS a`? Also can we add a doc to the reference describing this syntax. Currently its unclear from the PR alone how the syntax fits with existing grammar the parser covers here otherwise



-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "finchxxia (via GitHub)" <gi...@apache.org>.
finchxxia commented on code in PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#discussion_r3750040945


##########
src/parser/mod.rs:
##########
@@ -18404,7 +18404,9 @@ impl<'a> Parser<'a> {
             let table = self.parse_keyword(Keyword::TABLE);
             let table_object = self.parse_table_object()?;
 
+            // `BY NAME` is an INSERT clause, not a table alias.
             let table_alias = if self.dialect.supports_insert_table_alias()
+                && !self.peek_keywords(&[Keyword::BY, Keyword::NAME])

Review Comment:
   @iffyio done



-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "finchxxia (via GitHub)" <gi...@apache.org>.
finchxxia commented on code in PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#discussion_r3945900350


##########
tests/sqlparser_postgres.rs:
##########
@@ -9663,3 +9666,22 @@ fn parse_right_deep_join_chain() {
     // NATURAL JOIN followed by a constrained join must stay left-associative.
     pg().verified_stmt("SELECT * FROM t0 NATURAL JOIN t1 INNER JOIN t2 ON true");
 }
+
+#[test]
+fn parse_insert_by_name_keywords_as_table_and_alias() {
+    // Without a table name, `BY NAME` is not an INSERT BY NAME clause. PostgreSQL
+    // treats `BY` as the table name and `NAME` as its implicit table alias.
+    match pg().verified_stmt("INSERT INTO BY NAME SELECT 1 AS a") {

Review Comment:
   > sorry I didn't follow, what do we mean by 'fallback parse'? do we mean essentially that `"INSERT INTO BY NAME SELECT 1 AS a"` fails to parse for some dialects currently?
   
   @iffyio yes. In PostgreSQL, BY is parsed as the table name and NAME as its implicit alias; BY NAME is not treated as a clause here



-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#discussion_r3841365936


##########
tests/sqlparser_postgres.rs:
##########
@@ -9663,3 +9666,22 @@ fn parse_right_deep_join_chain() {
     // NATURAL JOIN followed by a constrained join must stay left-associative.
     pg().verified_stmt("SELECT * FROM t0 NATURAL JOIN t1 INNER JOIN t2 ON true");
 }
+
+#[test]
+fn parse_insert_by_name_keywords_as_table_and_alias() {
+    // Without a table name, `BY NAME` is not an INSERT BY NAME clause. PostgreSQL
+    // treats `BY` as the table name and `NAME` as its implicit table alias.
+    match pg().verified_stmt("INSERT INTO BY NAME SELECT 1 AS a") {

Review Comment:
   is this test case specific to postgres? I would imagine that its part of the parse_insert_by_name test in common and that all dialects have the same behavior? (i.e. a table_name is required in order for `BY NAME` to be accepted as a clause so the grammar seems dialect agnostic from what I can tell from the databricks docs)



-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "finchxxia (via GitHub)" <gi...@apache.org>.
finchxxia commented on PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#issuecomment-5164847550

   Hi @iffyio , I have committed some changes as per your suggestions. Could you plz take a look again?
   


-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "finchxxia (via GitHub)" <gi...@apache.org>.
finchxxia commented on code in PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#discussion_r3869439160


##########
tests/sqlparser_postgres.rs:
##########
@@ -9663,3 +9666,22 @@ fn parse_right_deep_join_chain() {
     // NATURAL JOIN followed by a constrained join must stay left-associative.
     pg().verified_stmt("SELECT * FROM t0 NATURAL JOIN t1 INNER JOIN t2 ON true");
 }
+
+#[test]
+fn parse_insert_by_name_keywords_as_table_and_alias() {
+    // Without a table name, `BY NAME` is not an INSERT BY NAME clause. PostgreSQL
+    // treats `BY` as the table name and `NAME` as its implicit table alias.
+    match pg().verified_stmt("INSERT INTO BY NAME SELECT 1 AS a") {

Review Comment:
   > is this test case specific to postgres? I would imagine that its part of the parse_insert_by_name test in common and that all dialects have the same behavior? (i.e. a table_name is required in order for `BY NAME` to be accepted as a clause so the grammar seems dialect agnostic from what I can tell from the databricks docs)
   
   @iffyio  I agree that the requirement for table_name is dialect-agnostic. However, the fallback parse is not identical across all dialects in sqlparser: BY is parsed as the table name and NAME as an implicit alias only for dialects that support INSERT table aliases, currently PostgreSQL and Oracle. I think I can move the test into the common parse_insert_by_name coverage and restrict it to those dialects.



-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio merged PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403


-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "finchxxia (via GitHub)" <gi...@apache.org>.
finchxxia commented on PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#issuecomment-5565188697

   > @finchxxia could you take a look to resolve the conflicts on this branch, otherwise I think this looks good
   
   @iffyio done. Thanks for your help. 


-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "finchxxia (via GitHub)" <gi...@apache.org>.
finchxxia commented on code in PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#discussion_r3717373412


##########
src/parser/mod.rs:
##########
@@ -18404,7 +18404,9 @@ impl<'a> Parser<'a> {
             let table = self.parse_keyword(Keyword::TABLE);
             let table_object = self.parse_table_object()?;
 
+            // `BY NAME` is an INSERT clause, not a table alias.
             let table_alias = if self.dialect.supports_insert_table_alias()
+                && !self.peek_keywords(&[Keyword::BY, Keyword::NAME])

Review Comment:
   @iffyio I see. But in postgresQL dialect,  if we don't add keywords `BY NAME`, `BY` would be recognized as a table alias.
   
   So I think that would be clearer.
   ```
    let mut by_name = self.peek_keywords(&[Keyword::BY, Keyword::NAME]);
    let table_alias = if self.dialect.supports_insert_table_alias()
       && !by_name
       && !self.peek_sub_query()
       && self
           .peek_one_of_keywords(&[Keyword::DEFAULT, Keyword::VALUES])
           .is_none()
   
   // ...
   let partitioned = self.parse_insert_partition()?;
   by_name = self.parse_keywords(&[Keyword::BY, Keyword::NAME]) || by_name;
   ```



-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#discussion_r3703079612


##########
tests/sqlparser_databricks.rs:
##########
@@ -737,3 +737,34 @@ fn parse_cte_without_as() {
         .parse_sql_statements("WITH cte (SELECT 1) SELECT * FROM cte")
         .is_err());
 }
+
+#[test]
+fn test_databricks_insert_by_name() {
+    match databricks_and_generic().verified_stmt("INSERT INTO target BY NAME SELECT 1 AS a") {

Review Comment:
   we can remove the AST assertion and rely on verified_stmt only for these, (AST is already covered in common)



##########
src/parser/mod.rs:
##########
@@ -18404,7 +18404,9 @@ impl<'a> Parser<'a> {
             let table = self.parse_keyword(Keyword::TABLE);
             let table_object = self.parse_table_object()?;
 
+            // `BY NAME` is an INSERT clause, not a table alias.
             let table_alias = if self.dialect.supports_insert_table_alias()
+                && !self.peek_keywords(&[Keyword::BY, Keyword::NAME])

Review Comment:
   oh this pattern looks a bit odd, would it make more sense to do the following?
   ``` rust
   let mut by_name = self.peek_keywords(&[BY, NAME]);
   let table_alias = if ... // unchanged
   
   // ...
   let partitioned = self.parse_insert_partition()?;
   by_name = by_name || self.parse_keywords(&[Keyword::BY, Keyword::NAME]);
   ```
   



##########
tests/sqlparser_common.rs:
##########
@@ -19665,3 +19665,18 @@ fn parse_function_arg_call_chain_no_exponential_blowup() {
     rx.recv_timeout(Duration::from_secs(5))
         .expect("parser should reject this quickly, not loop exponentially");
 }
+
+#[test]
+fn parse_insert_by_name_in_all_dialects() {
+    verified_stmt("INSERT INTO target BY NAME SELECT 1 AS a");

Review Comment:
   maybe we can also add some scenarios with table options that cover around the new option to ensure that parsing `BY NAME` isn't conflicting with other features that show up around it



##########
tests/sqlparser_common.rs:
##########
@@ -19665,3 +19665,18 @@ fn parse_function_arg_call_chain_no_exponential_blowup() {
     rx.recv_timeout(Duration::from_secs(5))
         .expect("parser should reject this quickly, not loop exponentially");
 }
+
+#[test]
+fn parse_insert_by_name_in_all_dialects() {

Review Comment:
   ```suggestion
   fn parse_insert_by_name() {
   ```



-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#discussion_r3926517634


##########
tests/sqlparser_postgres.rs:
##########
@@ -9663,3 +9666,22 @@ fn parse_right_deep_join_chain() {
     // NATURAL JOIN followed by a constrained join must stay left-associative.
     pg().verified_stmt("SELECT * FROM t0 NATURAL JOIN t1 INNER JOIN t2 ON true");
 }
+
+#[test]
+fn parse_insert_by_name_keywords_as_table_and_alias() {
+    // Without a table name, `BY NAME` is not an INSERT BY NAME clause. PostgreSQL
+    // treats `BY` as the table name and `NAME` as its implicit table alias.
+    match pg().verified_stmt("INSERT INTO BY NAME SELECT 1 AS a") {

Review Comment:
   sorry I didn't follow, what do we mean by 'fallback parse'? do we mean essentially that `"INSERT INTO BY NAME SELECT 1 AS a"` fails to parse for some dialects currently?



-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [PR] Databricks: support INSERT BY NAME [datafusion-sqlparser-rs]

Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #2403:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/2403#discussion_r3655813031


##########
src/dialect/mod.rs:
##########
@@ -1353,6 +1353,13 @@ pub trait Dialect: Debug + Any {
         false
     }
 
+    /// Returns true if this dialect supports `INSERT INTO ... BY NAME ...`.
+    ///
+    /// Databricks: <https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-dml-insert-into>
+    fn supports_insert_by_name(&self) -> bool {
+        false
+    }

Review Comment:
   unless the syntax conflicts with other dialects (doesn't seem to be the case?) I think we can skip the dialect method and let the parser be permissive?



##########
src/parser/mod.rs:
##########
@@ -18348,6 +18349,9 @@ impl<'a> Parser<'a> {
                         self.parse_parenthesized_qualified_column_list(Optional, is_mysql)?;
 
                     let partitioned = self.parse_insert_partition()?;
+                    by_name = columns.is_empty()
+                        && self.dialect.supports_insert_by_name()

Review Comment:
   ```suggestion
                       by_name = by_name.is_none()
                           && self.dialect.supports_insert_by_name()
   ```
   I think we can drop the columns check? its not clear why it would be needed at this layer, if the implication is something semantic then that can be left up to the consumer to validate. Relatedly, I assume we need to only set the name if we don't already have one (can we cover that scenario in the tests)?



-- 
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: github-unsubscribe@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org