You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "aharpervc (via GitHub)" <gi...@apache.org> on 2025/04/10 19:55:16 UTC
[PR] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
aharpervc opened a new pull request, #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808
For reference: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver16
In SQL Server, functions look a lot like procedures, with begin/end, multiple statements, and a return. To accommodate that here in the parser, a `MultiStatement` function body type is introduced along with a `RETURN` statement type.
Then also while I was working on this, I noticed that only SQL Server (so far...?) supports `OR ALTER`, so I introduced a new struct field to track that. (Again, very similar to `CREATE PROCEDURE` logic).
I'm not aiming to enable parsing every function type here in this PR -- just trying to get something operational, and the additional capabilities like table expressions, etc, can be added in later.
---
Also while I was here, I noticed that both these new multi statement functions & the existing procedure parsing fails unless the statements are semi-colon delimited. Semi-colons are optional in SQL Server, so for example, this code executes fine for real but fails to parse here in this library, unless that "SET" ends with a `;`...
```mssql
CREATE OR ALTER PROCEDURE test (@foo INT, @bar VARCHAR(256))
AS
BEGIN
SET @foo = @foo + 1
SELECT @foo
END
```
I think this is because the procedure body parsing logic is using `parse_statements()`, which requires it? I'm game to tackle the problem with some guidance on what's a good approach to make it parse 🤔
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#issuecomment-2835931034
Thanks again @iffyio and @aharpervc
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2051037497
##########
src/ast/spans.rs:
##########
@@ -2280,6 +2277,12 @@ impl Spanned for TableObject {
}
}
+impl Spanned for BeginEndStatements {
+ fn span(&self) -> Span {
+ union_spans([self.begin_token.0.span, self.end_token.0.span].into_iter())
Review Comment:
I updated this, although I'm unsure if my implementation was what you're suggesting
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047762830
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
+ statements: vec![Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Value(
+ (number("1")).with_empty_span()
+ ))),
+ }),],
+ end_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "END".to_string(),
+ quote_style: None,
+ keyword: Keyword::END
+ }
+ ))),
+ })),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let multi_statement_function = "\
+ CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) \
+ RETURNS INT \
+ AS \
+ BEGIN \
+ SET @foo = @foo + 1; \
+ RETURN @foo; \
+ END\
+ ";
+ assert_eq!(
+ ms().verified_stmt(multi_statement_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
+ statements: vec![
+ Statement::Set(Set::SingleAssignment {
+ scope: None,
+ hivevar: false,
+ variable: ObjectName::from(vec!["@foo".into()]),
+ values: vec![sqlparser::ast::Expr::BinaryOp {
+ left: Box::new(sqlparser::ast::Expr::Identifier(Ident {
+ value: "@foo".to_string(),
+ quote_style: None,
+ span: Span::empty(),
+ })),
+ op: sqlparser::ast::BinaryOperator::Plus,
+ right: Box::new(Expr::Value(number("1").with_empty_span())),
+ }],
+ }),
+ Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Identifier(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }))),
+ }),
+ ],
+ end_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "END".to_string(),
+ quote_style: None,
+ keyword: Keyword::END
+ }
+ ))),
+ })),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let create_function_with_conditional = r#"
+ CREATE FUNCTION some_scalar_udf()
+ RETURNS INT
+ AS
+ BEGIN
+ IF 1=2
+ BEGIN
+ RETURN 1;
+ END
+
+ RETURN 0;
+ END
+ "#;
+ let create_stmt = ms().one_statement_parses_to(create_function_with_conditional, "");
Review Comment:
I think the main downside is we lose the newlines, right? So we can't write SQL text the conventional way with new lines with `r#"..."#;`. ~Unless, are you suggesting this example case be reduced to _only_ verified_stmt, and no ast assertion?~ (just saw your other comment)
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2052848713
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
This is to address a separate issue. The situation is addressed by the "create_function_with_conditional" test case example, which runs verified_stmt on this sql:
```mssql
CREATE FUNCTION some_scalar_udf()
RETURNS INT
AS
BEGIN
IF 1 = 2
BEGIN
RETURN 1;
END;
RETURN 0;
END
```
The difficulty is regarding the IF statement. The pre-existing `parse_if_stmt` logic advances the parse past semi-colons (if any) before looking for an `else` block. If there's no `else` block as in this example, the next token will be part of the next statement after END's semi-colon.
That conflicts with the pre-existing logic in `parse_statement_list`, which unconditionally required a semi-colon after each parsed statement.
Therefore, this example would incorrectly be a parser error. To work around that limitation I added this conditional logic to parse_statement_list to inspect the previous statement for an IF statement end block. If found, no semi colon is expected.
---
Thinking about it some more, I suppose it would be better to keep that concern inside the parse_if_stmt function rather than bleeding out into parse_statement_list. I've done that now on the branch, 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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2052851172
##########
src/parser/mod.rs:
##########
@@ -15063,6 +15127,15 @@ impl<'a> Parser<'a> {
message: Box::new(self.parse_expr()?),
}))
}
+ /// Parse [Statement::Return]
Review Comment:
Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2042207498
##########
src/ast/mod.rs:
##########
@@ -4050,6 +4051,16 @@ pub enum Statement {
arguments: Vec<Expr>,
options: Vec<RaisErrorOption>,
},
+ /// Return (SQL Server)
+ ///
+ /// for Functions:
+ /// RETURN
+ /// RETURN scalar_expression
+ /// RETURN select_statement
Review Comment:
Excellent. I will do this
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2040136168
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
Again, this will suffer the same fate as other multi statement syntax currently in this parser, which is that semi-colons are required to parse.
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2051414204
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
we seem to have some of the related changes left in this codepath? https://github.com/apache/datafusion-sqlparser-rs/blob/6d0ea6462ead65ff598105d8cd44468d972667cb/src/parser/mod.rs#L4458-L4466
##########
src/parser/mod.rs:
##########
@@ -15063,6 +15127,15 @@ impl<'a> Parser<'a> {
message: Box::new(self.parse_expr()?),
}))
}
+ /// Parse [Statement::Return]
Review Comment:
```suggestion
/// Parse [Statement::Return]
```
##########
tests/sqlparser_common.rs:
##########
@@ -15015,3 +15015,8 @@ fn parse_set_time_zone_alias() {
_ => unreachable!(),
}
}
+
+#[test]
+fn parse_return() {
+ all_dialects().verified_stmt("RETURN");
Review Comment:
hmm yeah given that its currently part of the parser behavior I think its reasonable to assert it. If the parser later stops accepting a top-level return value then we would change the test here to assert an error instead
##########
src/parser/mod.rs:
##########
@@ -5134,6 +5128,76 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [MsSql]
+ ///
+ /// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let (name, args) = self.parse_create_function_name_and_params()?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ return parser_err!("Expected RETURNS keyword", self.peek_token().span.start);
+ };
Review Comment:
```suggestion
self.expect_keyword(Keyword::RETURNS)?;
let return_type = Some(self.parse_data_type()?);
```
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2038224067
##########
src/ast/mod.rs:
##########
@@ -4050,6 +4051,16 @@ pub enum Statement {
arguments: Vec<Expr>,
options: Vec<RaisErrorOption>,
},
+ /// Return (SQL Server)
+ ///
+ /// for Functions:
+ /// RETURN
+ /// RETURN scalar_expression
+ /// RETURN select_statement
Review Comment:
1. Perhaps this example should be removed, until it's supported? (maybe the bare return, too)
2. Do we want a "ReturnType" enum to describe what will be?
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047778337
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
+ statements: vec![Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Value(
+ (number("1")).with_empty_span()
+ ))),
+ }),],
+ end_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "END".to_string(),
+ quote_style: None,
+ keyword: Keyword::END
+ }
+ ))),
+ })),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let multi_statement_function = "\
Review Comment:
Done 👍
If I'm understanding your thinking, we don't need a separate ast assertion for `or_alter`, because it's the preference of this project to pair parsing + writing, such that new properties like this can fully round trip, and therefore as long as the output matches the input we're appropriately covered. Is that accurate?
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2045572622
##########
src/ast/mod.rs:
##########
@@ -4050,6 +4051,13 @@ pub enum Statement {
arguments: Vec<Expr>,
options: Vec<RaisErrorOption>,
},
+ /// Return (Mssql)
+ ///
+ /// for Functions:
+ /// RETURN scalar_expression
+ ///
+ /// See: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
Review Comment:
Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2042196775
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
Review Comment:
I'll do it for this PR to get it merged.
Would you be willing to have a separate discussion about normalizing that name to `SQL Server` library-wide? The product/database itself is "SQL Server", and the language is "Transact-SQL" (aka T-SQL). I noticed that it's mostly like "BigQuery" rather than "Google BigQuery" and "Postgres" (or PostgreSQL) but not "PL/PGSQL". Therefore it seems like the most "normal" way to name it would just be "SQL Server" (or `SQLServer` for identifiers). The string "mssql" doesn't even show up [here](https://learn.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver16) or [here](https://en.wikipedia.org/wiki/Microsoft_SQL_Server), for reference.
Therefore it seems reasonable to me, to have SQL Server be more like BigQuery & PostgreSQL rather than it's own separate naming system...
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2051040245
##########
tests/sqlparser_common.rs:
##########
@@ -15015,3 +15015,8 @@ fn parse_set_time_zone_alias() {
_ => unreachable!(),
}
}
+
+#[test]
+fn parse_return() {
+ all_dialects().verified_stmt("RETURN");
Review Comment:
> Can we have this check assert the AST?
Done 👍
> Also we can add another test case that includes a return statement having an expression
Done, although this is invalid for SQL Server. I think having that test here would also mean we can't have a test that asserts an error when return with value is found as a top level statement. 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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047762830
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
+ statements: vec![Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Value(
+ (number("1")).with_empty_span()
+ ))),
+ }),],
+ end_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "END".to_string(),
+ quote_style: None,
+ keyword: Keyword::END
+ }
+ ))),
+ })),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let multi_statement_function = "\
+ CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) \
+ RETURNS INT \
+ AS \
+ BEGIN \
+ SET @foo = @foo + 1; \
+ RETURN @foo; \
+ END\
+ ";
+ assert_eq!(
+ ms().verified_stmt(multi_statement_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
+ statements: vec![
+ Statement::Set(Set::SingleAssignment {
+ scope: None,
+ hivevar: false,
+ variable: ObjectName::from(vec!["@foo".into()]),
+ values: vec![sqlparser::ast::Expr::BinaryOp {
+ left: Box::new(sqlparser::ast::Expr::Identifier(Ident {
+ value: "@foo".to_string(),
+ quote_style: None,
+ span: Span::empty(),
+ })),
+ op: sqlparser::ast::BinaryOperator::Plus,
+ right: Box::new(Expr::Value(number("1").with_empty_span())),
+ }],
+ }),
+ Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Identifier(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }))),
+ }),
+ ],
+ end_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "END".to_string(),
+ quote_style: None,
+ keyword: Keyword::END
+ }
+ ))),
+ })),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let create_function_with_conditional = r#"
+ CREATE FUNCTION some_scalar_udf()
+ RETURNS INT
+ AS
+ BEGIN
+ IF 1=2
+ BEGIN
+ RETURN 1;
+ END
+
+ RETURN 0;
+ END
+ "#;
+ let create_stmt = ms().one_statement_parses_to(create_function_with_conditional, "");
Review Comment:
I think the main downside is we lose the newlines, right? So we can't write SQL text the conventional way with new lines with `r#"..."#;`. Unless, are you suggesting this example case be reduced to _only_ verified_stmt, and no ast assertion?
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047771506
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
Review Comment:
Done 👍. I don't really understand the pro/con versus an empty token, though
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
Review Comment:
Makes sense, I didn't know you could do that. Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2042206786
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
I think I understand what you're getting at with regards to configuration, but I'm not yet sure how to actually implement that statement parsing logic.
Do you see the semicolon-less statement parsing question as a blocker for the PR? If not I'd prefer to get this in (per your other suggestions) and then tackle this separately
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2042299062
##########
src/ast/mod.rs:
##########
@@ -8368,6 +8387,22 @@ pub enum CreateFunctionBody {
///
/// [BigQuery]: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#syntax_11
AsAfterOptions(Expr),
+ /// Function body with statements before the `RETURN` keyword.
+ ///
+ /// Example:
+ /// ```sql
+ /// CREATE FUNCTION my_scalar_udf(a INT, b INT)
+ /// RETURNS INT
+ /// AS
+ /// BEGIN
+ /// DECLARE c INT;
+ /// SET c = a + b;
+ /// RETURN c;
+ /// END;
+ /// ```
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ MultiStatement(Vec<Statement>),
Review Comment:
Is the current iteration acceptable for this PR, or do you want me to do this now?
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2043612190
##########
src/ast/mod.rs:
##########
@@ -9203,6 +9232,30 @@ pub enum CopyIntoSnowflakeKind {
Location,
}
+#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub struct ReturnStatement {
+ pub value: Option<ReturnStatementValue>,
+}
+
+impl fmt::Display for ReturnStatement {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match &self.value {
+ Some(ReturnStatementValue::Expr(expr)) => write!(f, "RETURN {}", expr),
+ None => write!(f, "RETURN")
+ }
+ }
+}
+
+/// Variants of the Mssql `RETURN` statement
Review Comment:
```suggestion
/// Variants of a `RETURN` statement
```
since this statement isn't mysql specific
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5142,69 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [MsSql]
+ ///
+ /// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
Review Comment:
This part looks identical to the [bigquery implementation](https://github.com/apache/datafusion-sqlparser-rs/blob/1b50dd9d0764b96d9a6b4e81f77d77e7cae84097/src/parser/mod.rs#L5064-L5085) could we pull the logic out into a function to be reused by both?
##########
src/ast/mod.rs:
##########
@@ -4050,6 +4051,13 @@ pub enum Statement {
arguments: Vec<Expr>,
options: Vec<RaisErrorOption>,
},
+ /// Return (Mssql)
+ ///
+ /// for Functions:
+ /// RETURN scalar_expression
+ ///
+ /// See: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
Review Comment:
Maybe we can move this to the `ReturnStatement` struct definition, and here we can only point to the statement like `Parse a [ReturnStatement]`? Thinking having it on the struct will be more visible as the struct can be reused/reached from more places
##########
src/ast/mod.rs:
##########
@@ -8368,6 +8387,22 @@ pub enum CreateFunctionBody {
///
/// [BigQuery]: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#syntax_11
AsAfterOptions(Expr),
+ /// Function body with statements before the `RETURN` keyword.
+ ///
+ /// Example:
+ /// ```sql
+ /// CREATE FUNCTION my_scalar_udf(a INT, b INT)
+ /// RETURNS INT
+ /// AS
+ /// BEGIN
+ /// DECLARE c INT;
+ /// SET c = a + b;
+ /// RETURN c;
+ /// END;
+ /// ```
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ MultiStatement(Vec<Statement>),
Review Comment:
Yeah I think we ideally have it in this PR since this adds a new API that will otherwise need to be changed
##########
src/ast/mod.rs:
##########
@@ -4050,6 +4051,13 @@ pub enum Statement {
arguments: Vec<Expr>,
options: Vec<RaisErrorOption>,
},
+ /// Return (Mssql)
+ ///
+ /// for Functions:
+ /// RETURN scalar_expression
+ ///
+ /// See: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
Review Comment:
```suggestion
/// Return
///
/// for Functions:
/// RETURN scalar_expression
///
/// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
```
since the statement is supported by other dialects
##########
src/parser/mod.rs:
##########
@@ -15017,6 +15075,13 @@ impl<'a> Parser<'a> {
}
}
+ fn parse_return(&mut self) -> Result<Statement, ParserError> {
+ let expr = self.parse_expr()?;
Review Comment:
Maybe we can remove the return statement bits from this PR and tackle it separately as its own PR?
Otherwise indeed the expectation would be that this uses maybe_parse and we have the test cases in place as part of this PR
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
> but I'm not yet sure how to actually implement that statement parsing logic
hmm not sure I followed what we mean here? parsing a semicolon-less statement I imagine would follow from the above code snippet by calling `self.parse_statements_inner(false)`
It would indeed be good to resolve in this PR, currently in the PR we seem to have a workaround for this behavior [here](https://github.com/apache/datafusion-sqlparser-rs/blob/1b50dd9d0764b96d9a6b4e81f77d77e7cae84097/src/parser/mod.rs#L487-L493) and [here](https://github.com/apache/datafusion-sqlparser-rs/blob/1b50dd9d0764b96d9a6b4e81f77d77e7cae84097/src/parser/mod.rs#L5180-L5185) and another in the Go statement PR.
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
Review Comment:
Yeah the mssql name used in the library is unfortunate, but it wouldn't be worth the breakage to rename the [dialect itself](https://github.com/apache/datafusion-sqlparser-rs/blob/main/src/dialect/mssql.rs#L29-L31) and the documentation in the code follows from there, so that I think it would be preferable to remain consistent with the naming at this point even if the name isn't optimal
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5142,69 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [MsSql]
+ ///
+ /// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
Review Comment:
Yeah it sounds reasonable in that case to expect the returns clause
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2038224067
##########
src/ast/mod.rs:
##########
@@ -4050,6 +4051,16 @@ pub enum Statement {
arguments: Vec<Expr>,
options: Vec<RaisErrorOption>,
},
+ /// Return (SQL Server)
+ ///
+ /// for Functions:
+ /// RETURN
+ /// RETURN scalar_expression
+ /// RETURN select_statement
Review Comment:
1. Perhaps this example should be removed, until it's supported?
2. Do we want a "ReturnType" enum to describe what will be?
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2050170398
##########
src/ast/spans.rs:
##########
@@ -2280,6 +2277,12 @@ impl Spanned for TableObject {
}
}
+impl Spanned for BeginEndStatements {
+ fn span(&self) -> Span {
+ union_spans([self.begin_token.0.span, self.end_token.0.span].into_iter())
Review Comment:
We can do an exhausitve match here, so that the code is revisite if fields get added or changed
```rust
let BeginEndStatements {
begin,
statements,
end
} = self;
union_spans(begin, statements, end)
```
we can probably include statements in the union spans call if only for completeness
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
+ statements: vec![Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Value(
+ (number("1")).with_empty_span()
+ ))),
+ }),],
+ end_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "END".to_string(),
+ quote_style: None,
+ keyword: Keyword::END
+ }
+ ))),
+ })),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let multi_statement_function = "\
Review Comment:
Yeah that's correct!
##########
tests/sqlparser_common.rs:
##########
@@ -15015,3 +15015,8 @@ fn parse_set_time_zone_alias() {
_ => unreachable!(),
}
}
+
+#[test]
+fn parse_return() {
+ all_dialects().verified_stmt("RETURN");
Review Comment:
Can we have this check assert the AST? Also we can add another test case that includes a return statement having an expression
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
does something like in [this suggestion](https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2040592844) work just as well? its currently unclear to me why the semicolon handling is a special case of an `IfCondition` statement
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2045437472
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5142,69 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [MsSql]
+ ///
+ /// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
Review Comment:
Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2046079970
##########
src/ast/spans.rs:
##########
@@ -777,11 +778,9 @@ impl Spanned for ConditionalStatements {
ConditionalStatements::Sequence { statements } => {
union_spans(statements.iter().map(|s| s.span()))
}
- ConditionalStatements::BeginEnd {
- begin_token: AttachedToken(start),
- statements: _,
- end_token: AttachedToken(end),
- } => union_spans([start.span, end.span].into_iter()),
+ ConditionalStatements::BeginEnd(bes) => {
+ union_spans([bes.begin_token.0.span, bes.end_token.0.span].into_iter())
Review Comment:
Can we `impl Spanned for BeginEndStatements` and then call `bes.span()` here instead? That would make the span impl reusable by nother nodes that embed the `BeginEndStatements`
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
Review Comment:
```suggestion
name: Some(Ident::new("@foo")),
```
I think the idents in the tests can be simplified with the following
##########
src/ast/mod.rs:
##########
@@ -2317,15 +2313,37 @@ impl fmt::Display for ConditionalStatements {
}
Ok(())
}
- ConditionalStatements::BeginEnd { statements, .. } => {
- write!(f, "BEGIN ")?;
- format_statement_list(f, statements)?;
- write!(f, " END")
- }
+ ConditionalStatements::BeginEnd(bes) => write!(f, "{}", bes),
}
}
}
+/// A shared representation of `BEGIN`, multiple statements, and `END` tokens.
Review Comment:
```suggestion
/// Represents a list of statements enclosed within `BEGIN` and `END` keywords.
/// Example:
/// ```sql
/// BEGIN
/// SELECT 1;
/// SELECT 2;
/// END
/// ```
```
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
+ statements: vec![Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Value(
+ (number("1")).with_empty_span()
+ ))),
+ }),],
+ end_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "END".to_string(),
+ quote_style: None,
+ keyword: Keyword::END
+ }
+ ))),
+ })),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let multi_statement_function = "\
Review Comment:
to simplify the tests, I think we can have one of the test cases like `return_expression_function` as is, which asserts the returned AST. Then for the others, it should be enough to rely on `verified_stmt`, without fully asserting the AST. the latter has significant overhead maintenance wise so that ideally we use them more sparingly.
So essentially thinking for `multi_statement_function`, `create_function_with_conditional` and `create_or_alter_function` we just call `verified_stmt(sql)` to assert behavior. Then the `create_or_alter_function` test case I think we can move into this function since its part of the same feature
##########
src/ast/ddl.rs:
##########
@@ -2272,6 +2277,10 @@ impl fmt::Display for CreateFunction {
if let Some(CreateFunctionBody::AsAfterOptions(function_body)) = &self.function_body {
write!(f, " AS {function_body}")?;
}
+ if let Some(CreateFunctionBody::AsBeginEnd(bes)) = &self.function_body {
+ write!(f, " AS")?;
+ write!(f, " {}", bes)?;
Review Comment:
```suggestion
write!(f, " AS {bes}")?;
```
##########
src/ast/mod.rs:
##########
@@ -9211,6 +9253,41 @@ pub enum CopyIntoSnowflakeKind {
Location,
}
+/// Return (MsSql)
+///
+/// for Functions:
+/// RETURN scalar_expression
+///
+/// See <https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql>
+///
+/// for Triggers:
+/// RETURN
+///
+/// See <https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql>
Review Comment:
```suggestion
/// Represents a `Return` statement.
///
/// [MsSql triggers](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql)
/// [MySql functions](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql)
```
this makes it possible to include docs to other dialects like bigquery/snowflake that support this statement
##########
src/parser/mod.rs:
##########
@@ -15017,6 +15075,13 @@ impl<'a> Parser<'a> {
}
}
+ fn parse_return(&mut self) -> Result<Statement, ParserError> {
+ let expr = self.parse_expr()?;
Review Comment:
Ah alright, in that case I think its only the test cases missing for the return statement we can add those to common.rs I imagine
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
Review Comment:
```suggestion
begin_token: AttachedToken::empty(),
```
for tests in places where a token is needed, we can use this instead. since there is no Eq impl on the `AttachedToken` type
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +188,386 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1; END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
+ statements: vec![Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Value(
+ (number("1")).with_empty_span()
+ ))),
+ }),],
+ end_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "END".to_string(),
+ quote_style: None,
+ keyword: Keyword::END
+ }
+ ))),
+ })),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let multi_statement_function = "\
+ CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) \
+ RETURNS INT \
+ AS \
+ BEGIN \
+ SET @foo = @foo + 1; \
+ RETURN @foo; \
+ END\
+ ";
+ assert_eq!(
+ ms().verified_stmt(multi_statement_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::AsBeginEnd(BeginEndStatements {
+ begin_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "BEGIN".to_string(),
+ quote_style: None,
+ keyword: Keyword::BEGIN
+ }
+ ))),
+ statements: vec![
+ Statement::Set(Set::SingleAssignment {
+ scope: None,
+ hivevar: false,
+ variable: ObjectName::from(vec!["@foo".into()]),
+ values: vec![sqlparser::ast::Expr::BinaryOp {
+ left: Box::new(sqlparser::ast::Expr::Identifier(Ident {
+ value: "@foo".to_string(),
+ quote_style: None,
+ span: Span::empty(),
+ })),
+ op: sqlparser::ast::BinaryOperator::Plus,
+ right: Box::new(Expr::Value(number("1").with_empty_span())),
+ }],
+ }),
+ Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Identifier(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }))),
+ }),
+ ],
+ end_token: AttachedToken(TokenWithSpan::wrap(sqlparser::tokenizer::Token::Word(
+ sqlparser::tokenizer::Word {
+ value: "END".to_string(),
+ quote_style: None,
+ keyword: Keyword::END
+ }
+ ))),
+ })),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let create_function_with_conditional = r#"
+ CREATE FUNCTION some_scalar_udf()
+ RETURNS INT
+ AS
+ BEGIN
+ IF 1=2
+ BEGIN
+ RETURN 1;
+ END
+
+ RETURN 0;
+ END
+ "#;
+ let create_stmt = ms().one_statement_parses_to(create_function_with_conditional, "");
Review Comment:
can we use `verified_stmt` here instead (what does the empty canonical string imply)?
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2042200007
##########
src/ast/ddl.rs:
##########
@@ -2157,6 +2157,10 @@ impl fmt::Display for ClusteredBy {
#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
pub struct CreateFunction {
+ /// Conditionally alters the function only if it already exists.
Review Comment:
👍 I copy/pasted from the SQL Server docs page [here](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver16#or-alter), although I see your point about making the comment for the struct field rather than the semantics.
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2042434307
##########
src/ast/mod.rs:
##########
@@ -4050,6 +4051,16 @@ pub enum Statement {
arguments: Vec<Expr>,
options: Vec<RaisErrorOption>,
},
+ /// Return (SQL Server)
+ ///
+ /// for Functions:
+ /// RETURN
+ /// RETURN scalar_expression
+ /// RETURN select_statement
Review Comment:
We'll want bare `RETURN` for SQL Server trigger bodies, so the followup on this is over here: https://github.com/apache/datafusion-sqlparser-rs/pull/1810
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047575809
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
Thanks for the reminder.
> It would indeed be good to resolve in this PR, currently in the PR we seem to have a workaround for this behavior [here](https://github.com/apache/datafusion-sqlparser-rs/blob/1b50dd9d0764b96d9a6b4e81f77d77e7cae84097/src/parser/mod.rs#L487-L493) and [here](https://github.com/apache/datafusion-sqlparser-rs/blob/1b50dd9d0764b96d9a6b4e81f77d77e7cae84097/src/parser/mod.rs#L5180-L5185) and another in the Go statement PR.
I was able to refactor the latter, and the former we'll need regardless of the semi colon situation. Does the latest iteration resolve your concerns here?
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +187,145 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1 END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::MultiStatement(vec![
+ Statement::Return {
+ value: Some(Box::new(Expr::Value((number("1")).with_empty_span()))),
+ },
+ ])),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let multi_statement_function = "\
+ CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) \
+ RETURNS INT \
+ AS \
+ BEGIN \
+ SET @foo = @foo + 1; \
+ RETURN @foo \
Review Comment:
This is fixed
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2045435742
##########
src/ast/mod.rs:
##########
@@ -8368,6 +8387,22 @@ pub enum CreateFunctionBody {
///
/// [BigQuery]: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#syntax_11
AsAfterOptions(Expr),
+ /// Function body with statements before the `RETURN` keyword.
+ ///
+ /// Example:
+ /// ```sql
+ /// CREATE FUNCTION my_scalar_udf(a INT, b INT)
+ /// RETURNS INT
+ /// AS
+ /// BEGIN
+ /// DECLARE c INT;
+ /// SET c = a + b;
+ /// RETURN c;
+ /// END;
+ /// ```
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ MultiStatement(Vec<Statement>),
Review Comment:
> Yeah I think we ideally have it in this PR since this adds a new API that will otherwise need to be changed
Makes sense, and I've done it, per your suggestion above -- BeginEndStatements is extracted from ConditionalStatements as a re-usable struct, and then also used for parsing create function. It's much cleaner!
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047770271
##########
src/ast/mod.rs:
##########
@@ -9211,6 +9253,41 @@ pub enum CopyIntoSnowflakeKind {
Location,
}
+/// Return (MsSql)
+///
+/// for Functions:
+/// RETURN scalar_expression
+///
+/// See <https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql>
+///
+/// for Triggers:
+/// RETURN
+///
+/// See <https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql>
Review Comment:
Done 👍
---
BTW, see how easy it is for "MsSql" to accidentally become "M**y**Sql"? Another vote for renaming the dialect 😆
<img width="303" alt="image" src="https://github.com/user-attachments/assets/c99b0c0f-e2ac-4e8d-85be-08861b376610" />
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047770466
##########
src/ast/mod.rs:
##########
@@ -2317,15 +2313,37 @@ impl fmt::Display for ConditionalStatements {
}
Ok(())
}
- ConditionalStatements::BeginEnd { statements, .. } => {
- write!(f, "BEGIN ")?;
- format_statement_list(f, statements)?;
- write!(f, " END")
- }
+ ConditionalStatements::BeginEnd(bes) => write!(f, "{}", bes),
}
}
}
+/// A shared representation of `BEGIN`, multiple statements, and `END` tokens.
Review Comment:
Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047577310
##########
src/parser/mod.rs:
##########
@@ -580,10 +580,12 @@ impl<'a> Parser<'a> {
// `BEGIN` is a nonstandard but common alias for the
// standard `START TRANSACTION` statement. It is supported
// by at least PostgreSQL and MySQL.
+ // `BEGIN` is also used for multi-statement blocks in SQL Server
Keyword::BEGIN => self.parse_begin(),
// `END` is a nonstandard but common alias for the
// standard `COMMIT TRANSACTION` statement. It is supported
// by PostgreSQL.
+ // `END` is also used for multi-statement blocks in SQL Server
Review Comment:
Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2039938786
##########
src/ast/ddl.rs:
##########
@@ -2157,6 +2157,10 @@ impl fmt::Display for ClusteredBy {
#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
pub struct CreateFunction {
+ /// Conditionally alters the function only if it already exists.
+ ///
+ /// [SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver16#or-alter)
+ pub or_alter: bool,
pub or_replace: bool,
Review Comment:
I wasn't sure if there was a conventional ordering for these struct fields so I put it on top, before "or_replace". However I just noticed that parse_create has or_replace above or_alter here: https://github.com/apache/datafusion-sqlparser-rs/blob/d090ad4ccfd6c76c87de676fae50bbaec36e752a/src/parser/mod.rs#L4536-L4537
Happy to adjust it here for consistency if anyone prefers
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2040196781
##########
tests/sqlparser_mssql.rs:
##########
@@ -187,6 +187,145 @@ fn parse_mssql_create_procedure() {
let _ = ms().verified_stmt("CREATE PROCEDURE [foo] AS BEGIN UPDATE bar SET col = 'test'; SELECT [foo] FROM BAR WHERE [FOO] > 10 END");
}
+#[test]
+fn parse_create_function() {
+ let return_expression_function = "CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) RETURNS INT AS BEGIN RETURN 1 END";
+ assert_eq!(
+ ms().verified_stmt(return_expression_function),
+ sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::MultiStatement(vec![
+ Statement::Return {
+ value: Some(Box::new(Expr::Value((number("1")).with_empty_span()))),
+ },
+ ])),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+
+ let multi_statement_function = "\
+ CREATE FUNCTION some_scalar_udf(@foo INT, @bar VARCHAR(256)) \
+ RETURNS INT \
+ AS \
+ BEGIN \
+ SET @foo = @foo + 1; \
+ RETURN @foo \
Review Comment:
Hm, the return "should" have a semi-colon here... although I think if it did, parse_statements would produce a `COMMIT` for the end. I don't think this needs to block the PR, but could be some followup work.
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio merged PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2052853852
##########
src/parser/mod.rs:
##########
@@ -5134,6 +5128,76 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [MsSql]
+ ///
+ /// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let (name, args) = self.parse_create_function_name_and_params()?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ return parser_err!("Expected RETURNS keyword", self.peek_token().span.start);
+ };
Review Comment:
Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047853472
##########
src/ast/spans.rs:
##########
@@ -777,11 +778,9 @@ impl Spanned for ConditionalStatements {
ConditionalStatements::Sequence { statements } => {
union_spans(statements.iter().map(|s| s.span()))
}
- ConditionalStatements::BeginEnd {
- begin_token: AttachedToken(start),
- statements: _,
- end_token: AttachedToken(end),
- } => union_spans([start.span, end.span].into_iter()),
+ ConditionalStatements::BeginEnd(bes) => {
+ union_spans([bes.begin_token.0.span, bes.end_token.0.span].into_iter())
Review Comment:
Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047798057
##########
src/ast/spans.rs:
##########
@@ -777,11 +778,9 @@ impl Spanned for ConditionalStatements {
ConditionalStatements::Sequence { statements } => {
union_spans(statements.iter().map(|s| s.span()))
}
- ConditionalStatements::BeginEnd {
- begin_token: AttachedToken(start),
- statements: _,
- end_token: AttachedToken(end),
- } => union_spans([start.span, end.span].into_iter()),
+ ConditionalStatements::BeginEnd(bes) => {
+ union_spans([bes.begin_token.0.span, bes.end_token.0.span].into_iter())
Review Comment:
I think I understand what you're asking for, I'll give the implementation a shot
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2042310768
##########
src/parser/mod.rs:
##########
@@ -15017,6 +15075,13 @@ impl<'a> Parser<'a> {
}
}
+ fn parse_return(&mut self) -> Result<Statement, ParserError> {
+ let expr = self.parse_expr()?;
Review Comment:
Does this need to be a maybe_parse, like how parse_raise_stmt does it for Exprs? The approach I picked for the discussion of RETURN variants above was to only do what's needed for _this_ PR & _these_ tests, which always have an Expr after the return.
So I think perhaps what we'd need in a followup PR is:
1. test case for bare RETURN
2. update this to be use "maybe" logic to find an expr (perhaps we'd like `maybe_expr` to wrap up that pattern?)
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2042297201
##########
tests/sqlparser_mssql.rs:
##########
@@ -255,6 +256,93 @@ fn parse_create_function() {
assert_eq!(
ms().verified_stmt(multi_statement_function),
sqlparser::ast::Statement::CreateFunction(CreateFunction {
+ or_alter: false,
+ or_replace: false,
+ temporary: false,
+ if_not_exists: false,
+ name: ObjectName::from(vec![Ident {
+ value: "some_scalar_udf".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }]),
+ args: Some(vec![
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Int(None),
+ default_expr: None,
+ },
+ OperateFunctionArg {
+ mode: None,
+ name: Some(Ident {
+ value: "@bar".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }),
+ data_type: DataType::Varchar(Some(CharacterLength::IntegerLength {
+ length: 256,
+ unit: None
+ })),
+ default_expr: None,
+ },
+ ]),
+ return_type: Some(DataType::Int(None)),
+ function_body: Some(CreateFunctionBody::MultiStatement(vec![
+ Statement::Set(Set::SingleAssignment {
+ scope: None,
+ hivevar: false,
+ variable: ObjectName::from(vec!["@foo".into()]),
+ values: vec![sqlparser::ast::Expr::BinaryOp {
+ left: Box::new(sqlparser::ast::Expr::Identifier(Ident {
+ value: "@foo".to_string(),
+ quote_style: None,
+ span: Span::empty(),
+ })),
+ op: sqlparser::ast::BinaryOperator::Plus,
+ right: Box::new(Expr::Value(
+ (Value::Number("1".into(), false)).with_empty_span()
+ )),
+ }],
+ }),
+ Statement::Return(ReturnStatement {
+ value: Some(ReturnStatementValue::Expr(Expr::Identifier(Ident {
+ value: "@foo".into(),
+ quote_style: None,
+ span: Span::empty(),
+ }))),
+ }),
+ ])),
+ behavior: None,
+ called_on_null: None,
+ parallel: None,
+ using: None,
+ language: None,
+ determinism_specifier: None,
+ options: None,
+ remote_connection: None,
+ }),
+ );
+}
+
+#[test]
+fn parse_mssql_create_function() {
+ let create_or_alter_function = "\
Review Comment:
In the latest iteration, I moved this "create or alter" example to a separate test function. Not sure what the best pattern here is, trying to follow existing patterns as applicable 🤔
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2042834988
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5142,69 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [MsSql]
+ ///
+ /// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
Review Comment:
In SQL Server, all function types must have a `RETURNS ...` to be valid ([ref](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver16)). With this current code, a function will successfully parse even without that keyword. Therefore, it'd be up to library consumers to figure out that the return type is `None` and handle that accordingly. This seems incorrect. Therefore, I think it'd be better to expect the returns keyword.
@iffyio 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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047765609
##########
src/parser/mod.rs:
##########
@@ -15017,6 +15075,13 @@ impl<'a> Parser<'a> {
}
}
+ fn parse_return(&mut self) -> Result<Statement, ParserError> {
+ let expr = self.parse_expr()?;
Review Comment:
I've added a test case for a bare `RETURN` to sqlparser_common.rs, although I'm unsure how useful it is. At least for SQL Server, a top level return _must_ be bare (eg, can't return anything). Therefore, the more useful tests are where return is inside a function body, trigger body, etc. And we have those tests in their respective branches.
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2045450414
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5142,69 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [MsSql]
+ ///
+ /// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
Review Comment:
Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2046107175
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
@aharpervc just in case, note that this part of the PR is unresolved
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2047767596
##########
src/ast/ddl.rs:
##########
@@ -2272,6 +2277,10 @@ impl fmt::Display for CreateFunction {
if let Some(CreateFunctionBody::AsAfterOptions(function_body)) = &self.function_body {
write!(f, " AS {function_body}")?;
}
+ if let Some(CreateFunctionBody::AsBeginEnd(bes)) = &self.function_body {
+ write!(f, " AS")?;
+ write!(f, " {}", bes)?;
Review Comment:
Done 👍
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2045317219
##########
src/parser/mod.rs:
##########
@@ -15017,6 +15075,13 @@ impl<'a> Parser<'a> {
}
}
+ fn parse_return(&mut self) -> Result<Statement, ParserError> {
+ let expr = self.parse_expr()?;
Review Comment:
> Maybe we can remove the return statement bits from this PR and tackle it separately as its own PR?
I don't think this is possible, since SQL Server requires that functions have a return, so create function goes together with return. I'll take another pass on the PR though
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2051054233
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
I'm not deeply opposed to making an inner parse function, I guess, but it also doesn't seem required for _this_ PR.
> its currently unclear to me why the semicolon handling is a special case of an IfCondition statement
I reverted that change, I think either it was a problem while hacking on the branch or pollution from another branch 😬
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "iffyio (via GitHub)" <gi...@apache.org>.
iffyio commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2040586609
##########
src/ast/mod.rs:
##########
@@ -8368,6 +8387,22 @@ pub enum CreateFunctionBody {
///
/// [BigQuery]: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#syntax_11
AsAfterOptions(Expr),
+ /// Function body with statements before the `RETURN` keyword.
+ ///
+ /// Example:
+ /// ```sql
+ /// CREATE FUNCTION my_scalar_udf(a INT, b INT)
+ /// RETURNS INT
+ /// AS
+ /// BEGIN
+ /// DECLARE c INT;
+ /// SET c = a + b;
+ /// RETURN c;
+ /// END;
+ /// ```
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ MultiStatement(Vec<Statement>),
Review Comment:
Ah yeah I think if possible it would be good to reuse the same representation as `ConditionalStatements`.Maybe something like?
```rust
CreateFunctionBody::AsBeginEnd(BeginEndStatements)
ConditionalStatements::BeginEnd(BeginEndStatements)
```
Where we pull out the current representation into its own type, it'll also get to implement display once as a result too
##########
src/parser/mod.rs:
##########
@@ -580,10 +580,12 @@ impl<'a> Parser<'a> {
// `BEGIN` is a nonstandard but common alias for the
// standard `START TRANSACTION` statement. It is supported
// by at least PostgreSQL and MySQL.
+ // `BEGIN` is also used for multi-statement blocks in SQL Server
Keyword::BEGIN => self.parse_begin(),
// `END` is a nonstandard but common alias for the
// standard `COMMIT TRANSACTION` statement. It is supported
// by PostgreSQL.
+ // `END` is also used for multi-statement blocks in SQL Server
Review Comment:
```suggestion
Keyword::BEGIN => self.parse_begin(),
```
Thinking we can skip the comment entirely since they don't add much, it will avoid the dilemma of being inconsistent in the comment by not mentioning all supported dialects/use cases vs having to mention all of them
##########
src/parser/mod.rs:
##########
@@ -617,6 +619,9 @@ impl<'a> Parser<'a> {
}
// `COMMENT` is snowflake specific https://docs.snowflake.com/en/sql-reference/sql/comment
Keyword::COMMENT if self.dialect.supports_comment_on() => self.parse_comment(),
+ Keyword::RETURN if dialect_of!(self is MsSqlDialect) => {
Review Comment:
```suggestion
Keyword::RETURN => {
```
I think its reasonable to have the parser always parse the statement if it shows up. e.g. snowflake and bigquery also support the statement so we'd get support for them automatically as a result
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
+ fn parse_mssql_create_function(
+ &mut self,
+ or_alter: bool,
+ or_replace: bool,
+ temporary: bool,
+ ) -> Result<Statement, ParserError> {
+ let name = self.parse_object_name(false)?;
+
+ let parse_function_param =
+ |parser: &mut Parser| -> Result<OperateFunctionArg, ParserError> {
+ let name = parser.parse_identifier()?;
+ let data_type = parser.parse_data_type()?;
+ Ok(OperateFunctionArg {
+ mode: None,
+ name: Some(name),
+ data_type,
+ default_expr: None,
+ })
+ };
+ self.expect_token(&Token::LParen)?;
+ let args = self.parse_comma_separated0(parse_function_param, Token::RParen)?;
+ self.expect_token(&Token::RParen)?;
+
+ let return_type = if self.parse_keyword(Keyword::RETURNS) {
+ Some(self.parse_data_type()?)
+ } else {
+ None
+ };
+
+ self.expect_keyword_is(Keyword::AS)?;
+ self.expect_keyword_is(Keyword::BEGIN)?;
+ let function_body = Some(CreateFunctionBody::MultiStatement(self.parse_statements()?));
Review Comment:
Ah if the desired behavior is to have optional semicolon delimited statements for mssql, what we could do would be to make the behavior configurable when [parsing the statements list](https://github.com/apache/datafusion-sqlparser-rs/blob/2457d079daeb564782f8d408c19846fe45080caf/src/parser/mod.rs#L461). We could do something like this?
```rust
pub fn parse_statements(&mut self) -> Result<Vec<Statement>, ParserError> {
self.parse_statements_inner(true)
}
fn parse_statements_inner(&mut self, require_semicolon_delimiter) -> Result<Vec<Statement>, ParserError> {
// ... same as the body of `parse_statements()` on main
// ... I _think_ except for this part to make the behavior configurable
if require_semicolon_delimiter && expecting_statement_delimiter {
return self.expected("end of statement", self.peek_token());
}
// ...
}
```
then the mssql code can call the inner function directly
##########
src/ast/mod.rs:
##########
@@ -3603,13 +3603,14 @@ pub enum Statement {
managed_location: Option<String>,
},
/// ```sql
- /// CREATE FUNCTION
+ /// CREATE [OR ALTER] FUNCTION
Review Comment:
```suggestion
/// CREATE FUNCTION
```
similar to `ON REPLACE` etc, I think no need to enumerate the different syntax variants since they differ across dialects
##########
src/parser/mod.rs:
##########
@@ -5135,6 +5146,63 @@ impl<'a> Parser<'a> {
}))
}
+ /// Parse `CREATE FUNCTION` for [SQL Server]
+ ///
+ /// [SQL Server]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
Review Comment:
```suggestion
/// [MsSql]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
```
for this an similar places in the PR maybe we switch to `MsSql` since that's what the dialect is called? vs `Sql Server`
##########
src/ast/mod.rs:
##########
@@ -4050,6 +4051,16 @@ pub enum Statement {
arguments: Vec<Expr>,
options: Vec<RaisErrorOption>,
},
+ /// Return (SQL Server)
+ ///
+ /// for Functions:
+ /// RETURN
+ /// RETURN scalar_expression
+ /// RETURN select_statement
Review Comment:
1. Yeah, I think either we include support with tests for the `RETURN` statement in this PR or we can remove related new code and defer it to a follow up PR
2. Yeah due to the `Expr` vs `Statement` I suspect an enum would be required, maybe something like?
```rust
enum ReturnStatementValue {
Expr(Expr),
Statement(Statement)
}
struct ReturnStatement {
value: Option<ReturnStatementValue>
}
Statement::Return(ReturnStatement)
```
##########
src/ast/ddl.rs:
##########
@@ -2157,6 +2157,10 @@ impl fmt::Display for ClusteredBy {
#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
pub struct CreateFunction {
+ /// Conditionally alters the function only if it already exists.
Review Comment:
```suggestion
/// True if this is a `CREATE OR ALTER FUNCTION` statement
```
I think something like this would be more descriptive to someone reading the code or using the library? vs describing the database semantics of the flag
--
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] Add `CREATE FUNCTION` support for SQL Server [datafusion-sqlparser-rs]
Posted by "aharpervc (via GitHub)" <gi...@apache.org>.
aharpervc commented on code in PR #1808:
URL: https://github.com/apache/datafusion-sqlparser-rs/pull/1808#discussion_r2038226027
##########
tests/sqlparser_hive.rs:
##########
@@ -423,7 +423,7 @@ fn parse_create_function() {
}
// Test error in dialect that doesn't support parsing CREATE FUNCTION
- let unsupported_dialects = TestedDialects::new(vec![Box::new(MsSqlDialect {})]);
+ let unsupported_dialects = TestedDialects::new(vec![Box::new(AnsiDialect {})]);
Review Comment:
MSSQL dialect supports this now, so I had to pick another dialect 😁
--
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