This is an automated email from the ASF dual-hosted git repository. github-bot pushed a commit to branch gh-readonly-queue/main/pr-2172-3ac567076ce505adb033f08a2044cfe5c3a7b229 in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
commit 5e5c16c597ed6dd1d6539d4c0c701972b166921d Author: Michael Victor Zink <[email protected]> AuthorDate: Thu Feb 5 07:40:58 2026 -0800 MySQL: Add support for `SELECT` modifiers (#2172) --- src/ast/mod.rs | 17 ++-- src/ast/query.rs | 119 +++++++++++++++++++++++++- src/ast/spans.rs | 3 +- src/dialect/mod.rs | 13 +++ src/dialect/mysql.rs | 4 + src/keywords.rs | 6 ++ src/parser/mod.rs | 110 +++++++++++++++++++++--- tests/sqlparser_bigquery.rs | 2 + tests/sqlparser_clickhouse.rs | 5 +- tests/sqlparser_common.rs | 34 ++++++-- tests/sqlparser_duckdb.rs | 2 + tests/sqlparser_mssql.rs | 3 + tests/sqlparser_mysql.rs | 189 ++++++++++++++++++++++++++++++++++++++++++ tests/sqlparser_postgres.rs | 3 + 14 files changed, 480 insertions(+), 30 deletions(-) diff --git a/src/ast/mod.rs b/src/ast/mod.rs index ce5a67e1..1e626916 100644 --- a/src/ast/mod.rs +++ b/src/ast/mod.rs @@ -97,14 +97,15 @@ pub use self::query::{ OffsetRows, OpenJsonTableColumn, OrderBy, OrderByExpr, OrderByKind, OrderByOptions, PipeOperator, 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, XmlNamespaceDefinition, - XmlPassingArgument, XmlPassingClause, XmlTableColumn, XmlTableColumnOption, + SelectFlavor, SelectInto, SelectItem, SelectItemQualifiedWildcardKind, SelectModifiers, + 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, + XmlNamespaceDefinition, XmlPassingArgument, XmlPassingClause, XmlTableColumn, + XmlTableColumnOption, }; pub use self::trigger::{ diff --git a/src/ast/query.rs b/src/ast/query.rs index 08448cab..bb2d889f 100644 --- a/src/ast/query.rs +++ b/src/ast/query.rs @@ -334,6 +334,108 @@ pub enum SelectFlavor { FromFirstNoSelect, } +/// MySQL-specific SELECT modifiers that appear after the SELECT keyword. +/// +/// These modifiers affect query execution and optimization. They can appear in any order after +/// SELECT and before the column list, can be repeated, and can be interleaved with +/// DISTINCT/DISTINCTROW/ALL: +/// +/// ```sql +/// SELECT +/// [ALL | DISTINCT | DISTINCTROW] +/// [HIGH_PRIORITY] +/// [STRAIGHT_JOIN] +/// [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] +/// [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] +/// select_expr [, select_expr] ... +/// ``` +/// +/// See [MySQL SELECT](https://dev.mysql.com/doc/refman/8.4/en/select.html). +#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash, Default)] +#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] +#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] +pub struct SelectModifiers { + /// `HIGH_PRIORITY` gives the SELECT higher priority than statements that update a table. + /// + /// <https://dev.mysql.com/doc/refman/8.4/en/select.html> + pub high_priority: bool, + /// `STRAIGHT_JOIN` forces the optimizer to join tables in the order listed in the FROM clause. + /// + /// <https://dev.mysql.com/doc/refman/8.4/en/select.html> + pub straight_join: bool, + /// `SQL_SMALL_RESULT` hints that the result set is small, using in-memory temp tables. + /// + /// <https://dev.mysql.com/doc/refman/8.4/en/select.html> + pub sql_small_result: bool, + /// `SQL_BIG_RESULT` hints that the result set is large, using disk-based temp tables. + /// + /// <https://dev.mysql.com/doc/refman/8.4/en/select.html> + pub sql_big_result: bool, + /// `SQL_BUFFER_RESULT` forces the result to be put into a temporary table to release locks early. + /// + /// <https://dev.mysql.com/doc/refman/8.4/en/select.html> + pub sql_buffer_result: bool, + /// `SQL_NO_CACHE` tells MySQL not to cache the query result. (Deprecated in 8.4+.) + /// + /// <https://dev.mysql.com/doc/refman/8.4/en/select.html> + pub sql_no_cache: bool, + /// `SQL_CALC_FOUND_ROWS` tells MySQL to calculate the total number of rows. (Deprecated in 8.0.17+.) + /// + /// - [MySQL SELECT modifiers](https://dev.mysql.com/doc/refman/8.4/en/select.html) + /// - [`FOUND_ROWS()`](https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_found-rows) + pub sql_calc_found_rows: bool, +} + +impl fmt::Display for SelectModifiers { + fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result { + if self.high_priority { + f.write_str(" HIGH_PRIORITY")?; + } + if self.straight_join { + f.write_str(" STRAIGHT_JOIN")?; + } + if self.sql_small_result { + f.write_str(" SQL_SMALL_RESULT")?; + } + if self.sql_big_result { + f.write_str(" SQL_BIG_RESULT")?; + } + if self.sql_buffer_result { + f.write_str(" SQL_BUFFER_RESULT")?; + } + if self.sql_no_cache { + f.write_str(" SQL_NO_CACHE")?; + } + if self.sql_calc_found_rows { + f.write_str(" SQL_CALC_FOUND_ROWS")?; + } + Ok(()) + } +} + +impl SelectModifiers { + /// Returns true if any of the modifiers are set. + pub fn is_any_set(&self) -> bool { + // Using irrefutable destructuring to catch fields added in the future + let Self { + high_priority, + straight_join, + sql_small_result, + sql_big_result, + sql_buffer_result, + sql_no_cache, + sql_calc_found_rows, + } = self; + *high_priority + || *straight_join + || *sql_small_result + || *sql_big_result + || *sql_buffer_result + || *sql_no_cache + || *sql_calc_found_rows + } +} + /// A restricted variant of `SELECT` (without CTEs/`ORDER BY`), which may /// appear either as the only body item of a `Query`, or as an operand /// to a set operation like `UNION`. @@ -350,6 +452,10 @@ pub struct Select { pub optimizer_hint: Option<OptimizerHint>, /// `SELECT [DISTINCT] ...` pub distinct: Option<Distinct>, + /// MySQL-specific SELECT modifiers. + /// + /// See [MySQL SELECT](https://dev.mysql.com/doc/refman/8.4/en/select.html). + pub select_modifiers: Option<SelectModifiers>, /// MSSQL syntax: `TOP (<N>) [ PERCENT ] [ WITH TIES ]` pub top: Option<Top>, /// Whether the top was located before `ALL`/`DISTINCT` @@ -442,6 +548,10 @@ impl fmt::Display for Select { } } + if let Some(ref select_modifiers) = self.select_modifiers { + select_modifiers.fmt(f)?; + } + if !self.projection.is_empty() { indented_list(f, &self.projection)?; } @@ -3351,8 +3461,14 @@ impl fmt::Display for NonBlock { #[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)] #[cfg_attr(feature = "serde", derive(Serialize, Deserialize))] #[cfg_attr(feature = "visitor", derive(Visit, VisitMut))] -/// `DISTINCT` or `DISTINCT ON (...)` modifiers for `SELECT` lists. +/// `ALL`, `DISTINCT`, or `DISTINCT ON (...)` modifiers for `SELECT` lists. pub enum Distinct { + /// `ALL` (keep duplicate rows) + /// + /// Generally this is the default if omitted, but omission should be represented as + /// `None::<Option<Distinct>>` + All, + /// `DISTINCT` (remove duplicate rows) Distinct, @@ -3363,6 +3479,7 @@ pub enum Distinct { impl fmt::Display for Distinct { fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result { match self { + Distinct::All => write!(f, "ALL"), Distinct::Distinct => write!(f, "DISTINCT"), Distinct::On(col_names) => { let col_names = display_comma_separated(col_names); diff --git a/src/ast/spans.rs b/src/ast/spans.rs index 60c983fa..ffc96ed3 100644 --- a/src/ast/spans.rs +++ b/src/ast/spans.rs @@ -2238,7 +2238,8 @@ impl Spanned for Select { select_token, optimizer_hint: _, distinct: _, // todo - top: _, // todo, mysql specific + select_modifiers: _, + top: _, // todo, mysql specific projection, exclude: _, into, diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs index 477d60f8..f23eb387 100644 --- a/src/dialect/mod.rs +++ b/src/dialect/mod.rs @@ -698,6 +698,19 @@ pub trait Dialect: Debug + Any { false } + /// Returns true if the dialect supports MySQL-specific SELECT modifiers + /// like `HIGH_PRIORITY`, `STRAIGHT_JOIN`, `SQL_SMALL_RESULT`, etc. + /// + /// For example: + /// ```sql + /// SELECT HIGH_PRIORITY STRAIGHT_JOIN SQL_SMALL_RESULT * FROM t1 JOIN t2 ON ... + /// ``` + /// + /// [MySQL](https://dev.mysql.com/doc/refman/8.4/en/select.html) + fn supports_select_modifiers(&self) -> bool { + false + } + /// Dialect-specific infix parser override /// /// This method is called to parse the next infix expression. diff --git a/src/dialect/mysql.rs b/src/dialect/mysql.rs index ad3ba6f3..e1a68417 100644 --- a/src/dialect/mysql.rs +++ b/src/dialect/mysql.rs @@ -156,6 +156,10 @@ impl Dialect for MySqlDialect { true } + fn supports_select_modifiers(&self) -> bool { + true + } + fn supports_set_names(&self) -> bool { true } diff --git a/src/keywords.rs b/src/keywords.rs index 964e4b38..2e26bda8 100644 --- a/src/keywords.rs +++ b/src/keywords.rs @@ -333,6 +333,7 @@ define_keywords!( DISCARD, DISCONNECT, DISTINCT, + DISTINCTROW, DISTRIBUTE, DIV, DO, @@ -956,6 +957,11 @@ define_keywords!( SQLEXCEPTION, SQLSTATE, SQLWARNING, + SQL_BIG_RESULT, + SQL_BUFFER_RESULT, + SQL_CALC_FOUND_ROWS, + SQL_NO_CACHE, + SQL_SMALL_RESULT, SQRT, SRID, STABLE, diff --git a/src/parser/mod.rs b/src/parser/mod.rs index 5fa224f9..d9a5c518 100644 --- a/src/parser/mod.rs +++ b/src/parser/mod.rs @@ -4927,16 +4927,27 @@ impl<'a> Parser<'a> { /// and results in a [`ParserError`] if both `ALL` and `DISTINCT` are found. pub fn parse_all_or_distinct(&mut self) -> Result<Option<Distinct>, ParserError> { let loc = self.peek_token().span.start; - let all = self.parse_keyword(Keyword::ALL); - let distinct = self.parse_keyword(Keyword::DISTINCT); - if !distinct { - return Ok(None); - } - if all { - return parser_err!("Cannot specify both ALL and DISTINCT".to_string(), loc); - } - let on = self.parse_keyword(Keyword::ON); - if !on { + let distinct = match self.parse_one_of_keywords(&[Keyword::ALL, Keyword::DISTINCT]) { + Some(Keyword::ALL) => { + if self.peek_keyword(Keyword::DISTINCT) { + return parser_err!("Cannot specify ALL then DISTINCT".to_string(), loc); + } + Some(Distinct::All) + } + Some(Keyword::DISTINCT) => { + if self.peek_keyword(Keyword::ALL) { + return parser_err!("Cannot specify DISTINCT then ALL".to_string(), loc); + } + Some(Distinct::Distinct) + } + None => return Ok(None), + _ => return parser_err!("ALL or DISTINCT", loc), + }; + + let Some(Distinct::Distinct) = distinct else { + return Ok(distinct); + }; + if !self.parse_keyword(Keyword::ON) { return Ok(Some(Distinct::Distinct)); } @@ -13861,6 +13872,7 @@ impl<'a> Parser<'a> { select_token: AttachedToken(from_token), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![], @@ -13890,13 +13902,26 @@ impl<'a> Parser<'a> { let optimizer_hint = self.maybe_parse_optimizer_hint()?; let value_table_mode = self.parse_value_table_mode()?; + let (select_modifiers, distinct_select_modifier) = + if self.dialect.supports_select_modifiers() { + self.parse_select_modifiers()? + } else { + (None, None) + }; + let mut top_before_distinct = false; let mut top = None; if self.dialect.supports_top_before_distinct() && self.parse_keyword(Keyword::TOP) { top = Some(self.parse_top()?); top_before_distinct = true; } - let distinct = self.parse_all_or_distinct()?; + + let distinct = if distinct_select_modifier.is_some() { + distinct_select_modifier + } else { + self.parse_all_or_distinct()? + }; + if !self.dialect.supports_top_before_distinct() && self.parse_keyword(Keyword::TOP) { top = Some(self.parse_top()?); } @@ -14044,6 +14069,7 @@ impl<'a> Parser<'a> { select_token: AttachedToken(select_token), optimizer_hint, distinct, + select_modifiers, top, top_before_distinct, projection, @@ -14120,6 +14146,68 @@ impl<'a> Parser<'a> { } } + /// Parses MySQL SELECT modifiers and DISTINCT/ALL in any order. + /// + /// Manual testing shows odifiers can appear in any order, and modifiers other than DISTINCT/ALL + /// can be repeated. + /// + /// <https://dev.mysql.com/doc/refman/8.4/en/select.html> + fn parse_select_modifiers( + &mut self, + ) -> Result<(Option<SelectModifiers>, Option<Distinct>), ParserError> { + let mut modifiers = SelectModifiers::default(); + let mut distinct = None; + + let keywords = &[ + Keyword::ALL, + Keyword::DISTINCT, + Keyword::DISTINCTROW, + Keyword::HIGH_PRIORITY, + Keyword::STRAIGHT_JOIN, + Keyword::SQL_SMALL_RESULT, + Keyword::SQL_BIG_RESULT, + Keyword::SQL_BUFFER_RESULT, + Keyword::SQL_NO_CACHE, + Keyword::SQL_CALC_FOUND_ROWS, + ]; + + while let Some(keyword) = self.parse_one_of_keywords(keywords) { + match keyword { + Keyword::ALL | Keyword::DISTINCT if distinct.is_none() => { + self.prev_token(); + distinct = self.parse_all_or_distinct()?; + } + // DISTINCTROW is a MySQL-specific legacy (but not deprecated) alias for DISTINCT + Keyword::DISTINCTROW if distinct.is_none() => { + distinct = Some(Distinct::Distinct); + } + Keyword::HIGH_PRIORITY => modifiers.high_priority = true, + Keyword::STRAIGHT_JOIN => modifiers.straight_join = true, + Keyword::SQL_SMALL_RESULT => modifiers.sql_small_result = true, + Keyword::SQL_BIG_RESULT => modifiers.sql_big_result = true, + Keyword::SQL_BUFFER_RESULT => modifiers.sql_buffer_result = true, + Keyword::SQL_NO_CACHE => modifiers.sql_no_cache = true, + Keyword::SQL_CALC_FOUND_ROWS => modifiers.sql_calc_found_rows = true, + _ => { + self.prev_token(); + return self.expected( + "HIGH_PRIORITY, STRAIGHT_JOIN, or other MySQL select modifier", + self.peek_token(), + ); + } + } + } + + // Avoid polluting the AST with `Some(SelectModifiers::default())` empty value unless there + // actually were some modifiers set. + let select_modifiers = if modifiers.is_any_set() { + Some(modifiers) + } else { + None + }; + Ok((select_modifiers, distinct)) + } + fn parse_value_table_mode(&mut self) -> Result<Option<ValueTableMode>, ParserError> { if !dialect_of!(self is BigQueryDialect) { return Ok(None); diff --git a/tests/sqlparser_bigquery.rs b/tests/sqlparser_bigquery.rs index fb28b4d2..d5ad6373 100644 --- a/tests/sqlparser_bigquery.rs +++ b/tests/sqlparser_bigquery.rs @@ -2683,6 +2683,7 @@ fn test_export_data() { )), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![ @@ -2788,6 +2789,7 @@ fn test_export_data() { )), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![ diff --git a/tests/sqlparser_clickhouse.rs b/tests/sqlparser_clickhouse.rs index ac31a278..db832911 100644 --- a/tests/sqlparser_clickhouse.rs +++ b/tests/sqlparser_clickhouse.rs @@ -40,9 +40,10 @@ fn parse_map_access_expr() { let select = clickhouse().verified_only_select(sql); assert_eq!( Select { - distinct: None, - optimizer_hint: None, select_token: AttachedToken::empty(), + optimizer_hint: None, + distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![UnnamedExpr(Expr::CompoundFieldAccess { diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs index e6a48c7b..b442ec42 100644 --- a/tests/sqlparser_common.rs +++ b/tests/sqlparser_common.rs @@ -473,6 +473,7 @@ fn parse_update_set_from() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![ @@ -1042,18 +1043,18 @@ fn parse_outer_join_operator() { #[test] fn parse_select_distinct_on() { let sql = "SELECT DISTINCT ON (album_id) name FROM track ORDER BY album_id, milliseconds"; - let select = verified_only_select(sql); + let select = all_dialects_except(|d| d.is::<MySqlDialect>()).verified_only_select(sql); assert_eq!( &Some(Distinct::On(vec![Expr::Identifier(Ident::new("album_id"))])), &select.distinct ); let sql = "SELECT DISTINCT ON () name FROM track ORDER BY milliseconds"; - let select = verified_only_select(sql); + let select = all_dialects_except(|d| d.is::<MySqlDialect>()).verified_only_select(sql); assert_eq!(&Some(Distinct::On(vec![])), &select.distinct); let sql = "SELECT DISTINCT ON (album_id, milliseconds) name FROM track"; - let select = verified_only_select(sql); + let select = all_dialects_except(|d| d.is::<MySqlDialect>()).verified_only_select(sql); assert_eq!( &Some(Distinct::On(vec![ Expr::Identifier(Ident::new("album_id")), @@ -1074,14 +1075,24 @@ fn parse_select_distinct_missing_paren() { #[test] fn parse_select_all() { - one_statement_parses_to("SELECT ALL name FROM customer", "SELECT name FROM customer"); + verified_stmt("SELECT ALL name FROM customer"); } #[test] fn parse_select_all_distinct() { let result = parse_sql_statements("SELECT ALL DISTINCT name FROM customer"); assert_eq!( - ParserError::ParserError("Cannot specify both ALL and DISTINCT".to_string()), + ParserError::ParserError("Cannot specify ALL then DISTINCT".to_string()), + result.unwrap_err(), + ); + let result = parse_sql_statements("SELECT DISTINCT ALL name FROM customer"); + assert_eq!( + ParserError::ParserError("Cannot specify DISTINCT then ALL".to_string()), + result.unwrap_err(), + ); + let result = parse_sql_statements("SELECT ALL DISTINCT ON(name) name FROM customer"); + assert_eq!( + ParserError::ParserError("Cannot specify ALL then DISTINCT".to_string()), result.unwrap_err(), ); } @@ -5809,6 +5820,7 @@ fn test_parse_named_window() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![ @@ -6540,6 +6552,7 @@ fn parse_interval_and_or_xor() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![UnnamedExpr(Expr::Identifier(Ident { @@ -8917,6 +8930,7 @@ fn lateral_function() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, projection: vec![SelectItem::Wildcard(WildcardAdditionalOptions::default())], exclude: None, @@ -9919,6 +9933,7 @@ fn parse_merge() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![SelectItem::Wildcard( @@ -12323,6 +12338,7 @@ fn parse_unload() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![UnnamedExpr(Expr::Identifier(Ident::new("cola"))),], @@ -12632,6 +12648,7 @@ fn parse_connect_by() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![ @@ -12715,6 +12732,7 @@ fn parse_connect_by() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![ @@ -13649,6 +13667,7 @@ fn test_extract_seconds_ok() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![UnnamedExpr(Expr::Extract { @@ -14830,9 +14849,9 @@ fn test_load_extension() { #[test] fn test_select_top() { let dialects = all_dialects_where(|d| d.supports_top_before_distinct()); - dialects.one_statement_parses_to("SELECT ALL * FROM tbl", "SELECT * FROM tbl"); + dialects.verified_stmt("SELECT ALL * FROM tbl"); dialects.verified_stmt("SELECT TOP 3 * FROM tbl"); - dialects.one_statement_parses_to("SELECT TOP 3 ALL * FROM tbl", "SELECT TOP 3 * FROM tbl"); + dialects.verified_stmt("SELECT TOP 3 ALL * FROM tbl"); dialects.verified_stmt("SELECT TOP 3 DISTINCT * FROM tbl"); dialects.verified_stmt("SELECT TOP 3 DISTINCT a, b, c FROM tbl"); } @@ -15789,6 +15808,7 @@ fn test_select_from_first() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, projection, exclude: None, diff --git a/tests/sqlparser_duckdb.rs b/tests/sqlparser_duckdb.rs index 7cc710de..e408d997 100644 --- a/tests/sqlparser_duckdb.rs +++ b/tests/sqlparser_duckdb.rs @@ -268,6 +268,7 @@ fn test_select_union_by_name() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, projection: vec![SelectItem::Wildcard(WildcardAdditionalOptions::default())], exclude: None, @@ -300,6 +301,7 @@ fn test_select_union_by_name() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, projection: vec![SelectItem::Wildcard(WildcardAdditionalOptions::default())], exclude: None, diff --git a/tests/sqlparser_mssql.rs b/tests/sqlparser_mssql.rs index d7700088..7f609342 100644 --- a/tests/sqlparser_mssql.rs +++ b/tests/sqlparser_mssql.rs @@ -143,6 +143,7 @@ fn parse_create_procedure() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![SelectItem::UnnamedExpr(Expr::Value( @@ -1351,6 +1352,7 @@ fn parse_substring_in_select() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: Some(Distinct::Distinct), + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![SelectItem::UnnamedExpr(Expr::Substring { @@ -1509,6 +1511,7 @@ fn parse_mssql_declare() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![SelectItem::UnnamedExpr(Expr::BinaryOp { diff --git a/tests/sqlparser_mysql.rs b/tests/sqlparser_mysql.rs index 2c942798..b719f2ef 100644 --- a/tests/sqlparser_mysql.rs +++ b/tests/sqlparser_mysql.rs @@ -1437,6 +1437,7 @@ fn parse_escaped_quote_identifiers_with_escape() { 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 { @@ -1493,6 +1494,7 @@ fn parse_escaped_quote_identifiers_with_no_escape() { 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 { @@ -1541,6 +1543,7 @@ fn parse_escaped_backticks_with_escape() { 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 { @@ -1593,6 +1596,7 @@ fn parse_escaped_backticks_with_no_escape() { 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 { @@ -2413,6 +2417,7 @@ fn parse_select_with_numeric_prefix_column_name() { 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( @@ -2588,6 +2593,7 @@ fn parse_select_with_concatenation_of_exp_number_and_numeric_prefix_column() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![ @@ -3222,6 +3228,7 @@ fn parse_substring_in_select() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: Some(Distinct::Distinct), + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![SelectItem::UnnamedExpr(Expr::Substring { @@ -3546,6 +3553,7 @@ fn parse_hex_string_introducer() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![SelectItem::UnnamedExpr(Expr::Prefixed { @@ -4272,6 +4280,187 @@ fn parse_straight_join() { .verified_stmt("SELECT a.*, b.* FROM table_a STRAIGHT_JOIN table_b AS b ON a.b_id = b.id"); } +#[test] +fn parse_distinctrow_to_distinct() { + mysql().one_statement_parses_to( + "SELECT DISTINCTROW * FROM employees", + "SELECT DISTINCT * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT HIGH_PRIORITY DISTINCTROW * FROM employees", + "SELECT DISTINCT HIGH_PRIORITY * FROM employees", + ); +} + +#[test] +fn parse_select_straight_join() { + let select = mysql().verified_only_select( + "SELECT STRAIGHT_JOIN * FROM employees e JOIN dept_emp d ON e.emp_no = d.emp_no WHERE d.emp_no = 10001", + ); + assert!(select.select_modifiers.unwrap().straight_join); + + mysql().verified_stmt( + "SELECT STRAIGHT_JOIN e.emp_no, d.dept_no FROM employees e JOIN dept_emp d ON e.emp_no = d.emp_no", + ); + mysql().verified_stmt("SELECT DISTINCT STRAIGHT_JOIN emp_no FROM employees"); + + let select = mysql().verified_only_select("SELECT * FROM employees"); + assert!(select.select_modifiers.is_none()); +} + +#[test] +fn parse_select_modifiers() { + let select = mysql().verified_only_select("SELECT HIGH_PRIORITY * FROM employees"); + assert!(select.select_modifiers.as_ref().unwrap().high_priority); + assert!(!select.select_modifiers.unwrap().straight_join); + + let select = mysql().verified_only_select("SELECT SQL_SMALL_RESULT * FROM employees"); + assert!(select.select_modifiers.unwrap().sql_small_result); + + let select = mysql().verified_only_select("SELECT SQL_BIG_RESULT * FROM employees"); + assert!(select.select_modifiers.unwrap().sql_big_result); + + let select = mysql().verified_only_select("SELECT SQL_BUFFER_RESULT * FROM employees"); + assert!(select.select_modifiers.unwrap().sql_buffer_result); + + let select = mysql().verified_only_select("SELECT SQL_NO_CACHE * FROM employees"); + assert!(select.select_modifiers.unwrap().sql_no_cache); + + let select = mysql().verified_only_select("SELECT SQL_CALC_FOUND_ROWS * FROM employees"); + assert!(select.select_modifiers.unwrap().sql_calc_found_rows); + + let select = mysql().verified_only_select( + "SELECT HIGH_PRIORITY STRAIGHT_JOIN SQL_SMALL_RESULT SQL_BIG_RESULT SQL_BUFFER_RESULT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM employees", + ); + assert!(select.select_modifiers.as_ref().unwrap().high_priority); + assert!(select.select_modifiers.as_ref().unwrap().straight_join); + assert!(select.select_modifiers.as_ref().unwrap().sql_small_result); + assert!(select.select_modifiers.as_ref().unwrap().sql_big_result); + assert!(select.select_modifiers.as_ref().unwrap().sql_buffer_result); + assert!(select.select_modifiers.as_ref().unwrap().sql_no_cache); + assert!(select.select_modifiers.unwrap().sql_calc_found_rows); + + mysql().verified_stmt("SELECT DISTINCT HIGH_PRIORITY emp_no FROM employees"); + mysql().verified_stmt("SELECT DISTINCT SQL_CALC_FOUND_ROWS emp_no FROM employees"); + mysql().verified_stmt("SELECT HIGH_PRIORITY STRAIGHT_JOIN e.emp_no, d.dept_no FROM employees e JOIN dept_emp d ON e.emp_no = d.emp_no"); +} + +#[test] +fn parse_select_modifiers_any_order() { + mysql().one_statement_parses_to( + "SELECT HIGH_PRIORITY DISTINCT * FROM employees", + "SELECT DISTINCT HIGH_PRIORITY * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT SQL_CALC_FOUND_ROWS DISTINCT HIGH_PRIORITY * FROM employees", + "SELECT DISTINCT HIGH_PRIORITY SQL_CALC_FOUND_ROWS * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT HIGH_PRIORITY DISTINCT SQL_SMALL_RESULT * FROM employees", + "SELECT DISTINCT HIGH_PRIORITY SQL_SMALL_RESULT * FROM employees", + ); + + mysql().one_statement_parses_to( + "SELECT HIGH_PRIORITY DISTINCTROW * FROM employees", + "SELECT DISTINCT HIGH_PRIORITY * FROM employees", + ); + + mysql().verified_stmt("SELECT ALL * FROM employees"); + mysql().verified_stmt("SELECT ALL HIGH_PRIORITY * FROM employees"); + mysql().one_statement_parses_to( + "SELECT HIGH_PRIORITY ALL * FROM employees", + "SELECT ALL HIGH_PRIORITY * FROM employees", + ); + + let select = mysql().verified_only_select_with_canonical( + "SELECT HIGH_PRIORITY DISTINCT * FROM employees", + "SELECT DISTINCT HIGH_PRIORITY * FROM employees", + ); + assert!(select.select_modifiers.unwrap().high_priority); + assert!(matches!(select.distinct, Some(Distinct::Distinct))); + + let select = mysql().verified_only_select_with_canonical( + "SELECT SQL_CALC_FOUND_ROWS ALL HIGH_PRIORITY * FROM employees", + "SELECT ALL HIGH_PRIORITY SQL_CALC_FOUND_ROWS * FROM employees", + ); + assert!(select.select_modifiers.as_ref().unwrap().high_priority); + assert!(select.select_modifiers.unwrap().sql_calc_found_rows); + assert_eq!(select.distinct, Some(Distinct::All)) +} + +#[test] +fn parse_select_modifiers_can_be_repeated() { + mysql().one_statement_parses_to( + "SELECT HIGH_PRIORITY HIGH_PRIORITY * FROM employees", + "SELECT HIGH_PRIORITY * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT SQL_CALC_FOUND_ROWS SQL_CALC_FOUND_ROWS * FROM employees", + "SELECT SQL_CALC_FOUND_ROWS * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT STRAIGHT_JOIN STRAIGHT_JOIN * FROM employees", + "SELECT STRAIGHT_JOIN * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT SQL_NO_CACHE SQL_NO_CACHE * FROM employees", + "SELECT SQL_NO_CACHE * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT HIGH_PRIORITY DISTINCT HIGH_PRIORITY * FROM employees", + "SELECT DISTINCT HIGH_PRIORITY * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT SQL_CALC_FOUND_ROWS DISTINCT SQL_CALC_FOUND_ROWS * FROM employees", + "SELECT DISTINCT SQL_CALC_FOUND_ROWS * FROM employees", + ); +} + +#[test] +fn parse_select_modifiers_canonical_ordering() { + mysql().one_statement_parses_to( + "SELECT SQL_CALC_FOUND_ROWS SQL_NO_CACHE SQL_BUFFER_RESULT SQL_BIG_RESULT SQL_SMALL_RESULT STRAIGHT_JOIN HIGH_PRIORITY * FROM employees", + "SELECT HIGH_PRIORITY STRAIGHT_JOIN SQL_SMALL_RESULT SQL_BIG_RESULT SQL_BUFFER_RESULT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT SQL_NO_CACHE DISTINCT SQL_CALC_FOUND_ROWS * FROM employees", + "SELECT DISTINCT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT HIGH_PRIORITY STRAIGHT_JOIN DISTINCT SQL_SMALL_RESULT * FROM employees", + "SELECT DISTINCT HIGH_PRIORITY STRAIGHT_JOIN SQL_SMALL_RESULT * FROM employees", + ); + mysql().one_statement_parses_to( + "SELECT HIGH_PRIORITY ALL STRAIGHT_JOIN * FROM employees", + "SELECT ALL HIGH_PRIORITY STRAIGHT_JOIN * FROM employees", + ); +} + +#[test] +fn parse_select_modifiers_errors() { + assert!(mysql() + .parse_sql_statements("SELECT DISTINCT DISTINCT * FROM t") + .is_err()); + assert!(mysql() + .parse_sql_statements("SELECT DISTINCTROW DISTINCTROW * FROM t") + .is_err()); + assert!(mysql() + .parse_sql_statements("SELECT DISTINCT DISTINCTROW * FROM t") + .is_err()); + assert!(mysql() + .parse_sql_statements("SELECT ALL DISTINCT * FROM t") + .is_err()); + assert!(mysql() + .parse_sql_statements("SELECT DISTINCT ALL * FROM t") + .is_err()); + assert!(mysql() + .parse_sql_statements("SELECT ALL DISTINCTROW * FROM t") + .is_err()); + assert!(mysql() + .parse_sql_statements("SELECT ALL ALL * FROM t") + .is_err()); +} + #[test] fn mysql_foreign_key_with_index_name() { mysql().verified_stmt( diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs index 1f91bffd..4ce8ed8b 100644 --- a/tests/sqlparser_postgres.rs +++ b/tests/sqlparser_postgres.rs @@ -1293,6 +1293,7 @@ fn parse_copy_to() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![ @@ -3074,6 +3075,7 @@ fn parse_array_subquery_expr() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![SelectItem::UnnamedExpr(Expr::Value( @@ -3101,6 +3103,7 @@ fn parse_array_subquery_expr() { select_token: AttachedToken::empty(), optimizer_hint: None, distinct: None, + select_modifiers: None, top: None, top_before_distinct: false, projection: vec![SelectItem::UnnamedExpr(Expr::Value( --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
