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 f7b0812b Add support for mysql table hints (#1675)
f7b0812b is described below
commit f7b0812b01111c678c595f6f79b8d2f5cf5cb305
Author: AvivDavid-Satori <[email protected]>
AuthorDate: Tue Jan 28 10:41:03 2025 +0200
Add support for mysql table hints (#1675)
---
src/ast/mod.rs | 10 +++---
src/ast/query.rs | 82 +++++++++++++++++++++++++++++++++++++++++++++
src/ast/spans.rs | 1 +
src/dialect/mod.rs | 4 +++
src/dialect/mysql.rs | 14 ++++++++
src/parser/mod.rs | 67 ++++++++++++++++++++++++++++++++++++
src/test_utils.rs | 3 ++
tests/sqlparser_bigquery.rs | 3 ++
tests/sqlparser_common.rs | 78 ++++++++++++++++++++++++++++++++++++++++++
tests/sqlparser_hive.rs | 1 +
tests/sqlparser_mssql.rs | 6 ++++
tests/sqlparser_mysql.rs | 2 ++
12 files changed, 266 insertions(+), 5 deletions(-)
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index e64b7d3d..6917b7c9 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -69,11 +69,11 @@ pub use self::query::{
OrderBy, OrderByExpr, PivotValueSource, ProjectionSelect, Query,
RenameSelectItem,
RepetitionQuantifier, ReplaceSelectElement, ReplaceSelectItem,
RowsPerMatch, Select,
SelectInto, SelectItem, SetExpr, SetOperator, SetQuantifier, Setting,
SymbolDefinition, Table,
- TableAlias, TableAliasColumnDef, TableFactor, TableFunctionArgs,
TableSample,
- TableSampleBucket, TableSampleKind, TableSampleMethod, TableSampleModifier,
- TableSampleQuantity, TableSampleSeed, TableSampleSeedModifier,
TableSampleUnit, TableVersion,
- TableWithJoins, Top, TopQuantity, UpdateTableFromKind, ValueTableMode,
Values,
- WildcardAdditionalOptions, With, WithFill,
+ 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 e982c7f0..09058f76 100644
--- a/src/ast/query.rs
+++ b/src/ast/query.rs
@@ -975,6 +975,81 @@ pub struct TableFunctionArgs {
pub settings: Option<Vec<Setting>>,
}
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum TableIndexHintType {
+ Use,
+ Ignore,
+ Force,
+}
+
+impl fmt::Display for TableIndexHintType {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ f.write_str(match self {
+ TableIndexHintType::Use => "USE",
+ TableIndexHintType::Ignore => "IGNORE",
+ TableIndexHintType::Force => "FORCE",
+ })
+ }
+}
+
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum TableIndexType {
+ Index,
+ Key,
+}
+
+impl fmt::Display for TableIndexType {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ f.write_str(match self {
+ TableIndexType::Index => "INDEX",
+ TableIndexType::Key => "KEY",
+ })
+ }
+}
+
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum TableIndexHintForClause {
+ Join,
+ OrderBy,
+ GroupBy,
+}
+
+impl fmt::Display for TableIndexHintForClause {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ f.write_str(match self {
+ TableIndexHintForClause::Join => "JOIN",
+ TableIndexHintForClause::OrderBy => "ORDER BY",
+ TableIndexHintForClause::GroupBy => "GROUP BY",
+ })
+ }
+}
+
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub struct TableIndexHints {
+ pub hint_type: TableIndexHintType,
+ pub index_type: TableIndexType,
+ pub for_clause: Option<TableIndexHintForClause>,
+ pub index_names: Vec<Ident>,
+}
+
+impl fmt::Display for TableIndexHints {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ write!(f, "{} {} ", self.hint_type, self.index_type)?;
+ if let Some(for_clause) = &self.for_clause {
+ write!(f, "FOR {} ", for_clause)?;
+ }
+ write!(f, "({})", display_comma_separated(&self.index_names))
+ }
+}
+
/// A table name or a parenthesized subquery with an optional alias
#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
@@ -1009,6 +1084,9 @@ pub enum TableFactor {
/// Optional table sample modifier
/// See:
<https://jakewheat.github.io/sql-overview/sql-2016-foundation-grammar.html#sample-clause>
sample: Option<TableSampleKind>,
+ /// Optional index hints(mysql)
+ /// See: <https://dev.mysql.com/doc/refman/8.4/en/index-hints.html>
+ index_hints: Vec<TableIndexHints>,
},
Derived {
lateral: bool,
@@ -1590,6 +1668,7 @@ impl fmt::Display for TableFactor {
with_ordinality,
json_path,
sample,
+ index_hints,
} => {
write!(f, "{name}")?;
if let Some(json_path) = json_path {
@@ -1618,6 +1697,9 @@ impl fmt::Display for TableFactor {
if let Some(alias) = alias {
write!(f, " AS {alias}")?;
}
+ if !index_hints.is_empty() {
+ write!(f, " {}", display_separated(index_hints, " "))?;
+ }
if !with_hints.is_empty() {
write!(f, " WITH ({})",
display_comma_separated(with_hints))?;
}
diff --git a/src/ast/spans.rs b/src/ast/spans.rs
index aed1c6c2..8f72c26f 100644
--- a/src/ast/spans.rs
+++ b/src/ast/spans.rs
@@ -1739,6 +1739,7 @@ impl Spanned for TableFactor {
partitions: _,
json_path: _,
sample: _,
+ index_hints: _,
} => union_spans(
name.0
.iter()
diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs
index 9fc16cd5..6329c5cf 100644
--- a/src/dialect/mod.rs
+++ b/src/dialect/mod.rs
@@ -854,6 +854,10 @@ pub trait Dialect: Debug + Any {
fn supports_string_escape_constant(&self) -> bool {
false
}
+ /// Returns true if the dialect supports the table hints in the `FROM`
clause.
+ fn supports_table_hints(&self) -> bool {
+ false
+ }
}
/// This represents the operators for which precedence must be defined
diff --git a/src/dialect/mysql.rs b/src/dialect/mysql.rs
index 535b4298..a67fe67b 100644
--- a/src/dialect/mysql.rs
+++ b/src/dialect/mysql.rs
@@ -25,6 +25,10 @@ use crate::{
parser::{Parser, ParserError},
};
+use super::keywords;
+
+const RESERVED_FOR_TABLE_ALIAS_MYSQL: &[Keyword] = &[Keyword::USE,
Keyword::IGNORE, Keyword::FORCE];
+
/// A [`Dialect`] for [MySQL](https://www.mysql.com/)
#[derive(Debug)]
pub struct MySqlDialect {}
@@ -111,6 +115,16 @@ impl Dialect for MySqlDialect {
fn supports_user_host_grantee(&self) -> bool {
true
}
+
+ fn is_table_factor_alias(&self, explicit: bool, kw: &Keyword, _parser:
&mut Parser) -> bool {
+ explicit
+ || (!keywords::RESERVED_FOR_TABLE_ALIAS.contains(kw)
+ && !RESERVED_FOR_TABLE_ALIAS_MYSQL.contains(kw))
+ }
+
+ fn supports_table_hints(&self) -> bool {
+ true
+ }
}
/// `LOCK TABLES`
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index c6e1eb19..c8ff01f7 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -8910,6 +8910,64 @@ impl<'a> Parser<'a> {
}
}
+ fn parse_table_index_hints(&mut self) -> Result<Vec<TableIndexHints>,
ParserError> {
+ let mut hints = vec![];
+ while let Some(hint_type) =
+ self.parse_one_of_keywords(&[Keyword::USE, Keyword::IGNORE,
Keyword::FORCE])
+ {
+ let hint_type = match hint_type {
+ Keyword::USE => TableIndexHintType::Use,
+ Keyword::IGNORE => TableIndexHintType::Ignore,
+ Keyword::FORCE => TableIndexHintType::Force,
+ _ => {
+ return self.expected(
+ "expected to match USE/IGNORE/FORCE keyword",
+ self.peek_token(),
+ )
+ }
+ };
+ let index_type = match
self.parse_one_of_keywords(&[Keyword::INDEX, Keyword::KEY]) {
+ Some(Keyword::INDEX) => TableIndexType::Index,
+ Some(Keyword::KEY) => TableIndexType::Key,
+ _ => {
+ return self.expected("expected to match INDEX/KEY
keyword", self.peek_token())
+ }
+ };
+ let for_clause = if self.parse_keyword(Keyword::FOR) {
+ let clause = if self.parse_keyword(Keyword::JOIN) {
+ TableIndexHintForClause::Join
+ } else if self.parse_keywords(&[Keyword::ORDER, Keyword::BY]) {
+ TableIndexHintForClause::OrderBy
+ } else if self.parse_keywords(&[Keyword::GROUP, Keyword::BY]) {
+ TableIndexHintForClause::GroupBy
+ } else {
+ return self.expected(
+ "expected to match FOR/ORDER BY/GROUP BY table hint in
for clause",
+ self.peek_token(),
+ );
+ };
+ Some(clause)
+ } else {
+ None
+ };
+
+ self.expect_token(&Token::LParen)?;
+ let index_names = if self.peek_token().token != Token::RParen {
+ self.parse_comma_separated(Parser::parse_identifier)?
+ } else {
+ vec![]
+ };
+ self.expect_token(&Token::RParen)?;
+ hints.push(TableIndexHints {
+ hint_type,
+ index_type,
+ for_clause,
+ index_names,
+ });
+ }
+ Ok(hints)
+ }
+
/// Wrapper for parse_optional_alias_inner, left for
backwards-compatibility
/// but new flows should use the context-specific methods such as
`maybe_parse_select_item_alias`
/// and `maybe_parse_table_alias`.
@@ -11257,6 +11315,14 @@ impl<'a> Parser<'a> {
let alias = self.maybe_parse_table_alias()?;
+ // MYSQL-specific table hints:
+ let index_hints = if self.dialect.supports_table_hints() {
+ self.maybe_parse(|p| p.parse_table_index_hints())?
+ .unwrap_or(vec![])
+ } else {
+ vec![]
+ };
+
// MSSQL-specific table hints:
let mut with_hints = vec![];
if self.parse_keyword(Keyword::WITH) {
@@ -11285,6 +11351,7 @@ impl<'a> Parser<'a> {
with_ordinality,
json_path,
sample,
+ index_hints,
};
while let Some(kw) = self.parse_one_of_keywords(&[Keyword::PIVOT,
Keyword::UNPIVOT]) {
diff --git a/src/test_utils.rs b/src/test_utils.rs
index f2e3adf0..20898422 100644
--- a/src/test_utils.rs
+++ b/src/test_utils.rs
@@ -362,6 +362,7 @@ pub fn table(name: impl Into<String>) -> TableFactor {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
}
}
@@ -376,6 +377,7 @@ pub fn table_from_name(name: ObjectName) -> TableFactor {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
}
}
@@ -393,6 +395,7 @@ pub fn table_with_alias(name: impl Into<String>, alias:
impl Into<String>) -> Ta
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
}
}
diff --git a/tests/sqlparser_bigquery.rs b/tests/sqlparser_bigquery.rs
index cbb96376..45d87a8b 100644
--- a/tests/sqlparser_bigquery.rs
+++ b/tests/sqlparser_bigquery.rs
@@ -1565,6 +1565,7 @@ fn parse_table_time_travel() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
joins: vec![]
},]
@@ -1665,6 +1666,7 @@ fn parse_merge() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
table
);
@@ -1682,6 +1684,7 @@ fn parse_merge() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
source
);
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index 5c11b290..2489ce2d 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -503,6 +503,7 @@ fn parse_update_with_table_alias() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
joins: vec![],
},
@@ -596,6 +597,7 @@ fn parse_select_with_table_alias() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
joins: vec![],
}]
@@ -792,6 +794,7 @@ fn parse_where_delete_with_alias_statement() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
from[0].relation,
);
@@ -810,6 +813,7 @@ fn parse_where_delete_with_alias_statement() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
joins: vec![],
}]),
@@ -6416,6 +6420,7 @@ fn parse_joins_on() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
global,
join_operator: f(JoinConstraint::On(Expr::BinaryOp {
@@ -6545,6 +6550,7 @@ fn parse_joins_using() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
global: false,
join_operator: f(JoinConstraint::Using(vec![ObjectName::from(vec![
@@ -6623,6 +6629,7 @@ fn parse_natural_join() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
global: false,
join_operator: f(JoinConstraint::Natural),
@@ -8718,6 +8725,7 @@ fn parse_merge() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
}
);
assert_eq!(table, table_no_into);
@@ -9901,6 +9909,7 @@ fn parse_pivot_table() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
}),
aggregate_functions: vec![
expected_function("a", None),
@@ -9977,6 +9986,7 @@ fn parse_unpivot_table() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
}),
value: Ident {
value: "quantity".to_string(),
@@ -10023,6 +10033,73 @@ fn parse_unpivot_table() {
);
}
+#[test]
+fn parse_select_table_with_index_hints() {
+ let supported_dialects = all_dialects_where(|d| d.supports_table_hints());
+ let s = supported_dialects.verified_only_select(
+ "SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER
BY a",
+ );
+ if let TableFactor::Table { index_hints, .. } = &s.from[0].relation {
+ assert_eq!(
+ vec![
+ TableIndexHints {
+ hint_type: TableIndexHintType::Use,
+ index_names: vec!["i1".into()],
+ index_type: TableIndexType::Index,
+ for_clause: None,
+ },
+ TableIndexHints {
+ hint_type: TableIndexHintType::Ignore,
+ index_names: vec!["i2".into()],
+ index_type: TableIndexType::Index,
+ for_clause: Some(TableIndexHintForClause::OrderBy),
+ },
+ ],
+ *index_hints
+ );
+ } else {
+ panic!("Expected TableFactor::Table");
+ }
+ supported_dialects.verified_stmt("SELECT * FROM t1 USE INDEX (i1) USE
INDEX (i1, i1)");
+ supported_dialects.verified_stmt(
+ "SELECT * FROM t1 USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE
INDEX (i2)",
+ );
+ supported_dialects.verified_stmt("SELECT * FROM t1 FORCE INDEX FOR JOIN
(i2)");
+ supported_dialects.verified_stmt("SELECT * FROM t1 IGNORE INDEX FOR JOIN
(i2)");
+ supported_dialects.verified_stmt(
+ "SELECT * FROM t USE INDEX (index1) IGNORE INDEX FOR ORDER BY (index1)
IGNORE INDEX FOR GROUP BY (index1) WHERE A = B",
+ );
+
+ // Test that dialects that don't support table hints will keep parsing the
USE as table alias
+ let sql = "SELECT * FROM T USE LIMIT 1";
+ let unsupported_dialects = all_dialects_where(|d|
!d.supports_table_hints());
+ let select = unsupported_dialects
+ .verified_only_select_with_canonical(sql, "SELECT * FROM T AS USE
LIMIT 1");
+ assert_eq!(
+ select.from,
+ vec![TableWithJoins {
+ relation: TableFactor::Table {
+ name:
ObjectName(vec![sqlparser::ast::ObjectNamePart::Identifier(
+ Ident::new("T")
+ )]),
+ alias: Some(TableAlias {
+ name: Ident::new("USE"),
+ columns: vec![],
+ }),
+ args: None,
+ with_hints: vec![],
+ version: None,
+ partitions: vec![],
+ with_ordinality: false,
+ json_path: None,
+ sample: None,
+ index_hints: vec![],
+ },
+ joins: vec![],
+ }]
+ );
+}
+
#[test]
fn parse_pivot_unpivot_table() {
let sql = concat!(
@@ -10048,6 +10125,7 @@ fn parse_pivot_unpivot_table() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
}),
value: Ident {
value: "population".to_string(),
diff --git a/tests/sqlparser_hive.rs b/tests/sqlparser_hive.rs
index 9c4e8f07..5d710b17 100644
--- a/tests/sqlparser_hive.rs
+++ b/tests/sqlparser_hive.rs
@@ -460,6 +460,7 @@ fn parse_delimited_identifiers() {
partitions: _,
json_path: _,
sample: _,
+ index_hints: _,
} => {
assert_eq!(
ObjectName::from(vec![Ident::with_quote('"', "a table")]),
diff --git a/tests/sqlparser_mssql.rs b/tests/sqlparser_mssql.rs
index 3c401759..9046e9e7 100644
--- a/tests/sqlparser_mssql.rs
+++ b/tests/sqlparser_mssql.rs
@@ -74,6 +74,7 @@ fn parse_table_time_travel() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![]
},
joins: vec![]
},]
@@ -223,6 +224,7 @@ fn parse_mssql_openjson() {
partitions: vec![],
json_path: None,
sample: None,
+ index_hints: vec![]
},
joins: vec![Join {
relation: TableFactor::OpenJsonTable {
@@ -282,6 +284,7 @@ fn parse_mssql_openjson() {
partitions: vec![],
json_path: None,
sample: None,
+ index_hints: vec![]
},
joins: vec![Join {
relation: TableFactor::OpenJsonTable {
@@ -341,6 +344,7 @@ fn parse_mssql_openjson() {
partitions: vec![],
json_path: None,
sample: None,
+ index_hints: vec![]
},
joins: vec![Join {
relation: TableFactor::OpenJsonTable {
@@ -400,6 +404,7 @@ fn parse_mssql_openjson() {
partitions: vec![],
json_path: None,
sample: None,
+ index_hints: vec![],
},
joins: vec![Join {
relation: TableFactor::OpenJsonTable {
@@ -439,6 +444,7 @@ fn parse_mssql_openjson() {
partitions: vec![],
json_path: None,
sample: None,
+ index_hints: vec![],
},
joins: vec![Join {
relation: TableFactor::OpenJsonTable {
diff --git a/tests/sqlparser_mysql.rs b/tests/sqlparser_mysql.rs
index fb72436e..501dce3e 100644
--- a/tests/sqlparser_mysql.rs
+++ b/tests/sqlparser_mysql.rs
@@ -2036,6 +2036,7 @@ fn parse_update_with_joins() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
joins: vec![Join {
relation: TableFactor::Table {
@@ -2051,6 +2052,7 @@ fn parse_update_with_joins() {
with_ordinality: false,
json_path: None,
sample: None,
+ index_hints: vec![],
},
global: false,
join_operator:
JoinOperator::Inner(JoinConstraint::On(Expr::BinaryOp {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
