Skip to content

Commit 9296011

Browse files
finchxxiaiffyiocursoragentfinchxia
authored
Databricks: support INSERT BY NAME (#2403)
Co-authored-by: Ifeanyi Ubah <[email protected]> Co-authored-by: Cursor <[email protected]> Co-authored-by: finchxia <[email protected]>
1 parent b595062 commit 9296011

7 files changed

Lines changed: 101 additions & 0 deletions

File tree

‎src/ast/dml.rs‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,20 @@ pub struct Insert {
6262
pub table_alias: Option<TableAliasWithoutColumns>,
6363
/// COLUMNS
6464
pub columns: Vec<ObjectName>,
65+
/// `BY NAME` clause used by Databricks SQL.
66+
///
67+
/// When present, columns from the source query are matched to columns in
68+
/// the target table by name instead of by position. The syntax is:
69+
///
70+
/// ```sql
71+
/// INSERT INTO [TABLE] table_name
72+
/// [PARTITION (...)]
73+
/// [(column_name [, ...]) | BY NAME]
74+
/// query
75+
/// ```
76+
///
77+
/// See <https://docs.databricks.com/gcp/en/sql/language-manual/sql-ref-syntax-dml-insert-into>.
78+
pub by_name: bool,
6579
/// Overwrite (Hive)
6680
pub overwrite: bool,
6781
/// A SQL query that specifies what to insert
@@ -201,6 +215,11 @@ impl Display for Insert {
201215
}
202216
}
203217

218+
if self.by_name {
219+
write!(f, "BY NAME")?;
220+
SpaceOrNewline.fmt(f)?;
221+
}
222+
204223
if !self.after_columns.is_empty() {
205224
write!(f, "({})", display_comma_separated(&self.after_columns))?;
206225
SpaceOrNewline.fmt(f)?;

‎src/ast/spans.rs‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1354,6 +1354,7 @@ impl Spanned for Insert {
13541354
table,
13551355
table_alias,
13561356
columns,
1357+
by_name: _, // bool
13571358
overwrite: _, // bool
13581359
source,
13591360
partitioned,

‎src/dialect/snowflake.rs‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1871,6 +1871,7 @@ fn parse_multi_table_insert(
18711871
table: TableObject::TableName(ObjectName(vec![])), // Not used for multi-table insert
18721872
table_alias: None,
18731873
columns: vec![],
1874+
by_name: false,
18741875
overwrite,
18751876
source: Some(source),
18761877
assignments: vec![],

‎src/parser/mod.rs‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18481,7 +18481,9 @@ impl<'a> Parser<'a> {
1848118481
let table = self.parse_keyword(Keyword::TABLE);
1848218482
let table_object = self.parse_table_object()?;
1848318483

18484+
// `BY NAME` is an INSERT clause, not a table alias.
1848418485
let table_alias = if self.dialect.supports_insert_table_alias()
18486+
&& !self.peek_keywords(&[Keyword::BY, Keyword::NAME])
1848518487
&& !self.peek_sub_query()
1848618488
&& self
1848718489
.peek_one_of_keywords(&[Keyword::DEFAULT, Keyword::VALUES])
@@ -18505,6 +18507,7 @@ impl<'a> Parser<'a> {
1850518507

1850618508
let is_mysql = dialect_of!(self is MySqlDialect);
1850718509

18510+
let mut by_name = false;
1850818511
let (columns, partitioned, after_columns, output, source, assignments) = if self
1850918512
.parse_keywords(&[Keyword::DEFAULT, Keyword::VALUES])
1851018513
{
@@ -18515,6 +18518,7 @@ impl<'a> Parser<'a> {
1851518518
self.parse_parenthesized_qualified_column_list(Optional, is_mysql)?;
1851618519

1851718520
let partitioned = self.parse_insert_partition()?;
18521+
by_name = self.parse_keywords(&[Keyword::BY, Keyword::NAME]);
1851818522
// Hive allows you to specify columns after partitions as well if you want.
1851918523
let after_columns = if dialect_of!(self is HiveDialect) {
1852018524
self.parse_parenthesized_column_list(Optional, false)?
@@ -18639,6 +18643,7 @@ impl<'a> Parser<'a> {
1863918643
ignore,
1864018644
into,
1864118645
overwrite,
18646+
by_name,
1864218647
partitioned,
1864318648
columns,
1864418649
after_columns,

‎tests/sqlparser_common.rs‎

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20015,3 +20015,48 @@ fn parse_function_arg_call_chain_no_exponential_blowup() {
2001520015
rx.recv_timeout(Duration::from_secs(5))
2001620016
.expect("parser should reject this quickly, not loop exponentially");
2001720017
}
20018+
20019+
#[test]
20020+
fn parse_insert_by_name() {
20021+
verified_stmt("INSERT INTO target BY NAME SELECT 1 AS a");
20022+
20023+
match verified_stmt("INSERT INTO target (a) BY NAME SELECT 1 AS a") {
20024+
Statement::Insert(Insert {
20025+
by_name, columns, ..
20026+
}) => {
20027+
assert!(by_name);
20028+
assert_eq!(columns.len(), 1);
20029+
}
20030+
_ => unreachable!(),
20031+
}
20032+
20033+
let dialects = all_dialects_where(|d| !d.supports_insert_table_alias());
20034+
match dialects.verified_stmt("INSERT INTO TABLE target PARTITION (p = 1) BY NAME SELECT 1 AS a")
20035+
{
20036+
Statement::Insert(Insert {
20037+
by_name,
20038+
has_table_keyword,
20039+
partitioned,
20040+
..
20041+
}) => {
20042+
assert!(by_name);
20043+
assert!(has_table_keyword);
20044+
assert_eq!(partitioned.unwrap().len(), 1);
20045+
}
20046+
_ => unreachable!(),
20047+
}
20048+
20049+
// `BY NAME` does not shadow a table alias in dialects supporting one.
20050+
let dialects = all_dialects_where(|d| d.supports_insert_table_alias());
20051+
match dialects.verified_stmt("INSERT INTO target AS t BY NAME SELECT 1 AS a") {
20052+
Statement::Insert(Insert {
20053+
by_name,
20054+
table_alias,
20055+
..
20056+
}) => {
20057+
assert!(by_name);
20058+
assert_eq!(table_alias.unwrap().alias.value, "t");
20059+
}
20060+
_ => unreachable!(),
20061+
}
20062+
}

‎tests/sqlparser_databricks.rs‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -738,6 +738,14 @@ fn parse_cte_without_as() {
738738
.is_err());
739739
}
740740

741+
#[test]
742+
fn test_databricks_insert_by_name() {
743+
databricks_and_generic().verified_stmt("INSERT INTO target BY NAME SELECT 1 AS a");
744+
databricks_and_generic().verified_stmt(
745+
"INSERT INTO TABLE lakehouse.dwd.dwd_event_quality_sla_metric_di BY NAME WITH day AS (SELECT 1 AS event_data_id) SELECT event_data_id FROM day",
746+
);
747+
}
748+
741749
#[test]
742750
fn parse_databricks_query_entry_points() {
743751
databricks().verified_stmt("CREATE TABLE t (attrs MAP<STRING, ARRAY<INT>>)");

‎tests/sqlparser_postgres.rs‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6121,6 +6121,7 @@ fn test_simple_postgres_insert_with_alias() {
61216121
span: Span::empty(),
61226122
})
61236123
],
6124+
by_name: false,
61246125
overwrite: false,
61256126
source: Some(Box::new(Query {
61266127
with: None,
@@ -6201,6 +6202,7 @@ fn test_simple_postgres_insert_with_alias() {
62016202
span: Span::empty(),
62026203
})
62036204
],
6205+
by_name: false,
62046206
overwrite: false,
62056207
source: Some(Box::new(Query {
62066208
with: None,
@@ -6283,6 +6285,7 @@ fn test_simple_insert_with_quoted_alias() {
62836285
span: Span::empty(),
62846286
})
62856287
],
6288+
by_name: false,
62866289
overwrite: false,
62876290
source: Some(Box::new(Query {
62886291
with: None,
@@ -9931,3 +9934,22 @@ fn parse_non_reserved_keywords_as_table_alias() {
99319934
));
99329935
}
99339936
}
9937+
9938+
#[test]
9939+
fn parse_insert_by_name_keywords_as_table_and_alias() {
9940+
// Without a table name, `BY NAME` is not an INSERT BY NAME clause. PostgreSQL
9941+
// treats `BY` as the table name and `NAME` as its implicit table alias.
9942+
match pg().verified_stmt("INSERT INTO BY NAME SELECT 1 AS a") {
9943+
Statement::Insert(Insert {
9944+
table: TableObject::TableName(table),
9945+
table_alias: Some(table_alias),
9946+
by_name,
9947+
..
9948+
}) => {
9949+
assert_eq!(table.to_string(), "BY");
9950+
assert_eq!(table_alias.alias.value, "NAME");
9951+
assert!(!by_name);
9952+
}
9953+
statement => panic!("Expected INSERT statement, got: {statement:?}"),
9954+
}
9955+
}

0 commit comments

Comments
 (0)