This is an automated email from the ASF dual-hosted git repository. github-bot pushed a commit to branch gh-readonly-queue/main/pr-2276-b3e176daf302985feef46a3933428b196efb78e8 in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
commit 50921b173a3ca7a3183398cc83de1dfd1cd165f5 Author: xitep <[email protected]> AuthorDate: Fri Mar 20 10:48:53 2026 +0100 [Oracle] Support for `INSERT INTO (<sub-query>) ...` (#2276) --- src/ast/mod.rs | 11 +++++++++++ src/ast/spans.rs | 1 + src/dialect/mod.rs | 7 +++++++ src/dialect/oracle.rs | 5 +++++ src/parser/mod.rs | 44 +++++++++++++++++++++++++++++++++++++++++--- tests/sqlparser_common.rs | 34 ++++++++++++++++++++++++++++++++++ 6 files changed, 99 insertions(+), 3 deletions(-) diff --git a/src/ast/mod.rs b/src/ast/mod.rs index c4d1b50c..36c41d6c 100644 --- a/src/ast/mod.rs +++ b/src/ast/mod.rs @@ -10888,6 +10888,16 @@ pub enum TableObject { /// ``` /// [Clickhouse](https://clickhouse.com/docs/en/sql-reference/table-functions) TableFunction(Function), + + /// Table specified through a sub-query + /// Example: + /// ```sql + /// INSERT INTO + /// (SELECT employee_id, last_name, email, hire_date, job_id, salary, commission_pct FROM employees) + /// VALUES (207, 'Gregory', '[email protected]', sysdate, 'PU_CLERK', 1.2E3, NULL); + /// ``` + /// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423__I2126242) + TableQuery(Box<Query>), } impl fmt::Display for TableObject { @@ -10895,6 +10905,7 @@ impl fmt::Display for TableObject { match self { Self::TableName(table_name) => write!(f, "{table_name}"), Self::TableFunction(func) => write!(f, "FUNCTION {func}"), + Self::TableQuery(table_query) => write!(f, "({table_query})"), } } } diff --git a/src/ast/spans.rs b/src/ast/spans.rs index 74f731a7..78698bbe 100644 --- a/src/ast/spans.rs +++ b/src/ast/spans.rs @@ -2387,6 +2387,7 @@ impl Spanned for TableObject { union_spans(segments.iter().map(|i| i.span())) } TableObject::TableFunction(func) => func.span(), + TableObject::TableQuery(query) => query.span(), } } } diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs index 8703e402..fed81b60 100644 --- a/src/dialect/mod.rs +++ b/src/dialect/mod.rs @@ -1247,6 +1247,13 @@ pub trait Dialect: Debug + Any { false } + /// Does the dialect support table queries in insertion? + /// + /// e.g. `SELECT INTO (<query>) ...` + fn supports_insert_table_query(&self) -> bool { + false + } + /// Does the dialect support insert formats, e.g. `INSERT INTO ... FORMAT <format>` fn supports_insert_format(&self) -> bool { false diff --git a/src/dialect/oracle.rs b/src/dialect/oracle.rs index dce0493d..ccbef5b6 100644 --- a/src/dialect/oracle.rs +++ b/src/dialect/oracle.rs @@ -114,4 +114,9 @@ impl Dialect for OracleDialect { fn supports_insert_table_alias(&self) -> bool { true } + + /// See <https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423__I2126242> + fn supports_insert_table_query(&self) -> bool { + true + } } diff --git a/src/parser/mod.rs b/src/parser/mod.rs index 6adecb0c..f617cadd 100644 --- a/src/parser/mod.rs +++ b/src/parser/mod.rs @@ -12688,6 +12688,9 @@ impl<'a> Parser<'a> { let fn_name = self.parse_object_name(false)?; self.parse_function_call(fn_name) .map(TableObject::TableFunction) + } else if self.dialect.supports_insert_table_query() && self.peek_subquery_or_cte_start() { + self.parse_parenthesized(|p| p.parse_query()) + .map(TableObject::TableQuery) } else { self.parse_object_name(false).map(TableObject::TableName) } @@ -17601,9 +17604,44 @@ impl<'a> Parser<'a> { /// Returns true if the immediate tokens look like the /// beginning of a subquery. `(SELECT ...` fn peek_subquery_start(&mut self) -> bool { - let [maybe_lparen, maybe_select] = self.peek_tokens(); - Token::LParen == maybe_lparen - && matches!(maybe_select, Token::Word(w) if w.keyword == Keyword::SELECT) + matches!( + self.peek_tokens_ref(), + [ + TokenWithSpan { + token: Token::LParen, + .. + }, + TokenWithSpan { + token: Token::Word(Word { + keyword: Keyword::SELECT, + .. + }), + .. + }, + ] + ) + } + + /// Returns true if the immediate tokens look like the + /// beginning of a subquery possibly preceded by CTEs; + /// i.e. `(WITH ...` or `(SELECT ...`. + fn peek_subquery_or_cte_start(&mut self) -> bool { + matches!( + self.peek_tokens_ref(), + [ + TokenWithSpan { + token: Token::LParen, + .. + }, + TokenWithSpan { + token: Token::Word(Word { + keyword: Keyword::SELECT | Keyword::WITH, + .. + }), + .. + }, + ] + ) } fn parse_conflict_clause(&mut self) -> Option<SqliteOnConflict> { diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs index 08fb6107..6f9e4695 100644 --- a/tests/sqlparser_common.rs +++ b/tests/sqlparser_common.rs @@ -13525,6 +13525,40 @@ fn insert_into_with_parentheses() { dialects.verified_stmt(r#"INSERT INTO t1 ("select", name) (SELECT t2.name FROM t2)"#); } +#[test] +fn test_insert_with_query_table() { + let dialects = all_dialects_where(|d| d.supports_insert_table_query()); + + // a simple query (block); i.e. SELECT ... + let sql = "INSERT INTO (SELECT employee_id, last_name FROM employees) VALUES (207, 'Gregory')"; + dialects.verified_stmt(sql); + + // a full blown query; i.e. `WITH ... SELECT .. ORDER BY ...` + let sql = "INSERT INTO \ + (WITH cte AS (SELECT 1 AS id, 2 AS val FROM dual) SELECT foo_t.id, foo_t.val FROM foo_t \ + WHERE EXISTS (SELECT 1 FROM cte WHERE cte.id = foo_t.id) ORDER BY 1, 2) \ + (id, val) \ + VALUES (1000, 10101)"; + dialects.verified_stmt(sql); + + // an alias to the insert target query table + let sql = "INSERT INTO \ + (WITH cte AS (SELECT 1 AS id, 2 AS val FROM dual) SELECT foo_t.id, foo_t.val FROM foo_t \ + WHERE EXISTS (SELECT 1 FROM cte WHERE cte.id = foo_t.id)) abc \ + (id, val) \ + VALUES (1000, 10101)"; + dialects.verified_stmt(sql); + + // a query table target and a query source + let sql = "INSERT INTO (SELECT foo_t.id, foo_t.val FROM foo_t) SELECT 10, 20 FROM dual"; + dialects.verified_stmt(sql); + + // a query table target and a query source, with explicit columns + let sql = + "INSERT INTO (SELECT foo_t.id, foo_t.val FROM foo_t) (id, val) SELECT 10, 20 FROM dual"; + dialects.verified_stmt(sql); +} + #[test] fn parse_odbc_scalar_function() { let select = verified_only_select("SELECT {fn my_func(1, 2)}"); --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
