You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "mvzink (via GitHub)" <gi...@apache.org> on 2024/11/20 16:54:40 UTC
[PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
mvzink opened a new pull request, #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538
- Support MySQL style `'user'@'host'` syntax.
- Support wildcards on grant objects: `*.*`.
- Make `CASCADE`/`RESTRICT` in revoke statements optional since MySQL doesn't accept it.
- Support optional MySQL parameters for create view: algorithm, definer (requires `user@host` syntax), and security context.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1907967357
##########
src/parser/mod.rs:
##########
@@ -11808,23 +11899,32 @@ impl<'a> Parser<'a> {
}
}
+ pub fn parse_grantee_name(&mut self) -> Result<GranteeName, ParserError> {
+ let name = self.parse_object_name(false)?;
+ if self.dialect.supports_user_host_grantee()
+ && name.0.len() == 1
+ && self.consume_token(&Token::AtSign)
+ {
+ let user = name.0[0].clone();
Review Comment:
@yoavcloud I believe in #1640 you changed grantee parsing from `parse_identifier` to `parse_object_name`; is that actually used by Snowflake? Would like to find a way to avoid this clone to get the identifier out of `name`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio merged PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1862426564
##########
src/ast/mod.rs:
##########
@@ -7377,15 +7420,84 @@ pub enum MySQLColumnPosition {
impl Display for MySQLColumnPosition {
fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
match self {
- MySQLColumnPosition::First => Ok(write!(f, "FIRST")?),
+ MySQLColumnPosition::First => write!(f, "FIRST"),
MySQLColumnPosition::After(ident) => {
let column_name = &ident.value;
- Ok(write!(f, "AFTER {column_name}")?)
+ write!(f, "AFTER {column_name}")
}
}
}
}
+/// MySQL `CREATE VIEW` algorithm parameter: [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum CreateViewAlgorithm {
+ Undefined,
+ Merge,
+ TempTable,
+}
+
+impl Display for CreateViewAlgorithm {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ CreateViewAlgorithm::Undefined => write!(f, "UNDEFINED"),
+ CreateViewAlgorithm::Merge => write!(f, "MERGE"),
+ CreateViewAlgorithm::TempTable => write!(f, "TEMPTABLE"),
+ }
+ }
+}
+/// MySQL `CREATE VIEW` security parameter: [SQL SECURITY { DEFINER | INVOKER }]
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum CreateViewSecurity {
+ Definer,
+ Invoker,
+}
+
+impl Display for CreateViewSecurity {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ CreateViewSecurity::Definer => write!(f, "DEFINER"),
+ CreateViewSecurity::Invoker => write!(f, "INVOKER"),
+ }
+ }
+}
+
+/// [MySQL] `CREATE VIEW` additional parameters
+///
+/// [MySQL]: https://dev.mysql.com/doc/refman/9.1/en/create-view.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 CreateViewParams {
+ pub algorithm: Option<CreateViewAlgorithm>,
+ pub definer: Option<Grantee>,
+ pub security: Option<CreateViewSecurity>,
+}
+
+impl Display for CreateViewParams {
+ fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
+ let parts = [
+ self.algorithm
+ .as_ref()
+ .map(|algorithm| format!("ALGORITHM = {algorithm}")),
+ self.definer
+ .as_ref()
+ .map(|definer| format!("DEFINER = {definer}")),
+ self.security
+ .as_ref()
+ .map(|security| format!("SQL SECURITY {security}")),
+ ]
+ .into_iter()
+ .flatten()
+ .collect::<Vec<_>>();
+ display_separated(&parts, " ").fmt(f)
Review Comment:
Can we write these explicitly as e.g?
```rust
if let Some(algorithm) = self.algorithm { write!() }
```
I think that potentially makes it easier to spot the final output and extend in the future.
Also ideally we can use an exhaustive check with
```rust
let CreateviewParams { algorithm, definer, security } = self
```
so that the user is automatically guided to this function whenever the representation changes
##########
src/tokenizer.rs:
##########
@@ -1202,6 +1202,9 @@ impl<'a> Tokenizer<'a> {
}
}
Some(' ') => Ok(Some(Token::AtSign)),
+ Some('\'') => Ok(Some(Token::AtSign)),
+ Some('\"') => Ok(Some(Token::AtSign)),
+ Some('`') => Ok(Some(Token::AtSign)),
Review Comment:
Ah I see that makes sense to me and the current approach sounds reasonable! Can we add a comment around mentioning the example use case? I think that would be useful since the logic/tradeoff wouldnt be obvious otherwise, Also can we extend the tokenizer test case to cover the other two tokens supported by this path `"` and '`'?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1861364050
##########
src/parser/mod.rs:
##########
@@ -8353,20 +8409,40 @@ impl<'a> Parser<'a> {
}
}
+ /// Parse a possibly qualified, possibly quoted identifier, optionally allowing for wildcards,
+ /// e.g. *, `foo`.*, or "foo"."bar"
+ pub fn parse_object_name_with_wildcards(
Review Comment:
Still having a think about the best way to do this; posting the other updates for further feedback.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1895651779
##########
src/parser/mod.rs:
##########
@@ -3869,11 +3873,16 @@ impl<'a> Parser<'a> {
.is_some();
let persistent = dialect_of!(self is DuckDbDialect)
&& self.parse_one_of_keywords(&[Keyword::PERSISTENT]).is_some();
+ let create_view_params = if dialect_of!(self is MySqlDialect | GenericDialect) {
+ self.parse_create_view_params()?
+ } else {
+ None
+ };
Review Comment:
maybe this could be accepted without the dialect check if that works just as well `let create_view_paras = self.parse_create_view_params()`? thinking since the view params are optional
##########
src/parser/mod.rs:
##########
@@ -11375,7 +11466,11 @@ impl<'a> Parser<'a> {
} else {
let object_type =
self.parse_one_of_keywords(&[Keyword::SEQUENCE, Keyword::SCHEMA, Keyword::TABLE]);
- let objects = self.parse_comma_separated(|p| p.parse_object_name(false));
+ let objects = if dialect_of!(self is MySqlDialect | GenericDialect) {
+ self.parse_comma_separated(|p| p.parse_object_name_with_wildcards(false, true))
+ } else {
+ self.parse_comma_separated(|p| p.parse_object_name(false))
+ };
Review Comment:
Thinking similarly here, if there aren't any conflicting behavior we could skip the dialect check and always parse the object name with wildcard?
##########
src/parser/mod.rs:
##########
@@ -8353,20 +8409,40 @@ impl<'a> Parser<'a> {
}
}
+ /// Parse a possibly qualified, possibly quoted identifier, optionally allowing for wildcards,
+ /// e.g. *, `foo`.*, or "foo"."bar"
+ pub fn parse_object_name_with_wildcards(
Review Comment:
Ah not a problem I think this sounds reasonable! Could we maybe make the function non-public in case it needs to change later on so that we don't consider downstream impact?
##########
src/parser/mod.rs:
##########
@@ -4842,9 +4852,68 @@ impl<'a> Parser<'a> {
if_not_exists,
temporary,
to,
+ params: create_view_params,
})
}
+ /// Parse optional algorithm, definer, and security context parameters for [MySQL]
Review Comment:
```suggestion
/// Parse optional parameters for the `CREATE VIEW` statement. [MySQL]
```
or something similar, thinking so since this can be extended later beyond mysql today
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1861074669
##########
src/parser/mod.rs:
##########
@@ -4693,9 +4699,59 @@ impl<'a> Parser<'a> {
if_not_exists,
temporary,
to,
+ params: mysql_create_view_params,
})
}
+ /// Parse optional algorithm, definer, and security context parameters for [MySQL]
+ ///
+ /// [MySQL]: https://dev.mysql.com/doc/refman/9.1/en/create-view.html
+ fn parse_mysql_create_view_params(&mut self) -> Result<Option<MySQLViewParams>, ParserError> {
+ let algorithm = if self.parse_keyword(Keyword::ALGORITHM) {
+ self.expect_token(&Token::Eq)?;
+ Some(
+ match self.expect_one_of_keywords(&[
+ Keyword::UNDEFINED,
+ Keyword::MERGE,
+ Keyword::TEMPTABLE,
+ ])? {
+ Keyword::UNDEFINED => MySQLViewAlgorithm::Undefined,
+ Keyword::MERGE => MySQLViewAlgorithm::Merge,
+ Keyword::TEMPTABLE => MySQLViewAlgorithm::TempTable,
+ _ => unreachable!(),
Review Comment:
Did so be rewinding the token to then call `self.expected` (otherwise I don't see how to get the location that it wants). Seems a bit clunky when this is exactly the error that `self.expect_one_of_keywords` should have already returned. The only way we would hit this branch is if someone added a keyword to the `self.expect_one_of_keywords` call but forgot to add a corresponding `match` arm. One overengineered solution which could be used here and elsewhere would be to wrap it in a macro that looks like a `match` but collects the keywords from the match arms into the `self.expect_one_of_keywords` for you.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1896016157
##########
src/parser/mod.rs:
##########
@@ -11375,7 +11466,11 @@ impl<'a> Parser<'a> {
} else {
let object_type =
self.parse_one_of_keywords(&[Keyword::SEQUENCE, Keyword::SCHEMA, Keyword::TABLE]);
- let objects = self.parse_comma_separated(|p| p.parse_object_name(false));
+ let objects = if dialect_of!(self is MySqlDialect | GenericDialect) {
+ self.parse_comma_separated(|p| p.parse_object_name_with_wildcards(false, true))
+ } else {
+ self.parse_comma_separated(|p| p.parse_object_name(false))
+ };
Review Comment:
There's no "conflicting behavior" per se, so the tests pass without these 2 checks, and I definitely see the value of reducing switches on dialect (especially using `dialect_of!` rather than a method on the dialect). But to be clear, this will mean that using `PostgreSqlDialect` for example will accept syntax that Postgres itself doesn't accept. If that's worth the tradeoff to reduce switching in here, I'm okay with that but just want to be clear that's okay.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1896650574
##########
src/parser/mod.rs:
##########
@@ -11375,7 +11466,11 @@ impl<'a> Parser<'a> {
} else {
let object_type =
self.parse_one_of_keywords(&[Keyword::SEQUENCE, Keyword::SCHEMA, Keyword::TABLE]);
- let objects = self.parse_comma_separated(|p| p.parse_object_name(false));
+ let objects = if dialect_of!(self is MySqlDialect | GenericDialect) {
+ self.parse_comma_separated(|p| p.parse_object_name_with_wildcards(false, true))
+ } else {
+ self.parse_comma_separated(|p| p.parse_object_name(false))
+ };
Review Comment:
Ah yeah that would be fine, the dialect impls can accept a superset of the actual sql spec
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1855409232
##########
src/parser/mod.rs:
##########
@@ -4693,9 +4699,59 @@ impl<'a> Parser<'a> {
if_not_exists,
temporary,
to,
+ params: mysql_create_view_params,
})
}
+ /// Parse optional algorithm, definer, and security context parameters for [MySQL]
+ ///
+ /// [MySQL]: https://dev.mysql.com/doc/refman/9.1/en/create-view.html
+ fn parse_mysql_create_view_params(&mut self) -> Result<Option<MySQLViewParams>, ParserError> {
+ let algorithm = if self.parse_keyword(Keyword::ALGORITHM) {
+ self.expect_token(&Token::Eq)?;
+ Some(
+ match self.expect_one_of_keywords(&[
+ Keyword::UNDEFINED,
+ Keyword::MERGE,
+ Keyword::TEMPTABLE,
+ ])? {
+ Keyword::UNDEFINED => MySQLViewAlgorithm::Undefined,
+ Keyword::MERGE => MySQLViewAlgorithm::Merge,
+ Keyword::TEMPTABLE => MySQLViewAlgorithm::TempTable,
+ _ => unreachable!(),
Review Comment:
For the unreachable branches, we can return an error instead of crashing? That would prevent bugs in the parser from crashing users' systems
##########
src/tokenizer.rs:
##########
@@ -1202,6 +1202,9 @@ impl<'a> Tokenizer<'a> {
}
}
Some(' ') => Ok(Some(Token::AtSign)),
+ Some('\'') => Ok(Some(Token::AtSign)),
+ Some('\"') => Ok(Some(Token::AtSign)),
+ Some('`') => Ok(Some(Token::AtSign)),
Review Comment:
the changes feel unexpected, took a quick look it seems rather a bug that mysql dialect is currently configured to accept `@` as an identifier start? [their doc](https://dev.mysql.com/doc/refman/8.0/en/identifiers.html) seems to suggest otherwise?
##########
src/parser/mod.rs:
##########
@@ -11023,23 +11103,43 @@ impl<'a> Parser<'a> {
}
}
+ pub fn parse_grantee(&mut self) -> Result<Grantee, ParserError> {
+ let user = self.parse_identifier(false)?;
+ if dialect_of!(self is MySqlDialect | GenericDialect) && self.consume_token(&Token::AtSign)
Review Comment:
We can use a method on the dialect trait to gate the new functionalities as its preferred over the [dialect_of](https://github.com/apache/datafusion-sqlparser-rs/blob/69bb0d824b360e60c358aad1367d0d897d0c5fea/src/dialect/mod.rs#L63-L68) macro.
here e.g. a `self.dialect.supports_user_host_grantee()` or similar method?
##########
tests/sqlparser_mysql.rs:
##########
@@ -2971,3 +2971,133 @@ fn parse_bitstring_literal() {
))]
);
}
+
+#[test]
+fn parse_grant() {
+ let sql = "GRANT ALL ON *.* TO 'jeffrey'@'%'";
+ assert_eq!(
+ mysql().verified_stmt(sql),
+ Statement::Grant {
+ privileges: Privileges::All {
+ with_privileges_keyword: false
+ },
+ objects: GrantObjects::Tables(vec![ObjectName(vec!["*".into(), "*".into()])]),
+ grantees: vec![Grantee::UserHost {
+ user: Ident {
+ value: "jeffrey".to_owned(),
+ quote_style: Some('\'')
+ },
+ host: Ident {
+ value: "%".to_owned(),
+ quote_style: Some('\'')
+ }
+ }],
+ with_grant_option: false,
+ granted_by: None
+ }
+ )
+}
+
+#[test]
+fn parse_revoke() {
+ let sql = "REVOKE ALL ON db1.* FROM 'jeffrey'@'%'";
+ assert_eq!(
+ mysql().verified_stmt(sql),
+ Statement::Revoke {
+ privileges: Privileges::All {
+ with_privileges_keyword: false
+ },
+ objects: GrantObjects::Tables(vec![ObjectName(vec!["db1".into(), "*".into()])]),
+ grantees: vec![Grantee::UserHost {
+ user: Ident {
+ value: "jeffrey".to_owned(),
+ quote_style: Some('\'')
+ },
+ host: Ident {
+ value: "%".to_owned(),
+ quote_style: Some('\'')
+ }
+ }],
+ granted_by: None,
+ cascade: None,
+ }
+ )
+}
+
+#[test]
+fn parse_create_view_algorithm_param() {
+ let sql = "CREATE ALGORITHM = MERGE VIEW foo AS SELECT 1";
+ let stmt = mysql().verified_stmt(sql);
+ if let Statement::CreateView {
+ params:
+ Some(MySQLViewParams {
+ algorithm,
+ definer,
+ security,
+ }),
+ ..
+ } = stmt
+ {
+ assert_eq!(algorithm, Some(MySQLViewAlgorithm::Merge));
+ assert!(definer.is_none());
+ assert!(security.is_none());
+ } else {
+ unreachable!()
+ }
+}
+
+#[test]
+fn parse_create_view_definer_param() {
+ let sql = "CREATE DEFINER = 'jeffrey'@'localhost' VIEW foo AS SELECT 1";
+ let stmt = mysql().verified_stmt(sql);
+ if let Statement::CreateView {
+ params:
+ Some(MySQLViewParams {
+ algorithm,
+ definer,
+ security,
+ }),
+ ..
+ } = stmt
+ {
+ assert!(algorithm.is_none());
+ assert_eq!(
+ definer,
+ Some(Grantee::UserHost {
+ user: Ident {
+ value: "jeffrey".to_owned(),
+ quote_style: Some('\'')
+ },
+ host: Ident {
+ value: "localhost".to_owned(),
+ quote_style: Some('\'')
+ },
+ })
+ );
+ assert!(security.is_none());
+ } else {
+ unreachable!()
+ }
+}
+
+#[test]
+fn parse_create_view_security_param() {
+ let sql = "CREATE SQL SECURITY DEFINER VIEW foo AS SELECT 1";
+ let stmt = mysql().verified_stmt(sql);
Review Comment:
similarly, we could add one scenario for invoker? e.g.
```rust
mysql().verified_stmt("CREATE SQL SECURITY INVOKER VIEW foo AS SELECT 1");
```
##########
src/parser/mod.rs:
##########
@@ -11023,23 +11103,43 @@ impl<'a> Parser<'a> {
}
}
+ pub fn parse_grantee(&mut self) -> Result<Grantee, ParserError> {
+ let user = self.parse_identifier(false)?;
+ if dialect_of!(self is MySqlDialect | GenericDialect) && self.consume_token(&Token::AtSign)
+ {
+ let host = self.parse_identifier(false)?;
+ Ok(Grantee::UserHost { user, host })
+ } else {
+ Ok(Grantee::Ident(user))
+ }
+ }
+
/// Parse a REVOKE statement
pub fn parse_revoke(&mut self) -> Result<Statement, ParserError> {
let (privileges, objects) = self.parse_grant_revoke_privileges_objects()?;
self.expect_keyword(Keyword::FROM)?;
- let grantees = self.parse_comma_separated(|p| p.parse_identifier(false))?;
+ let grantees = self.parse_comma_separated(|p| p.parse_grantee())?;
let granted_by = self
.parse_keywords(&[Keyword::GRANTED, Keyword::BY])
.then(|| self.parse_identifier(false).unwrap());
let loc = self.peek_token().location;
- let cascade = self.parse_keyword(Keyword::CASCADE);
- let restrict = self.parse_keyword(Keyword::RESTRICT);
- if cascade && restrict {
- return parser_err!("Cannot specify both CASCADE and RESTRICT in REVOKE", loc);
- }
+ let cascade = if !dialect_of!(self is MySqlDialect) {
Review Comment:
Can we change the representation and parsing of cascade/restrict to hopefully simplify the code and make the intent explicit, thinking instead of using a bool, to represent it in the statement we can use the existing cascade option?
`Statement::Revoke{ cascade: Option<TruncateCascadeOption> }`. That would also let us reuse the current parser code for that enum
##########
src/ast/mod.rs:
##########
@@ -7311,15 +7345,84 @@ pub enum MySQLColumnPosition {
impl Display for MySQLColumnPosition {
fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
match self {
- MySQLColumnPosition::First => Ok(write!(f, "FIRST")?),
+ MySQLColumnPosition::First => write!(f, "FIRST"),
MySQLColumnPosition::After(ident) => {
let column_name = &ident.value;
- Ok(write!(f, "AFTER {column_name}")?)
+ write!(f, "AFTER {column_name}")
}
}
}
}
+/// MySQL `CREATE VIEW` algorithm parameter: [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum MySQLViewAlgorithm {
+ Undefined,
+ Merge,
+ TempTable,
+}
+
+impl Display for MySQLViewAlgorithm {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ MySQLViewAlgorithm::Undefined => write!(f, "UNDEFINED"),
+ MySQLViewAlgorithm::Merge => write!(f, "MERGE"),
+ MySQLViewAlgorithm::TempTable => write!(f, "TEMPTABLE"),
+ }
+ }
+}
+/// MySQL `CREATE VIEW` security parameter: [SQL SECURITY { DEFINER | INVOKER }]
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum MySQLViewSecurity {
+ Definer,
+ Invoker,
+}
+
+impl Display for MySQLViewSecurity {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ MySQLViewSecurity::Definer => write!(f, "DEFINER"),
+ MySQLViewSecurity::Invoker => write!(f, "INVOKER"),
+ }
+ }
+}
+
+/// [MySQL] `CREATE VIEW` additional parameters
+///
+/// [MySQL]: https://dev.mysql.com/doc/refman/9.1/en/create-view.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 MySQLViewParams {
Review Comment:
```suggestion
pub struct CreateViewParams {
```
Re naming of the structs/enums we can probably use the statement type as the prefix instead of the dialect, to make it possible for the struct/enum to be extended to cover other dialects if required in the future. e.g. `CreateViewAlgorithm` etc
##########
src/parser/mod.rs:
##########
@@ -8353,20 +8409,40 @@ impl<'a> Parser<'a> {
}
}
+ /// Parse a possibly qualified, possibly quoted identifier, optionally allowing for wildcards,
+ /// e.g. *, `foo`.*, or "foo"."bar"
+ pub fn parse_object_name_with_wildcards(
Review Comment:
The method sounds like what `parse_wildcard_expr` does, minus the return type. Can we reuse that functionality instead? e.g. if we move that logic out to its own method so that `parse_wildcard_expr` and other functionality like grant etc can call it directly?
##########
tests/sqlparser_mysql.rs:
##########
@@ -2971,3 +2971,133 @@ fn parse_bitstring_literal() {
))]
);
}
+
+#[test]
+fn parse_grant() {
+ let sql = "GRANT ALL ON *.* TO 'jeffrey'@'%'";
+ assert_eq!(
+ mysql().verified_stmt(sql),
+ Statement::Grant {
+ privileges: Privileges::All {
+ with_privileges_keyword: false
+ },
+ objects: GrantObjects::Tables(vec![ObjectName(vec!["*".into(), "*".into()])]),
+ grantees: vec![Grantee::UserHost {
+ user: Ident {
+ value: "jeffrey".to_owned(),
+ quote_style: Some('\'')
+ },
+ host: Ident {
+ value: "%".to_owned(),
+ quote_style: Some('\'')
+ }
+ }],
+ with_grant_option: false,
+ granted_by: None
+ }
+ )
+}
+
+#[test]
+fn parse_revoke() {
+ let sql = "REVOKE ALL ON db1.* FROM 'jeffrey'@'%'";
+ assert_eq!(
+ mysql().verified_stmt(sql),
+ Statement::Revoke {
+ privileges: Privileges::All {
+ with_privileges_keyword: false
+ },
+ objects: GrantObjects::Tables(vec![ObjectName(vec!["db1".into(), "*".into()])]),
+ grantees: vec![Grantee::UserHost {
+ user: Ident {
+ value: "jeffrey".to_owned(),
+ quote_style: Some('\'')
+ },
+ host: Ident {
+ value: "%".to_owned(),
+ quote_style: Some('\'')
+ }
+ }],
+ granted_by: None,
+ cascade: None,
+ }
+ )
+}
+
+#[test]
+fn parse_create_view_algorithm_param() {
Review Comment:
Also for the create view, can we add a test case showing the behavior on multiple params in the same create statement?
##########
tests/sqlparser_mysql.rs:
##########
@@ -2971,3 +2971,133 @@ fn parse_bitstring_literal() {
))]
);
}
+
+#[test]
+fn parse_grant() {
+ let sql = "GRANT ALL ON *.* TO 'jeffrey'@'%'";
+ assert_eq!(
+ mysql().verified_stmt(sql),
+ Statement::Grant {
+ privileges: Privileges::All {
+ with_privileges_keyword: false
+ },
+ objects: GrantObjects::Tables(vec![ObjectName(vec!["*".into(), "*".into()])]),
+ grantees: vec![Grantee::UserHost {
+ user: Ident {
+ value: "jeffrey".to_owned(),
+ quote_style: Some('\'')
+ },
+ host: Ident {
+ value: "%".to_owned(),
+ quote_style: Some('\'')
+ }
+ }],
+ with_grant_option: false,
+ granted_by: None
+ }
+ )
+}
+
+#[test]
+fn parse_revoke() {
+ let sql = "REVOKE ALL ON db1.* FROM 'jeffrey'@'%'";
+ assert_eq!(
+ mysql().verified_stmt(sql),
+ Statement::Revoke {
+ privileges: Privileges::All {
+ with_privileges_keyword: false
+ },
+ objects: GrantObjects::Tables(vec![ObjectName(vec!["db1".into(), "*".into()])]),
+ grantees: vec![Grantee::UserHost {
+ user: Ident {
+ value: "jeffrey".to_owned(),
+ quote_style: Some('\'')
+ },
+ host: Ident {
+ value: "%".to_owned(),
+ quote_style: Some('\'')
+ }
+ }],
+ granted_by: None,
+ cascade: None,
+ }
+ )
+}
+
+#[test]
+fn parse_create_view_algorithm_param() {
+ let sql = "CREATE ALGORITHM = MERGE VIEW foo AS SELECT 1";
+ let stmt = mysql().verified_stmt(sql);
Review Comment:
can we add a couple scenarios for the other variants to get coverage? since we already verified the ast, they can be simpler roundtrip tests. e.g.
```rust
mysql().verified_stmt("CREATE ALGORITHM = UNDEFINED VIEW foo AS SELECT 1");
mysql().verified_stmt("CREATE ALGORITHM = TEMPTABLE VIEW foo AS SELECT 1");
```
##########
tests/sqlparser_mysql.rs:
##########
@@ -2971,3 +2971,133 @@ fn parse_bitstring_literal() {
))]
);
}
+
+#[test]
+fn parse_grant() {
+ let sql = "GRANT ALL ON *.* TO 'jeffrey'@'%'";
+ assert_eq!(
+ mysql().verified_stmt(sql),
+ Statement::Grant {
+ privileges: Privileges::All {
+ with_privileges_keyword: false
+ },
+ objects: GrantObjects::Tables(vec![ObjectName(vec!["*".into(), "*".into()])]),
+ grantees: vec![Grantee::UserHost {
+ user: Ident {
+ value: "jeffrey".to_owned(),
+ quote_style: Some('\'')
+ },
+ host: Ident {
+ value: "%".to_owned(),
+ quote_style: Some('\'')
+ }
+ }],
+ with_grant_option: false,
+ granted_by: None
+ }
+ )
+}
+
+#[test]
+fn parse_revoke() {
+ let sql = "REVOKE ALL ON db1.* FROM 'jeffrey'@'%'";
+ assert_eq!(
+ mysql().verified_stmt(sql),
Review Comment:
```suggestion
mysql_and_generic().verified_stmt(sql),
```
if that's applicable here, we can include generic in the cases to cover the features it supports
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#issuecomment-2489116782
Would appreciate feedback on the naming the MySQL-specific params types (would a generic/non-branded name be better?) and the API change for wildcard support (didn't want to change all 107 `parse_object_name` callsites for an option that is so niche, but happy to be convinced otherwise).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#issuecomment-2563681482
@mvzink it seems there's a couple conflicts in the branch could you take a look when you have some time?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#issuecomment-2578264216
Hi sorry I just noticed this was since rebased, could I ask for another rebase and if you could just ping me when its done I'll prio merging it to avoid further conflicts :pray:
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#issuecomment-2578858394
@iffyio rebased, but there were some significant changes to grantee parsing by @yoavcloud so maybe he should take a look too.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "yoavcloud (via GitHub)" <gi...@apache.org>.
yoavcloud commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1908272974
##########
src/parser/mod.rs:
##########
@@ -11808,23 +11899,32 @@ impl<'a> Parser<'a> {
}
}
+ pub fn parse_grantee_name(&mut self) -> Result<GranteeName, ParserError> {
+ let name = self.parse_object_name(false)?;
+ if self.dialect.supports_user_host_grantee()
+ && name.0.len() == 1
+ && self.consume_token(&Token::AtSign)
+ {
+ let user = name.0[0].clone();
Review Comment:
I see at least one case in Snowflake where you grant privileges to a database role, which can be a fully qualified object name. See here: https://docs.snowflake.com/en/sql-reference/sql/grant-privilege under database_role_name.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1893325618
##########
src/parser/mod.rs:
##########
@@ -8353,20 +8409,40 @@ impl<'a> Parser<'a> {
}
}
+ /// Parse a possibly qualified, possibly quoted identifier, optionally allowing for wildcards,
+ /// e.g. *, `foo`.*, or "foo"."bar"
+ pub fn parse_object_name_with_wildcards(
Review Comment:
Sorry it's taken me a while to get to this. It's a bit tricky, partly because the expression form shouldn't accept `*.*`. I will try to come back to this later, but have had higher priorities the last few weeks and probably will until later in January. In my opinion, it might be better to avoid dragging on with more merge conflicts etc. and merge without this suggested refactor. Let me know your thoughts.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on code in PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#discussion_r1859136590
##########
src/tokenizer.rs:
##########
@@ -1202,6 +1202,9 @@ impl<'a> Tokenizer<'a> {
}
}
Some(' ') => Ok(Some(Token::AtSign)),
+ Some('\'') => Ok(Some(Token::AtSign)),
+ Some('\"') => Ok(Some(Token::AtSign)),
+ Some('`') => Ok(Some(Token::AtSign)),
Review Comment:
I thought so at first too, but variables in MySQL start with `@` and I don't think there's a way to differentiate them during tokenization. We would want to tokenize `@foo` as a single `Token::Word`, but `@'foo'` (in my understanding) as `[Token::AtSign, Token::SingleQuotedString]`. In this check during tokenization, we aren't differentiating between "schema names etc." and "variable names", just "identifiers". Maybe there is something more correct to do here, like checking for `self.is_identifier_continuation(sch)` and ruling out `'` there. But my approach of just adding cases for quotes is based on the fact that as far as I can tell, no dialect allows quote marks within an identifier unless it's in an outer quotation, which doesn't get tokenized by this path.
I think an approach like `self.is_identifier_continuation(sch)` would effectively be the same but could allow customization per dialect, but it has another pitfall: say in addition to quote marks, a dialect doesn't allow `_` in an identifier. Then should we fail to tokenize `@_` with an error, or tokenize them separately as `[@, _]`? If the former, we would then need another special case for quote marks, and the latter seems wrong to me.
One other example in support of tokenizing like `[Token::AtSign, Token::SingleQuotedString]`, just in case it's not clear why I think that's right: in Postgres, `@` is absolute value, and it will implicitly cast the operand to a numeric type, so that `select @'1';` (no space after `@`) treats `'1'` as a string and casts it to double.
All that in mind, I still think my current approach is the simplest correct I can think of; let me know what you think!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Fix MySQL parsing of GRANT, REVOKE, and CREATE VIEW [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on PR #1538:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1538#issuecomment-2580824936
@iffyio I think this is good to go.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org