This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
The following commit(s) were added to refs/heads/main by this push:
new d7f56e89 [Oracle] Support hierarchical queries (#2185)
d7f56e89 is described below
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]
