This is an automated email from the ASF dual-hosted git repository.
iffyio 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 fb578bb4 Preserve MySQL-style `LIMIT <offset>, <limit>` syntax (#1765)
fb578bb4 is described below
commit fb578bb419d08d2d5b49fb75a61f1ddd6df77ba4
Author: Michael Victor Zink <[email protected]>
AuthorDate: Wed Mar 12 13:24:06 2025 -0700
Preserve MySQL-style `LIMIT <offset>, <limit>` syntax (#1765)
---
src/ast/mod.rs | 23 ++--
src/ast/query.rs | 73 ++++++++++---
src/ast/spans.rs | 31 ++++--
src/ast/visitor.rs | 4 +-
src/parser/mod.rs | 109 ++++++++++---------
tests/sqlparser_clickhouse.rs | 15 ++-
tests/sqlparser_common.rs | 240 +++++++++++++++++++++++-------------------
tests/sqlparser_mssql.rs | 12 +--
tests/sqlparser_mysql.rs | 66 +++---------
tests/sqlparser_postgres.rs | 20 +---
10 files changed, 327 insertions(+), 266 deletions(-)
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index 8ab3fc0f..139850e8 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -66,17 +66,18 @@ pub use self::query::{
FormatClause, GroupByExpr, GroupByWithModifier, IdentWithAlias,
IlikeSelectItem,
InputFormatClause, Interpolate, InterpolateExpr, Join, JoinConstraint,
JoinOperator,
JsonTableColumn, JsonTableColumnErrorHandling, JsonTableNamedColumn,
JsonTableNestedColumn,
- LateralView, LockClause, LockType, MatchRecognizePattern,
MatchRecognizeSymbol, Measure,
- NamedWindowDefinition, NamedWindowExpr, NonBlock, Offset, OffsetRows,
OpenJsonTableColumn,
- OrderBy, OrderByExpr, OrderByKind, OrderByOptions, PivotValueSource,
ProjectionSelect, Query,
- RenameSelectItem, RepetitionQuantifier, ReplaceSelectElement,
ReplaceSelectItem, RowsPerMatch,
- Select, SelectFlavor, SelectInto, SelectItem,
SelectItemQualifiedWildcardKind, SetExpr,
- SetOperator, SetQuantifier, Setting, SymbolDefinition, Table, TableAlias,
TableAliasColumnDef,
- TableFactor, TableFunctionArgs, TableIndexHintForClause,
TableIndexHintType, TableIndexHints,
- TableIndexType, TableSample, TableSampleBucket, TableSampleKind,
TableSampleMethod,
- TableSampleModifier, TableSampleQuantity, TableSampleSeed,
TableSampleSeedModifier,
- TableSampleUnit, TableVersion, TableWithJoins, Top, TopQuantity,
UpdateTableFromKind,
- ValueTableMode, Values, WildcardAdditionalOptions, With, WithFill,
+ LateralView, LimitClause, LockClause, LockType, MatchRecognizePattern,
MatchRecognizeSymbol,
+ Measure, NamedWindowDefinition, NamedWindowExpr, NonBlock, Offset,
OffsetRows,
+ OpenJsonTableColumn, OrderBy, OrderByExpr, OrderByKind, OrderByOptions,
PivotValueSource,
+ ProjectionSelect, Query, RenameSelectItem, RepetitionQuantifier,
ReplaceSelectElement,
+ ReplaceSelectItem, RowsPerMatch, Select, SelectFlavor, SelectInto,
SelectItem,
+ SelectItemQualifiedWildcardKind, SetExpr, SetOperator, SetQuantifier,
Setting,
+ SymbolDefinition, Table, TableAlias, TableAliasColumnDef, TableFactor,
TableFunctionArgs,
+ TableIndexHintForClause, TableIndexHintType, TableIndexHints,
TableIndexType, TableSample,
+ TableSampleBucket, TableSampleKind, TableSampleMethod, TableSampleModifier,
+ TableSampleQuantity, TableSampleSeed, TableSampleSeedModifier,
TableSampleUnit, TableVersion,
+ TableWithJoins, Top, TopQuantity, UpdateTableFromKind, ValueTableMode,
Values,
+ WildcardAdditionalOptions, With, WithFill,
};
pub use self::trigger::{
diff --git a/src/ast/query.rs b/src/ast/query.rs
index 12f72932..1b30dcf1 100644
--- a/src/ast/query.rs
+++ b/src/ast/query.rs
@@ -43,14 +43,8 @@ pub struct Query {
pub body: Box<SetExpr>,
/// ORDER BY
pub order_by: Option<OrderBy>,
- /// `LIMIT { <N> | ALL }`
- pub limit: Option<Expr>,
-
- /// `LIMIT { <N> } BY { <expr>,<expr>,... } }`
- pub limit_by: Vec<Expr>,
-
- /// `OFFSET <N> [ { ROW | ROWS } ]`
- pub offset: Option<Offset>,
+ /// `LIMIT ... OFFSET ... | LIMIT <offset>, <limit>`
+ pub limit_clause: Option<LimitClause>,
/// `FETCH { FIRST | NEXT } <N> [ PERCENT ] { ROW | ROWS } | { ONLY | WITH
TIES }`
pub fetch: Option<Fetch>,
/// `FOR { UPDATE | SHARE } [ OF table_name ] [ SKIP LOCKED | NOWAIT ]`
@@ -79,14 +73,9 @@ impl fmt::Display for Query {
if let Some(ref order_by) = self.order_by {
write!(f, " {order_by}")?;
}
- if let Some(ref limit) = self.limit {
- write!(f, " LIMIT {limit}")?;
- }
- if let Some(ref offset) = self.offset {
- write!(f, " {offset}")?;
- }
- if !self.limit_by.is_empty() {
- write!(f, " BY {}", display_separated(&self.limit_by, ", "))?;
+
+ if let Some(ref limit_clause) = self.limit_clause {
+ limit_clause.fmt(f)?;
}
if let Some(ref settings) = self.settings {
write!(f, " SETTINGS {}", display_comma_separated(settings))?;
@@ -2374,6 +2363,58 @@ impl fmt::Display for OrderByOptions {
}
}
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum LimitClause {
+ /// Standard SQL syntax
+ ///
+ /// `LIMIT <limit> [BY <expr>,<expr>,...] [OFFSET <offset>]`
+ LimitOffset {
+ /// `LIMIT { <N> | ALL }`
+ limit: Option<Expr>,
+ /// `OFFSET <N> [ { ROW | ROWS } ]`
+ offset: Option<Offset>,
+ /// `BY { <expr>,<expr>,... } }`
+ ///
+ ///
[ClickHouse](https://clickhouse.com/docs/sql-reference/statements/select/limit-by)
+ limit_by: Vec<Expr>,
+ },
+ /// [MySQL]-specific syntax; the order of expressions is reversed.
+ ///
+ /// `LIMIT <offset>, <limit>`
+ ///
+ /// [MySQL]: https://dev.mysql.com/doc/refman/8.4/en/select.html
+ OffsetCommaLimit { offset: Expr, limit: Expr },
+}
+
+impl fmt::Display for LimitClause {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ LimitClause::LimitOffset {
+ limit,
+ limit_by,
+ offset,
+ } => {
+ if let Some(ref limit) = limit {
+ write!(f, " LIMIT {limit}")?;
+ }
+ if let Some(ref offset) = offset {
+ write!(f, " {offset}")?;
+ }
+ if !limit_by.is_empty() {
+ debug_assert!(limit.is_some());
+ write!(f, " BY {}", display_separated(limit_by, ", "))?;
+ }
+ Ok(())
+ }
+ LimitClause::OffsetCommaLimit { offset, limit } => {
+ write!(f, " LIMIT {}, {}", offset, limit)
+ }
+ }
+ }
+}
+
#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
diff --git a/src/ast/spans.rs b/src/ast/spans.rs
index fb0fc3f3..a4f5eb46 100644
--- a/src/ast/spans.rs
+++ b/src/ast/spans.rs
@@ -29,8 +29,8 @@ use super::{
Function, FunctionArg, FunctionArgExpr, FunctionArgumentClause,
FunctionArgumentList,
FunctionArguments, GroupByExpr, HavingBound, IlikeSelectItem, Insert,
Interpolate,
InterpolateExpr, Join, JoinConstraint, JoinOperator, JsonPath,
JsonPathElem, LateralView,
- MatchRecognizePattern, Measure, NamedWindowDefinition, ObjectName,
ObjectNamePart, Offset,
- OnConflict, OnConflictAction, OnInsert, OrderBy, OrderByExpr, OrderByKind,
Partition,
+ LimitClause, MatchRecognizePattern, Measure, NamedWindowDefinition,
ObjectName, ObjectNamePart,
+ Offset, OnConflict, OnConflictAction, OnInsert, OrderBy, OrderByExpr,
OrderByKind, Partition,
PivotValueSource, ProjectionSelect, Query, ReferentialAction,
RenameSelectItem,
ReplaceSelectElement, ReplaceSelectItem, Select, SelectInto, SelectItem,
SetExpr, SqlOption,
Statement, Subscript, SymbolDefinition, TableAlias, TableAliasColumnDef,
TableConstraint,
@@ -94,9 +94,7 @@ impl Spanned for Query {
with,
body,
order_by,
- limit,
- limit_by,
- offset,
+ limit_clause,
fetch,
locks: _, // todo
for_clause: _, // todo, mssql specific
@@ -109,14 +107,31 @@ impl Spanned for Query {
.map(|i| i.span())
.chain(core::iter::once(body.span()))
.chain(order_by.as_ref().map(|i| i.span()))
- .chain(limit.as_ref().map(|i| i.span()))
- .chain(limit_by.iter().map(|i| i.span()))
- .chain(offset.as_ref().map(|i| i.span()))
+ .chain(limit_clause.as_ref().map(|i| i.span()))
.chain(fetch.as_ref().map(|i| i.span())),
)
}
}
+impl Spanned for LimitClause {
+ fn span(&self) -> Span {
+ match self {
+ LimitClause::LimitOffset {
+ limit,
+ offset,
+ limit_by,
+ } => union_spans(
+ limit
+ .iter()
+ .map(|i| i.span())
+ .chain(offset.as_ref().map(|i| i.span()))
+ .chain(limit_by.iter().map(|i| i.span())),
+ ),
+ LimitClause::OffsetCommaLimit { offset, limit } =>
offset.span().union(&limit.span()),
+ }
+ }
+}
+
impl Spanned for Offset {
fn span(&self) -> Span {
let Offset {
diff --git a/src/ast/visitor.rs b/src/ast/visitor.rs
index a5d355fe..50985a3e 100644
--- a/src/ast/visitor.rs
+++ b/src/ast/visitor.rs
@@ -523,7 +523,7 @@ where
/// // Remove all select limits in sub-queries
/// visit_expressions_mut(&mut statements, |expr| {
/// if let Expr::Subquery(q) = expr {
-/// q.limit = None
+/// q.limit_clause = None;
/// }
/// ControlFlow::<()>::Continue(())
/// });
@@ -647,7 +647,7 @@ where
/// // Remove all select limits in outer statements (not in sub-queries)
/// visit_statements_mut(&mut statements, |stmt| {
/// if let Statement::Query(q) = stmt {
-/// q.limit = None
+/// q.limit_clause = None;
/// }
/// ControlFlow::<()>::Continue(())
/// });
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index 32a7bccd..d3c48a6e 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -9491,6 +9491,60 @@ impl<'a> Parser<'a> {
}
}
+ fn parse_optional_limit_clause(&mut self) -> Result<Option<LimitClause>,
ParserError> {
+ let mut offset = if self.parse_keyword(Keyword::OFFSET) {
+ Some(self.parse_offset()?)
+ } else {
+ None
+ };
+
+ let (limit, limit_by) = if self.parse_keyword(Keyword::LIMIT) {
+ let expr = self.parse_limit()?;
+
+ if self.dialect.supports_limit_comma()
+ && offset.is_none()
+ && expr.is_some() // ALL not supported with comma
+ && self.consume_token(&Token::Comma)
+ {
+ let offset = expr.ok_or_else(|| {
+ ParserError::ParserError(
+ "Missing offset for LIMIT <offset>,
<limit>".to_string(),
+ )
+ })?;
+ return Ok(Some(LimitClause::OffsetCommaLimit {
+ offset,
+ limit: self.parse_expr()?,
+ }));
+ }
+
+ let limit_by = if dialect_of!(self is ClickHouseDialect |
GenericDialect)
+ && self.parse_keyword(Keyword::BY)
+ {
+ Some(self.parse_comma_separated(Parser::parse_expr)?)
+ } else {
+ None
+ };
+
+ (Some(expr), limit_by)
+ } else {
+ (None, None)
+ };
+
+ if offset.is_none() && limit.is_some() &&
self.parse_keyword(Keyword::OFFSET) {
+ offset = Some(self.parse_offset()?);
+ }
+
+ if offset.is_some() || (limit.is_some() && limit != Some(None)) ||
limit_by.is_some() {
+ Ok(Some(LimitClause::LimitOffset {
+ limit: limit.unwrap_or_default(),
+ offset,
+ limit_by: limit_by.unwrap_or_default(),
+ }))
+ } else {
+ Ok(None)
+ }
+ }
+
/// Parse a table object for insertion
/// e.g. `some_database.some_table` or `FUNCTION some_table_func(...)`
pub fn parse_table_object(&mut self) -> Result<TableObject, ParserError> {
@@ -10231,10 +10285,8 @@ impl<'a> Parser<'a> {
Ok(Query {
with,
body: self.parse_insert_setexpr_boxed()?,
- limit: None,
- limit_by: vec![],
order_by: None,
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -10246,10 +10298,8 @@ impl<'a> Parser<'a> {
Ok(Query {
with,
body: self.parse_update_setexpr_boxed()?,
- limit: None,
- limit_by: vec![],
order_by: None,
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -10261,10 +10311,8 @@ impl<'a> Parser<'a> {
Ok(Query {
with,
body: self.parse_delete_setexpr_boxed()?,
- limit: None,
- limit_by: vec![],
+ limit_clause: None,
order_by: None,
- offset: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -10277,40 +10325,7 @@ impl<'a> Parser<'a> {
let order_by = self.parse_optional_order_by()?;
- let mut limit = None;
- let mut offset = None;
-
- for _x in 0..2 {
- if limit.is_none() && self.parse_keyword(Keyword::LIMIT) {
- limit = self.parse_limit()?
- }
-
- if offset.is_none() && self.parse_keyword(Keyword::OFFSET) {
- offset = Some(self.parse_offset()?)
- }
-
- if self.dialect.supports_limit_comma()
- && limit.is_some()
- && offset.is_none()
- && self.consume_token(&Token::Comma)
- {
- // MySQL style LIMIT x,y => LIMIT y OFFSET x.
- // Check
<https://dev.mysql.com/doc/refman/8.0/en/select.html> for more details.
- offset = Some(Offset {
- value: limit.unwrap(),
- rows: OffsetRows::None,
- });
- limit = Some(self.parse_expr()?);
- }
- }
-
- let limit_by = if dialect_of!(self is ClickHouseDialect |
GenericDialect)
- && self.parse_keyword(Keyword::BY)
- {
- self.parse_comma_separated(Parser::parse_expr)?
- } else {
- vec![]
- };
+ let limit_clause = self.parse_optional_limit_clause()?;
let settings = self.parse_settings()?;
@@ -10347,9 +10362,7 @@ impl<'a> Parser<'a> {
with,
body,
order_by,
- limit,
- limit_by,
- offset,
+ limit_clause,
fetch,
locks,
for_clause,
@@ -11809,9 +11822,7 @@ impl<'a> Parser<'a> {
with: None,
body: Box::new(values),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
diff --git a/tests/sqlparser_clickhouse.rs b/tests/sqlparser_clickhouse.rs
index 72a64a48..c56f9886 100644
--- a/tests/sqlparser_clickhouse.rs
+++ b/tests/sqlparser_clickhouse.rs
@@ -944,6 +944,12 @@ fn parse_limit_by() {
clickhouse_and_generic().verified_stmt(
r#"SELECT * FROM default.last_asset_runs_mv ORDER BY created_at DESC
LIMIT 1 BY asset, toStartOfDay(created_at)"#,
);
+ clickhouse_and_generic().parse_sql_statements(
+ r#"SELECT * FROM default.last_asset_runs_mv ORDER BY created_at DESC
BY asset, toStartOfDay(created_at)"#,
+ ).expect_err("BY without LIMIT");
+ clickhouse_and_generic()
+ .parse_sql_statements("SELECT * FROM T OFFSET 5 BY foo")
+ .expect_err("BY with OFFSET but without LIMIT");
}
#[test]
@@ -1107,7 +1113,14 @@ fn parse_select_order_by_with_fill_interpolate() {
},
select.order_by.expect("ORDER BY expected")
);
- assert_eq!(Some(Expr::value(number("2"))), select.limit);
+ assert_eq!(
+ select.limit_clause,
+ Some(LimitClause::LimitOffset {
+ limit: Some(Expr::value(number("2"))),
+ offset: None,
+ limit_by: vec![]
+ })
+ );
}
#[test]
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index c7bf287c..b5d42ea6 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -483,9 +483,7 @@ fn parse_update_set_from() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -900,7 +898,12 @@ fn parse_simple_select() {
assert!(select.distinct.is_none());
assert_eq!(3, select.projection.len());
let select = verified_query(sql);
- assert_eq!(Some(Expr::value(number("5"))), select.limit);
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: Some(Expr::value(number("5"))),
+ offset: None,
+ limit_by: vec![],
+ };
+ assert_eq!(Some(expected_limit_clause), select.limit_clause);
}
#[test]
@@ -908,14 +911,31 @@ fn parse_limit() {
verified_stmt("SELECT * FROM user LIMIT 1");
}
+#[test]
+fn parse_invalid_limit_by() {
+ all_dialects()
+ .parse_sql_statements("SELECT * FROM user BY name")
+ .expect_err("BY without LIMIT");
+}
+
#[test]
fn parse_limit_is_not_an_alias() {
// In dialects supporting LIMIT it shouldn't be parsed as a table alias
let ast = verified_query("SELECT id FROM customer LIMIT 1");
- assert_eq!(Some(Expr::value(number("1"))), ast.limit);
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: Some(Expr::value(number("1"))),
+ offset: None,
+ limit_by: vec![],
+ };
+ assert_eq!(Some(expected_limit_clause), ast.limit_clause);
let ast = verified_query("SELECT 1 LIMIT 5");
- assert_eq!(Some(Expr::value(number("5"))), ast.limit);
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: Some(Expr::value(number("5"))),
+ offset: None,
+ limit_by: vec![],
+ };
+ assert_eq!(Some(expected_limit_clause), ast.limit_clause);
}
#[test]
@@ -2493,7 +2513,12 @@ fn parse_select_order_by_limit() {
]),
select.order_by.expect("ORDER BY expected").kind
);
- assert_eq!(Some(Expr::value(number("2"))), select.limit);
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: Some(Expr::value(number("2"))),
+ offset: None,
+ limit_by: vec![],
+ };
+ assert_eq!(Some(expected_limit_clause), select.limit_clause);
}
#[test]
@@ -2654,7 +2679,12 @@ fn parse_select_order_by_nulls_order() {
]),
select.order_by.expect("ORDER BY expeccted").kind
);
- assert_eq!(Some(Expr::value(number("2"))), select.limit);
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: Some(Expr::value(number("2"))),
+ offset: None,
+ limit_by: vec![],
+ };
+ assert_eq!(Some(expected_limit_clause), select.limit_clause);
}
#[test]
@@ -2864,6 +2894,14 @@ fn parse_limit_accepts_all() {
"SELECT id, fname, lname FROM customer WHERE id = 1 LIMIT ALL",
"SELECT id, fname, lname FROM customer WHERE id = 1",
);
+ one_statement_parses_to(
+ "SELECT id, fname, lname FROM customer WHERE id = 1 LIMIT ALL OFFSET
1",
+ "SELECT id, fname, lname FROM customer WHERE id = 1 OFFSET 1",
+ );
+ one_statement_parses_to(
+ "SELECT id, fname, lname FROM customer WHERE id = 1 OFFSET 1 LIMIT
ALL",
+ "SELECT id, fname, lname FROM customer WHERE id = 1 OFFSET 1",
+ );
}
#[test]
@@ -4247,9 +4285,7 @@ fn parse_create_table_as_table() {
schema_name: None,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -4274,9 +4310,7 @@ fn parse_create_table_as_table() {
schema_name: Some("schema_name".to_string()),
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -6273,9 +6307,7 @@ fn parse_interval_and_or_xor() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -8175,55 +8207,65 @@ fn parse_offset() {
let dialects =
all_dialects_where(|d| !d.is_column_alias(&Keyword::OFFSET, &mut
Parser::new(d)));
- let expect = Some(Offset {
- value: Expr::value(number("2")),
- rows: OffsetRows::Rows,
+ let expected_limit_clause = &Some(LimitClause::LimitOffset {
+ limit: None,
+ offset: Some(Offset {
+ value: Expr::value(number("2")),
+ rows: OffsetRows::Rows,
+ }),
+ limit_by: vec![],
});
let ast = dialects.verified_query("SELECT foo FROM bar OFFSET 2 ROWS");
- assert_eq!(ast.offset, expect);
+ assert_eq!(&ast.limit_clause, expected_limit_clause);
let ast = dialects.verified_query("SELECT foo FROM bar WHERE foo = 4
OFFSET 2 ROWS");
- assert_eq!(ast.offset, expect);
+ assert_eq!(&ast.limit_clause, expected_limit_clause);
let ast = dialects.verified_query("SELECT foo FROM bar ORDER BY baz OFFSET
2 ROWS");
- assert_eq!(ast.offset, expect);
+ assert_eq!(&ast.limit_clause, expected_limit_clause);
let ast =
dialects.verified_query("SELECT foo FROM bar WHERE foo = 4 ORDER BY
baz OFFSET 2 ROWS");
- assert_eq!(ast.offset, expect);
+ assert_eq!(&ast.limit_clause, expected_limit_clause);
let ast =
dialects.verified_query("SELECT foo FROM (SELECT * FROM bar OFFSET 2
ROWS) OFFSET 2 ROWS");
- assert_eq!(ast.offset, expect);
+ assert_eq!(&ast.limit_clause, expected_limit_clause);
match *ast.body {
SetExpr::Select(s) => match only(s.from).relation {
TableFactor::Derived { subquery, .. } => {
- assert_eq!(subquery.offset, expect);
+ assert_eq!(&subquery.limit_clause, expected_limit_clause);
}
_ => panic!("Test broke"),
},
_ => panic!("Test broke"),
}
- let ast = dialects.verified_query("SELECT 'foo' OFFSET 0 ROWS");
- assert_eq!(
- ast.offset,
- Some(Offset {
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: None,
+ offset: Some(Offset {
value: Expr::value(number("0")),
rows: OffsetRows::Rows,
- })
- );
- let ast = dialects.verified_query("SELECT 'foo' OFFSET 1 ROW");
- assert_eq!(
- ast.offset,
- Some(Offset {
+ }),
+ limit_by: vec![],
+ };
+ let ast = dialects.verified_query("SELECT 'foo' OFFSET 0 ROWS");
+ assert_eq!(ast.limit_clause, Some(expected_limit_clause));
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: None,
+ offset: Some(Offset {
value: Expr::value(number("1")),
rows: OffsetRows::Row,
- })
- );
- let ast = dialects.verified_query("SELECT 'foo' OFFSET 1");
- assert_eq!(
- ast.offset,
- Some(Offset {
- value: Expr::value(number("1")),
+ }),
+ limit_by: vec![],
+ };
+ let ast = dialects.verified_query("SELECT 'foo' OFFSET 1 ROW");
+ assert_eq!(ast.limit_clause, Some(expected_limit_clause));
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: None,
+ offset: Some(Offset {
+ value: Expr::value(number("2")),
rows: OffsetRows::None,
- })
- );
+ }),
+ limit_by: vec![],
+ };
+ let ast = dialects.verified_query("SELECT 'foo' OFFSET 2");
+ assert_eq!(ast.limit_clause, Some(expected_limit_clause));
}
#[test]
@@ -8273,13 +8315,15 @@ fn parse_fetch() {
let ast = verified_query(
"SELECT foo FROM bar WHERE foo = 4 ORDER BY baz OFFSET 2 ROWS FETCH
FIRST 2 ROWS ONLY",
);
- assert_eq!(
- ast.offset,
- Some(Offset {
+ let expected_limit_clause = Some(LimitClause::LimitOffset {
+ limit: None,
+ offset: Some(Offset {
value: Expr::value(number("2")),
rows: OffsetRows::Rows,
- })
- );
+ }),
+ limit_by: vec![],
+ });
+ assert_eq!(ast.limit_clause, expected_limit_clause);
assert_eq!(ast.fetch, fetch_first_two_rows_only);
let ast = verified_query(
"SELECT foo FROM (SELECT * FROM bar FETCH FIRST 2 ROWS ONLY) FETCH
FIRST 2 ROWS ONLY",
@@ -8295,24 +8339,20 @@ fn parse_fetch() {
_ => panic!("Test broke"),
}
let ast = verified_query("SELECT foo FROM (SELECT * FROM bar OFFSET 2 ROWS
FETCH FIRST 2 ROWS ONLY) OFFSET 2 ROWS FETCH FIRST 2 ROWS ONLY");
- assert_eq!(
- ast.offset,
- Some(Offset {
+ let expected_limit_clause = &Some(LimitClause::LimitOffset {
+ limit: None,
+ offset: Some(Offset {
value: Expr::value(number("2")),
rows: OffsetRows::Rows,
- })
- );
+ }),
+ limit_by: vec![],
+ });
+ assert_eq!(&ast.limit_clause, expected_limit_clause);
assert_eq!(ast.fetch, fetch_first_two_rows_only);
match *ast.body {
SetExpr::Select(s) => match only(s.from).relation {
TableFactor::Derived { subquery, .. } => {
- assert_eq!(
- subquery.offset,
- Some(Offset {
- value: Expr::value(number("2")),
- rows: OffsetRows::Rows,
- })
- );
+ assert_eq!(&subquery.limit_clause, expected_limit_clause);
assert_eq!(subquery.fetch, fetch_first_two_rows_only);
}
_ => panic!("Test broke"),
@@ -9358,9 +9398,7 @@ fn parse_merge() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -9678,21 +9716,18 @@ fn test_placeholder() {
})
);
- let sql = "SELECT * FROM student LIMIT $1 OFFSET $2";
- let ast = dialects.verified_query(sql);
- assert_eq!(
- ast.limit,
- Some(Expr::Value(
- (Value::Placeholder("$1".into())).with_empty_span()
- ))
- );
- assert_eq!(
- ast.offset,
- Some(Offset {
+ let ast = dialects.verified_query("SELECT * FROM student LIMIT $1 OFFSET
$2");
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: Some(Expr::Value(
+ (Value::Placeholder("$1".into())).with_empty_span(),
+ )),
+ offset: Some(Offset {
value:
Expr::Value((Value::Placeholder("$2".into())).with_empty_span()),
rows: OffsetRows::None,
}),
- );
+ limit_by: vec![],
+ };
+ assert_eq!(ast.limit_clause, Some(expected_limit_clause));
let dialects = TestedDialects::new(vec![
Box::new(GenericDialect {}),
@@ -9772,40 +9807,34 @@ fn verified_expr(query: &str) -> Expr {
#[test]
fn parse_offset_and_limit() {
let sql = "SELECT foo FROM bar LIMIT 1 OFFSET 2";
- let expect = Some(Offset {
- value: Expr::value(number("2")),
- rows: OffsetRows::None,
+ let expected_limit_clause = Some(LimitClause::LimitOffset {
+ limit: Some(Expr::value(number("1"))),
+ offset: Some(Offset {
+ value: Expr::value(number("2")),
+ rows: OffsetRows::None,
+ }),
+ limit_by: vec![],
});
let ast = verified_query(sql);
- assert_eq!(ast.offset, expect);
- assert_eq!(ast.limit, Some(Expr::value(number("1"))));
+ assert_eq!(ast.limit_clause, expected_limit_clause);
// different order is OK
one_statement_parses_to("SELECT foo FROM bar OFFSET 2 LIMIT 1", sql);
// mysql syntax is ok for some dialects
- TestedDialects::new(vec![
- Box::new(GenericDialect {}),
- Box::new(MySqlDialect {}),
- Box::new(SQLiteDialect {}),
- Box::new(ClickHouseDialect {}),
- ])
- .one_statement_parses_to("SELECT foo FROM bar LIMIT 2, 1", sql);
+ all_dialects_where(|d| d.supports_limit_comma())
+ .verified_query("SELECT foo FROM bar LIMIT 2, 1");
// expressions are allowed
let sql = "SELECT foo FROM bar LIMIT 1 + 2 OFFSET 3 * 4";
let ast = verified_query(sql);
- assert_eq!(
- ast.limit,
- Some(Expr::BinaryOp {
+ let expected_limit_clause = LimitClause::LimitOffset {
+ limit: Some(Expr::BinaryOp {
left: Box::new(Expr::value(number("1"))),
op: BinaryOperator::Plus,
right: Box::new(Expr::value(number("2"))),
}),
- );
- assert_eq!(
- ast.offset,
- Some(Offset {
+ offset: Some(Offset {
value: Expr::BinaryOp {
left: Box::new(Expr::value(number("3"))),
op: BinaryOperator::Multiply,
@@ -9813,7 +9842,12 @@ fn parse_offset_and_limit() {
},
rows: OffsetRows::None,
}),
- );
+ limit_by: vec![],
+ };
+ assert_eq!(ast.limit_clause, Some(expected_limit_clause),);
+
+ // OFFSET without LIMIT
+ verified_stmt("SELECT foo FROM bar OFFSET 2");
// Can't repeat OFFSET / LIMIT
let res = parse_sql_statements("SELECT foo FROM bar OFFSET 2 OFFSET 2");
@@ -11227,9 +11261,7 @@ fn parse_unload() {
flavor: SelectFlavor::Standard,
}))),
with: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -12400,9 +12432,7 @@ fn test_extract_seconds_ok() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -14265,11 +14295,9 @@ fn test_select_from_first() {
flavor,
}))),
order_by: None,
- limit: None,
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
- limit_by: vec![],
for_clause: None,
settings: None,
format_clause: None,
diff --git a/tests/sqlparser_mssql.rs b/tests/sqlparser_mssql.rs
index 386bd178..af71d252 100644
--- a/tests/sqlparser_mssql.rs
+++ b/tests/sqlparser_mssql.rs
@@ -107,9 +107,7 @@ fn parse_create_procedure() {
or_alter: true,
body: vec![Statement::Query(Box::new(Query {
with: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1161,9 +1159,7 @@ fn parse_substring_in_select() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1264,9 +1260,7 @@ fn parse_mssql_declare() {
}),
Statement::Query(Box::new(Query {
with: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
diff --git a/tests/sqlparser_mysql.rs b/tests/sqlparser_mysql.rs
index 13a8a6cc..a5633593 100644
--- a/tests/sqlparser_mysql.rs
+++ b/tests/sqlparser_mysql.rs
@@ -1107,9 +1107,7 @@ fn parse_escaped_quote_identifiers_with_escape() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1161,9 +1159,7 @@ fn parse_escaped_quote_identifiers_with_no_escape() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1209,9 +1205,7 @@ fn parse_escaped_backticks_with_escape() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1261,9 +1255,7 @@ fn parse_escaped_backticks_with_no_escape() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1438,9 +1430,7 @@ fn parse_simple_insert() {
]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1488,9 +1478,7 @@ fn parse_ignore_insert() {
]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1538,9 +1526,7 @@ fn parse_priority_insert() {
]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1585,9 +1571,7 @@ fn parse_priority_insert() {
]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1634,9 +1618,7 @@ fn parse_insert_as() {
)]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1698,9 +1680,7 @@ fn parse_insert_as() {
]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1749,9 +1729,7 @@ fn parse_replace_insert() {
]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1791,9 +1769,7 @@ fn parse_empty_row_insert() {
rows: vec![vec![], vec![]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -1857,9 +1833,7 @@ fn parse_insert_with_on_duplicate_update() {
]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -2596,9 +2570,7 @@ fn parse_substring_in_select() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -2737,10 +2709,8 @@ fn parse_set_names() {
#[test]
fn parse_limit_my_sql_syntax() {
- mysql_and_generic().one_statement_parses_to(
- "SELECT id, fname, lname FROM customer LIMIT 5, 10",
- "SELECT id, fname, lname FROM customer LIMIT 10 OFFSET 5",
- );
+ mysql_and_generic().verified_stmt("SELECT id, fname, lname FROM customer
LIMIT 10 OFFSET 5");
+ mysql_and_generic().verified_stmt("SELECT id, fname, lname FROM customer
LIMIT 5, 10");
mysql_and_generic().verified_stmt("SELECT * FROM user LIMIT ? OFFSET ?");
}
@@ -2903,9 +2873,7 @@ fn parse_hex_string_introducer() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs
index a65c4fa3..1a98870f 100644
--- a/tests/sqlparser_postgres.rs
+++ b/tests/sqlparser_postgres.rs
@@ -1319,9 +1319,7 @@ fn parse_copy_to() {
flavor: SelectFlavor::Standard,
}))),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -2955,9 +2953,7 @@ fn parse_array_subquery_expr() {
}))),
}),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -4747,9 +4743,7 @@ fn test_simple_postgres_insert_with_alias() {
]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -4820,9 +4814,7 @@ fn test_simple_postgres_insert_with_alias() {
]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
@@ -4891,9 +4883,7 @@ fn test_simple_insert_with_quoted_alias() {
]]
})),
order_by: None,
- limit: None,
- limit_by: vec![],
- offset: None,
+ limit_clause: None,
fetch: None,
locks: vec![],
for_clause: None,
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
