This is an automated email from the ASF dual-hosted git repository. github-bot pushed a commit to branch gh-readonly-queue/main/pr-2185-0c19e088bf621397458102fde83cbf1020925be5 in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
commit d7f56e8942f5e5ad2949b1a9be5f5b1c9bf1c3cd Author: xitep <[email protected]> AuthorDate: Thu Feb 5 20:09:07 2026 +0100 [Oracle] Support hierarchical queries (#2185) Co-authored-by: Ifeanyi Ubah <[email protected]> --- src/ast/mod.rs | 2 +- src/ast/query.rs | 70 ++++++++--- src/ast/spans.rs | 30 +++-- src/dialect/oracle.rs | 8 +- src/keywords.rs | 1 + src/parser/mod.rs | 79 ++++++------ tests/sqlparser_bigquery.rs | 4 +- tests/sqlparser_clickhouse.rs | 2 +- tests/sqlparser_common.rs | 281 ++++++++++++++++++++++++++++++------------ tests/sqlparser_duckdb.rs | 4 +- tests/sqlparser_mssql.rs | 6 +- tests/sqlparser_mysql.rs | 16 +-- tests/sqlparser_oracle.rs | 42 +++++-- tests/sqlparser_postgres.rs | 6 +- 14 files changed, 376 insertions(+), 175 deletions(-) diff --git a/src/ast/mod.rs b/src/ast/mod.rs index a26c14ef..a5951969 100644 --- a/src/ast/mod.rs +++ b/src/ast/mod.rs @@ -87,7 +87,7 @@ pub use self::dml::{ }; pub use self::operator::{BinaryOperator, UnaryOperator}; pub use self::query::{ - AfterMatchSkip, ConnectBy, Cte, CteAsMaterialized, Distinct, EmptyMatchesMode, + AfterMatchSkip, ConnectByKind, Cte, CteAsMaterialized, Distinct, EmptyMatchesMode, ExceptSelectItem, ExcludeSelectItem, ExprWithAlias, ExprWithAliasAndOrderBy, Fetch, ForClause, ForJson, ForXml, FormatClause, GroupByExpr, GroupByWithModifier, IdentWithAlias, IlikeSelectItem, InputFormatClause, Interpolate, InterpolateExpr, Join, JoinConstraint, diff --git a/src/ast/query.rs b/src/ast/query.rs index bb2d889f..b8f605be 100644 --- a/src/ast/query.rs +++ b/src/ast/query.rs @@ -480,6 +480,8 @@ pub struct Select { pub prewhere: Option<Expr>, /// WHERE pub selection: Option<Expr>, + /// [START WITH ..] CONNECT BY .. + pub connect_by: Vec<ConnectByKind>, /// GROUP BY pub group_by: GroupByExpr, /// CLUSTER BY (Hive) @@ -501,8 +503,6 @@ pub struct Select { pub window_before_qualify: bool, /// BigQuery syntax: `SELECT AS VALUE | SELECT AS STRUCT` pub value_table_mode: Option<ValueTableMode>, - /// STARTING WITH .. CONNECT BY - pub connect_by: Option<ConnectBy>, /// Was this a FROM-first query? pub flavor: SelectFlavor, } @@ -585,6 +585,10 @@ impl fmt::Display for Select { SpaceOrNewline.fmt(f)?; Indent(selection).fmt(f)?; } + for clause in &self.connect_by { + SpaceOrNewline.fmt(f)?; + clause.fmt(f)?; + } match &self.group_by { GroupByExpr::All(_) => { SpaceOrNewline.fmt(f)?; @@ -648,10 +652,6 @@ impl fmt::Display for Select { display_comma_separated(&self.named_window).fmt(f)?; } } - if let Some(ref connect_by) = self.connect_by { - SpaceOrNewline.fmt(f)?; - connect_by.fmt(f)?; - } Ok(()) } } @@ -1204,24 +1204,60 @@ impl fmt::Display for TableWithJoins { /// Joins a table to itself to process hierarchical data in the table. /// /// See <https://docs.snowflake.com/en/sql-reference/constructs/connect-by>. +/// See <https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Hierarchical-Queries.html> #[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] #[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] #[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] -pub struct ConnectBy { - /// START WITH - pub condition: Expr, +pub enum ConnectByKind { /// CONNECT BY - pub relationships: Vec<Expr>, + ConnectBy { + /// the `CONNECT` token + connect_token: AttachedToken, + + /// [CONNECT BY] NOCYCLE + /// + /// Optional on [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E__GUID-5377971A-F518-47E4-8781-F06FEB3EF993) + nocycle: bool, + + /// join conditions denoting the hierarchical relationship + relationships: Vec<Expr>, + }, + + /// START WITH + /// + /// Optional on [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E) + /// when comming _after_ the `CONNECT BY`. + StartWith { + /// the `START` token + start_token: AttachedToken, + + /// condition selecting the root rows of the hierarchy + condition: Box<Expr>, + }, } -impl fmt::Display for ConnectBy { +impl fmt::Display for ConnectByKind { fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result { - write!( - f, - "START WITH {condition} CONNECT BY {relationships}", - condition = self.condition, - relationships = display_comma_separated(&self.relationships) - ) + match self { + ConnectByKind::ConnectBy { + connect_token: _, + nocycle, + relationships, + } => { + write!( + f, + "CONNECT BY {nocycle}{relationships}", + nocycle = if *nocycle { "NOCYCLE " } else { "" }, + relationships = display_comma_separated(relationships) + ) + } + ConnectByKind::StartWith { + start_token: _, + condition, + } => { + write!(f, "START WITH {condition}") + } + } } } diff --git a/src/ast/spans.rs b/src/ast/spans.rs index ffc96ed3..16a9a926 100644 --- a/src/ast/spans.rs +++ b/src/ast/spans.rs @@ -32,7 +32,7 @@ use super::{ AlterIndexOperation, AlterTableOperation, Analyze, Array, Assignment, AssignmentTarget, AttachedToken, BeginEndStatements, CaseStatement, CloseCursor, ClusteredIndex, ColumnDef, ColumnOption, ColumnOptionDef, ConditionalStatementBlock, ConditionalStatements, - ConflictTarget, ConnectBy, ConstraintCharacteristics, CopySource, CreateIndex, CreateTable, + ConflictTarget, ConnectByKind, ConstraintCharacteristics, CopySource, CreateIndex, CreateTable, CreateTableOptions, Cte, Delete, DoUpdate, ExceptSelectItem, ExcludeSelectItem, Expr, ExprWithAlias, Fetch, ForValues, FromTable, Function, FunctionArg, FunctionArgExpr, FunctionArgumentClause, FunctionArgumentList, FunctionArguments, GroupByExpr, HavingBound, @@ -2269,28 +2269,34 @@ impl Spanned for Select { .chain(lateral_views.iter().map(|item| item.span())) .chain(prewhere.iter().map(|item| item.span())) .chain(selection.iter().map(|item| item.span())) + .chain(connect_by.iter().map(|item| item.span())) .chain(core::iter::once(group_by.span())) .chain(cluster_by.iter().map(|item| item.span())) .chain(distribute_by.iter().map(|item| item.span())) .chain(sort_by.iter().map(|item| item.span())) .chain(having.iter().map(|item| item.span())) .chain(named_window.iter().map(|item| item.span())) - .chain(qualify.iter().map(|item| item.span())) - .chain(connect_by.iter().map(|item| item.span())), + .chain(qualify.iter().map(|item| item.span())), ) } } -impl Spanned for ConnectBy { +impl Spanned for ConnectByKind { fn span(&self) -> Span { - let ConnectBy { - condition, - relationships, - } = self; - - union_spans( - core::iter::once(condition.span()).chain(relationships.iter().map(|item| item.span())), - ) + match self { + ConnectByKind::ConnectBy { + connect_token, + nocycle: _, + relationships, + } => union_spans( + core::iter::once(connect_token.0.span()) + .chain(relationships.last().iter().map(|item| item.span())), + ), + ConnectByKind::StartWith { + start_token, + condition, + } => union_spans([start_token.0.span(), condition.span()].into_iter()), + } } } diff --git a/src/dialect/oracle.rs b/src/dialect/oracle.rs index a72d5d7a..71b0fdb5 100644 --- a/src/dialect/oracle.rs +++ b/src/dialect/oracle.rs @@ -22,7 +22,9 @@ use crate::{ tokenizer::Token, }; -use super::{Dialect, Precedence}; +use super::{keywords::Keyword, Dialect, Precedence}; + +const RESERVED_KEYWORDS_FOR_SELECT_ITEM_OPERATOR: [Keyword; 1] = [Keyword::CONNECT_BY_ROOT]; /// A [`Dialect`] for [Oracle Databases](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/index.html) #[derive(Debug, Default)] @@ -96,6 +98,10 @@ impl Dialect for OracleDialect { true } + fn get_reserved_keywords_for_select_item_operator(&self) -> &[Keyword] { + &RESERVED_KEYWORDS_FOR_SELECT_ITEM_OPERATOR + } + fn supports_quote_delimited_string(&self) -> bool { true } diff --git a/src/keywords.rs b/src/keywords.rs index 2e26bda8..f84f4d21 100644 --- a/src/keywords.rs +++ b/src/keywords.rs @@ -678,6 +678,7 @@ define_keywords!( NOCOMPRESS, NOCREATEDB, NOCREATEROLE, + NOCYCLE, NOINHERIT, NOLOGIN, NONE, diff --git a/src/parser/mod.rs b/src/parser/mod.rs index bddafd1e..5b1f5990 100644 --- a/src/parser/mod.rs +++ b/src/parser/mod.rs @@ -4521,16 +4521,25 @@ impl<'a> Parser<'a> { /// consumed and returns false #[must_use] pub fn parse_keywords(&mut self, keywords: &[Keyword]) -> bool { - let index = self.index; + self.parse_keywords_indexed(keywords).is_some() + } + + /// Just like [Self::parse_keywords], but - upon success - returns the + /// token index of the first keyword. + #[must_use] + fn parse_keywords_indexed(&mut self, keywords: &[Keyword]) -> Option<usize> { + let start_index = self.index; + let mut first_keyword_index = None; for &keyword in keywords { if !self.parse_keyword(keyword) { - // println!("parse_keywords aborting .. did not find {:?}", keyword); - // reset index and return immediately - self.index = index; - return false; + self.index = start_index; + return None; + } + if first_keyword_index.is_none() { + first_keyword_index = Some(self.index.saturating_sub(1)); } } - true + first_keyword_index } /// If the current token is one of the given `keywords`, returns the keyword @@ -13921,7 +13930,7 @@ impl<'a> Parser<'a> { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::FromFirstNoSelect, }); } @@ -14032,6 +14041,8 @@ impl<'a> Parser<'a> { None }; + let connect_by = self.maybe_parse_connect_by()?; + let group_by = self .parse_optional_group_by()? .unwrap_or_else(|| GroupByExpr::Expressions(vec![], vec![])); @@ -14084,17 +14095,6 @@ impl<'a> Parser<'a> { Default::default() }; - let connect_by = if self.dialect.supports_connect_by() - && self - .parse_one_of_keywords(&[Keyword::START, Keyword::CONNECT]) - .is_some() - { - self.prev_token(); - Some(self.parse_connect_by()?) - } else { - None - }; - Ok(Select { select_token: AttachedToken(select_token), optimizer_hint, @@ -14279,27 +14279,28 @@ impl<'a> Parser<'a> { } /// Parse a `CONNECT BY` clause (Oracle-style hierarchical query support). - pub fn parse_connect_by(&mut self) -> Result<ConnectBy, ParserError> { - let (condition, relationships) = if self.parse_keywords(&[Keyword::CONNECT, Keyword::BY]) { - let relationships = self.with_state(ParserState::ConnectBy, |parser| { - parser.parse_comma_separated(Parser::parse_expr) - })?; - self.expect_keywords(&[Keyword::START, Keyword::WITH])?; - let condition = self.parse_expr()?; - (condition, relationships) - } else { - self.expect_keywords(&[Keyword::START, Keyword::WITH])?; - let condition = self.parse_expr()?; - self.expect_keywords(&[Keyword::CONNECT, Keyword::BY])?; - let relationships = self.with_state(ParserState::ConnectBy, |parser| { - parser.parse_comma_separated(Parser::parse_expr) - })?; - (condition, relationships) - }; - Ok(ConnectBy { - condition, - relationships, - }) + pub fn maybe_parse_connect_by(&mut self) -> Result<Vec<ConnectByKind>, ParserError> { + let mut clauses = Vec::with_capacity(2); + loop { + if let Some(idx) = self.parse_keywords_indexed(&[Keyword::START, Keyword::WITH]) { + clauses.push(ConnectByKind::StartWith { + start_token: self.token_at(idx).clone().into(), + condition: self.parse_expr()?.into(), + }); + } else if let Some(idx) = self.parse_keywords_indexed(&[Keyword::CONNECT, Keyword::BY]) + { + clauses.push(ConnectByKind::ConnectBy { + connect_token: self.token_at(idx).clone().into(), + nocycle: self.parse_keyword(Keyword::NOCYCLE), + relationships: self.with_state(ParserState::ConnectBy, |parser| { + parser.parse_comma_separated(Parser::parse_expr) + })?, + }); + } else { + break; + } + } + Ok(clauses) } /// Parse `CREATE TABLE x AS TABLE y` diff --git a/tests/sqlparser_bigquery.rs b/tests/sqlparser_bigquery.rs index d5ad6373..cf843ea2 100644 --- a/tests/sqlparser_bigquery.rs +++ b/tests/sqlparser_bigquery.rs @@ -2711,7 +2711,7 @@ fn test_export_data() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: Some(OrderBy { @@ -2817,7 +2817,7 @@ fn test_export_data() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: Some(OrderBy { diff --git a/tests/sqlparser_clickhouse.rs b/tests/sqlparser_clickhouse.rs index db832911..b8b4e337 100644 --- a/tests/sqlparser_clickhouse.rs +++ b/tests/sqlparser_clickhouse.rs @@ -103,7 +103,7 @@ fn parse_map_access_expr() { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }, select diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs index b442ec42..4f63e4a3 100644 --- a/tests/sqlparser_common.rs +++ b/tests/sqlparser_common.rs @@ -501,7 +501,7 @@ fn parse_update_set_from() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -5959,7 +5959,7 @@ fn test_parse_named_window() { qualify: None, window_before_qualify: true, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }; assert_eq!(actual_select_only, expected); @@ -6634,7 +6634,7 @@ fn parse_interval_and_or_xor() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -8972,7 +8972,7 @@ fn lateral_function() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }; assert_eq!(actual_select_only, expected); @@ -9960,7 +9960,7 @@ fn parse_merge() { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -12360,7 +12360,7 @@ fn parse_unload() { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), with: None, @@ -12644,54 +12644,7 @@ fn parse_map_access_expr() { #[test] fn parse_connect_by() { - let expect_query = Select { - select_token: AttachedToken::empty(), - optimizer_hint: None, - distinct: None, - select_modifiers: None, - top: None, - top_before_distinct: false, - projection: vec![ - SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("employee_id"))), - SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("manager_id"))), - SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("title"))), - ], - exclude: None, - from: vec![TableWithJoins { - relation: table_from_name(ObjectName::from(vec![Ident::new("employees")])), - joins: vec![], - }], - into: None, - lateral_views: vec![], - prewhere: None, - selection: None, - group_by: GroupByExpr::Expressions(vec![], vec![]), - cluster_by: vec![], - distribute_by: vec![], - sort_by: vec![], - having: None, - named_window: vec![], - qualify: None, - window_before_qualify: false, - value_table_mode: None, - connect_by: Some(ConnectBy { - condition: Expr::BinaryOp { - left: Box::new(Expr::Identifier(Ident::new("title"))), - op: BinaryOperator::Eq, - right: Box::new(Expr::Value( - Value::SingleQuotedString("president".to_owned()).with_empty_span(), - )), - }, - relationships: vec![Expr::BinaryOp { - left: Box::new(Expr::Identifier(Ident::new("manager_id"))), - op: BinaryOperator::Eq, - right: Box::new(Expr::Prior(Box::new(Expr::Identifier(Ident::new( - "employee_id", - ))))), - }], - }), - flavor: SelectFlavor::Standard, - }; + let dialects = all_dialects_where(|d| d.supports_connect_by()); let connect_by_1 = concat!( "SELECT employee_id, manager_id, title FROM employees ", @@ -12701,8 +12654,63 @@ fn parse_connect_by() { ); assert_eq!( - all_dialects_where(|d| d.supports_connect_by()).verified_only_select(connect_by_1), - expect_query + dialects.verified_only_select(connect_by_1), + Select { + select_token: AttachedToken::empty(), + optimizer_hint: None, + distinct: None, + select_modifiers: None, + top: None, + top_before_distinct: false, + projection: vec![ + SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("employee_id"))), + SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("manager_id"))), + SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("title"))), + ], + exclude: None, + from: vec![TableWithJoins { + relation: table_from_name(ObjectName::from(vec![Ident::new("employees")])), + joins: vec![], + }], + into: None, + lateral_views: vec![], + prewhere: None, + selection: None, + group_by: GroupByExpr::Expressions(vec![], vec![]), + cluster_by: vec![], + distribute_by: vec![], + sort_by: vec![], + having: None, + named_window: vec![], + qualify: None, + window_before_qualify: false, + value_table_mode: None, + connect_by: vec![ + ConnectByKind::StartWith { + start_token: AttachedToken::empty(), + condition: Expr::BinaryOp { + left: Box::new(Expr::Identifier(Ident::new("title"))), + op: BinaryOperator::Eq, + right: Box::new(Expr::Value( + Value::SingleQuotedString("president".to_owned()).with_empty_span(), + )), + } + .into() + }, + ConnectByKind::ConnectBy { + connect_token: AttachedToken::empty(), + nocycle: false, + relationships: vec![Expr::BinaryOp { + left: Box::new(Expr::Identifier(Ident::new("manager_id"))), + op: BinaryOperator::Eq, + right: Box::new(Expr::Prior(Box::new(Expr::Identifier(Ident::new( + "employee_id", + ))))), + }], + } + ], + flavor: SelectFlavor::Standard, + } ); // CONNECT BY can come before START WITH @@ -12713,9 +12721,63 @@ fn parse_connect_by() { "ORDER BY employee_id" ); assert_eq!( - all_dialects_where(|d| d.supports_connect_by()) - .verified_only_select_with_canonical(connect_by_2, connect_by_1), - expect_query + dialects.verified_only_select(connect_by_2), + Select { + select_token: AttachedToken::empty(), + optimizer_hint: None, + distinct: None, + select_modifiers: None, + top: None, + top_before_distinct: false, + projection: vec![ + SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("employee_id"))), + SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("manager_id"))), + SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("title"))), + ], + exclude: None, + from: vec![TableWithJoins { + relation: table_from_name(ObjectName::from(vec![Ident::new("employees")])), + joins: vec![], + }], + into: None, + lateral_views: vec![], + prewhere: None, + selection: None, + group_by: GroupByExpr::Expressions(vec![], vec![]), + cluster_by: vec![], + distribute_by: vec![], + sort_by: vec![], + having: None, + named_window: vec![], + qualify: None, + window_before_qualify: false, + value_table_mode: None, + connect_by: vec![ + ConnectByKind::ConnectBy { + connect_token: AttachedToken::empty(), + nocycle: false, + relationships: vec![Expr::BinaryOp { + left: Box::new(Expr::Identifier(Ident::new("manager_id"))), + op: BinaryOperator::Eq, + right: Box::new(Expr::Prior(Box::new(Expr::Identifier(Ident::new( + "employee_id", + ))))), + }], + }, + ConnectByKind::StartWith { + start_token: AttachedToken::empty(), + condition: Expr::BinaryOp { + left: Box::new(Expr::Identifier(Ident::new("title"))), + op: BinaryOperator::Eq, + right: Box::new(Expr::Value( + Value::SingleQuotedString("president".to_owned()).with_empty_span(), + )), + } + .into() + }, + ], + flavor: SelectFlavor::Standard, + } ); // WHERE must come before CONNECT BY @@ -12727,7 +12789,7 @@ fn parse_connect_by() { "ORDER BY employee_id" ); assert_eq!( - all_dialects_where(|d| d.supports_connect_by()).verified_only_select(connect_by_3), + dialects.verified_only_select(connect_by_3), Select { select_token: AttachedToken::empty(), optimizer_hint: None, @@ -12762,22 +12824,30 @@ fn parse_connect_by() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: Some(ConnectBy { - condition: Expr::BinaryOp { - left: Box::new(Expr::Identifier(Ident::new("title"))), - op: BinaryOperator::Eq, - right: Box::new(Expr::Value( - (Value::SingleQuotedString("president".to_owned(),)).with_empty_span() - )), + connect_by: vec![ + ConnectByKind::StartWith { + start_token: AttachedToken::empty(), + condition: Expr::BinaryOp { + left: Box::new(Expr::Identifier(Ident::new("title"))), + op: BinaryOperator::Eq, + right: Box::new(Expr::Value( + (Value::SingleQuotedString("president".to_owned(),)).with_empty_span() + )), + } + .into() }, - relationships: vec![Expr::BinaryOp { - left: Box::new(Expr::Identifier(Ident::new("manager_id"))), - op: BinaryOperator::Eq, - right: Box::new(Expr::Prior(Box::new(Expr::Identifier(Ident::new( - "employee_id", - ))))), - }], - }), + ConnectByKind::ConnectBy { + connect_token: AttachedToken::empty(), + nocycle: false, + relationships: vec![Expr::BinaryOp { + left: Box::new(Expr::Identifier(Ident::new("manager_id"))), + op: BinaryOperator::Eq, + right: Box::new(Expr::Prior(Box::new(Expr::Identifier(Ident::new( + "employee_id", + ))))), + }], + } + ], flavor: SelectFlavor::Standard, } ); @@ -12789,7 +12859,7 @@ fn parse_connect_by() { "WHERE employee_id <> 42 ", "ORDER BY employee_id" ); - all_dialects_where(|d| d.supports_connect_by()) + dialects .parse_sql_statements(connect_by_4) .expect_err("should have failed"); @@ -12803,6 +12873,61 @@ fn parse_connect_by() { "prior" )))] ); + + // no START WITH and NOCYCLE + let connect_by_5 = "SELECT child, parent FROM t CONNECT BY NOCYCLE parent = PRIOR child"; + assert_eq!( + dialects.verified_only_select(connect_by_5), + Select { + select_token: AttachedToken::empty(), + optimizer_hint: None, + distinct: None, + select_modifiers: None, + top: None, + top_before_distinct: false, + projection: vec![ + SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("child"))), + SelectItem::UnnamedExpr(Expr::Identifier(Ident::new("parent"))), + ], + exclude: None, + from: vec![TableWithJoins { + relation: table_from_name(ObjectName::from(vec![Ident::new("t")])), + joins: vec![], + }], + into: None, + lateral_views: vec![], + prewhere: None, + selection: None, + group_by: GroupByExpr::Expressions(vec![], vec![]), + cluster_by: vec![], + distribute_by: vec![], + sort_by: vec![], + having: None, + named_window: vec![], + qualify: None, + window_before_qualify: false, + value_table_mode: None, + connect_by: vec![ConnectByKind::ConnectBy { + connect_token: AttachedToken::empty(), + nocycle: true, + relationships: vec![Expr::BinaryOp { + left: Expr::Identifier(Ident::new("parent")).into(), + op: BinaryOperator::Eq, + right: Expr::Prior(Expr::Identifier(Ident::new("child")).into()).into(), + }], + }], + flavor: SelectFlavor::Standard, + } + ); + + // CONNECT BY after WHERE and before GROUP BY + dialects.verified_only_select("SELECT 0 FROM t WHERE 1 = 1 CONNECT BY 2 = 2 GROUP BY 3"); + dialects.verified_only_select( + "SELECT 0 FROM t WHERE 1 = 1 START WITH 'a' = 'a' CONNECT BY 2 = 2 GROUP BY 3", + ); + dialects.verified_only_select( + "SELECT 0 FROM t WHERE 1 = 1 CONNECT BY 2 = 2 START WITH 'a' = 'a' GROUP BY 3", + ); } #[test] @@ -13701,7 +13826,7 @@ fn test_extract_seconds_ok() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -15834,7 +15959,7 @@ fn test_select_from_first() { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor, }))), order_by: None, diff --git a/tests/sqlparser_duckdb.rs b/tests/sqlparser_duckdb.rs index e408d997..b9ae2649 100644 --- a/tests/sqlparser_duckdb.rs +++ b/tests/sqlparser_duckdb.rs @@ -294,7 +294,7 @@ fn test_select_union_by_name() { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), right: Box::<SetExpr>::new(SetExpr::Select(Box::new(Select { @@ -327,7 +327,7 @@ fn test_select_union_by_name() { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), }); diff --git a/tests/sqlparser_mssql.rs b/tests/sqlparser_mssql.rs index 7f609342..84b8658b 100644 --- a/tests/sqlparser_mssql.rs +++ b/tests/sqlparser_mssql.rs @@ -164,7 +164,7 @@ fn parse_create_procedure() { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))) }))], @@ -1392,7 +1392,7 @@ fn parse_substring_in_select() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -1536,7 +1536,7 @@ fn parse_mssql_declare() { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))) })) diff --git a/tests/sqlparser_mysql.rs b/tests/sqlparser_mysql.rs index d1e718f4..601b6c4f 100644 --- a/tests/sqlparser_mysql.rs +++ b/tests/sqlparser_mysql.rs @@ -1460,7 +1460,7 @@ fn parse_escaped_quote_identifiers_with_escape() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -1517,7 +1517,7 @@ fn parse_escaped_quote_identifiers_with_no_escape() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -1566,7 +1566,7 @@ fn parse_escaped_backticks_with_escape() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -1619,7 +1619,7 @@ fn parse_escaped_backticks_with_no_escape() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -2443,7 +2443,7 @@ fn parse_select_with_numeric_prefix_column_name() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))) ); @@ -2620,7 +2620,7 @@ fn parse_select_with_concatenation_of_exp_number_and_numeric_prefix_column() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))) ); @@ -3268,7 +3268,7 @@ fn parse_substring_in_select() { window_before_qualify: false, qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -3578,7 +3578,7 @@ fn parse_hex_string_introducer() { qualify: None, value_table_mode: None, into: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, diff --git a/tests/sqlparser_oracle.rs b/tests/sqlparser_oracle.rs index 1c12f868..0dbccdb5 100644 --- a/tests/sqlparser_oracle.rs +++ b/tests/sqlparser_oracle.rs @@ -209,7 +209,7 @@ fn parse_quote_delimited_string() { #[test] fn parse_invalid_quote_delimited_strings() { let dialect = all_dialects_where(|d| d.supports_quote_delimited_string()); - // ~ invalid quote delimiter + // invalid quote delimiter for q in [' ', '\t', '\r', '\n'] { assert_eq!( dialect.parse_sql_statements(&format!("SELECT Q'{q}abc{q}' FROM dual")), @@ -219,7 +219,7 @@ fn parse_invalid_quote_delimited_strings() { "with quote char {q:?}" ); } - // ~ invalid eof after quote + // invalid eof after quote assert_eq!( dialect.parse_sql_statements("SELECT Q'"), Err(ParserError::TokenizerError( @@ -227,7 +227,7 @@ fn parse_invalid_quote_delimited_strings() { )), "with EOF quote char" ); - // ~ unterminated string + // unterminated string assert_eq!( dialect.parse_sql_statements("SELECT Q'|asdfa...."), Err(ParserError::TokenizerError( @@ -338,7 +338,7 @@ fn parse_national_quote_delimited_string_but_is_a_word() { fn test_optimizer_hints() { let oracle_dialect = oracle(); - // ~ selects + // selects let select = oracle_dialect.verified_only_select_with_canonical( "SELECT /*+one two three*/ /*+not a hint!*/ 1 FROM dual", "SELECT /*+one two three*/ 1 FROM dual", @@ -369,16 +369,16 @@ fn test_optimizer_hints() { Some(" one two three /* asdf */\n") ); - // ~ inserts + // inserts oracle_dialect.verified_stmt("INSERT /*+ append */ INTO t1 SELECT * FROM all_objects"); - // ~ updates + // updates oracle_dialect.verified_stmt("UPDATE /*+ DISABLE_PARALLEL_DML */ table_name SET column1 = 1"); - // ~ deletes + // deletes oracle_dialect.verified_stmt("DELETE --+ ENABLE_PARALLEL_DML\n FROM table_name"); - // ~ merges + // merges oracle_dialect.verified_stmt( "MERGE /*+ CLUSTERING */ INTO people_target pt \ USING people_source ps \ @@ -388,3 +388,29 @@ fn test_optimizer_hints() { VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title)", ); } + +#[test] +fn test_connect_by() { + let oracle_dialect = oracle(); + + oracle_dialect.verified_only_select( + "SELECT last_name AS \"Employee\", CONNECT_BY_ISCYCLE AS \"Cycle\", \ + LEVEL, \ + SYS_CONNECT_BY_PATH(last_name, '/') AS \"Path\" \ + FROM employees \ + WHERE level <= 3 AND department_id = 80 \ + START WITH last_name = 'King' \ + CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4 \ + ORDER BY \"Employee\", \"Cycle\", LEVEL, \"Path\"", + ); + + // CONNECT_BY_ROOT + oracle_dialect.verified_only_select( + "SELECT last_name AS \"Employee\", CONNECT_BY_ROOT last_name AS \"Manager\", \ + LEVEL - 1 AS \"Pathlen\", SYS_CONNECT_BY_PATH(last_name, '/') AS \"Path\" \ + FROM employees \ + WHERE LEVEL > 1 AND department_id = 110 \ + CONNECT BY PRIOR employee_id = manager_id \ + ORDER BY \"Employee\", \"Manager\", \"Pathlen\", \"Path\"", + ); +} diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs index 4ce8ed8b..5853be7e 100644 --- a/tests/sqlparser_postgres.rs +++ b/tests/sqlparser_postgres.rs @@ -1331,7 +1331,7 @@ fn parse_copy_to() { sort_by: vec![], qualify: None, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), order_by: None, @@ -3096,7 +3096,7 @@ fn parse_array_subquery_expr() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), right: Box::new(SetExpr::Select(Box::new(Select { @@ -3124,7 +3124,7 @@ fn parse_array_subquery_expr() { qualify: None, window_before_qualify: false, value_table_mode: None, - connect_by: None, + connect_by: vec![], flavor: SelectFlavor::Standard, }))), }), --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
