cloud-fan commented on code in PR #49427:
URL: https://github.com/apache/spark/pull/49427#discussion_r1931424437
##########
sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/parser/SqlScriptingParserSuite.scala:
##########
@@ -2317,6 +2332,327 @@ class SqlScriptingParserSuite extends SparkFunSuite
with SQLHelper {
head.asInstanceOf[SingleStatement].getText == "SELECT 3")
}
+ test("declare condition: custom sqlstate") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE test CONDITION FOR SQLSTATE '12000';
+ | SELECT 1;
+ |END""".stripMargin
+ val tree = parsePlan(sqlScriptText).asInstanceOf[CompoundBody]
+ assert(tree.conditions.size == 1)
+ assert(tree.conditions("test").equals("12000"))
+ }
+
+ ignore("declare condition: default sqlstate") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE test CONDITION;
+ |END""".stripMargin
+ val tree = parsePlan(sqlScriptText).asInstanceOf[CompoundBody]
+ assert(tree.conditions.size == 1)
+ assert(tree.conditions("test").equals("45000")) // Default SQLSTATE
+ }
+
+ test("declare condition in wrong place") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | SELECT 1;
+ | DECLARE test_condition CONDITION FOR SQLSTATE '12345';
+ |END""".stripMargin
+ val exception = intercept[SqlScriptingException] {
+ parsePlan(sqlScriptText)
+ }
+ checkError(
+ exception = exception,
+ condition = "INVALID_ERROR_CONDITION_DECLARATION.ONLY_AT_BEGINNING",
+ parameters = Map("conditionName" -> "`test_condition`"))
+ assert(exception.origin.line.contains(2))
+ }
+
+ test("declare qualified condition") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE TEST.CONDITION CONDITION FOR SQLSTATE '12345';
+ |END""".stripMargin
+ val exception = intercept[SqlScriptingException] {
+ parsePlan(sqlScriptText)
+ }
+ checkError(
+ exception = exception,
+ condition =
"INVALID_ERROR_CONDITION_DECLARATION.QUALIFIED_CONDITION_NAME",
+ parameters = Map("conditionName" -> "TEST.CONDITION"))
+ assert(exception.origin.line.contains(3))
+ }
+
+ test("continue handler not supported") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE flag INT = -1;
+ | DECLARE CONTINUE HANDLER FOR SQLSTATE '22012'
+ | BEGIN
+ | SET VAR flag = 1;
+ | END;
+ | SELECT 1/0;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SqlScriptingException] {
+ parsePlan(sqlScript)
+ },
+ condition = "UNSUPPORTED_FEATURE.CONTINUE_EXCEPTION_HANDLER",
+ parameters = Map.empty)
+ }
+
+ test("declare handler for qualified condition name that is not supported") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE flag INT = -1;
+ | DECLARE EXIT HANDLER FOR qualified.condition.name
+ | BEGIN
+ | SET VAR flag = 1;
+ | END;
+ | SELECT 1/0;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SqlScriptingException] {
+ parsePlan(sqlScript)
+ },
+ condition =
"INVALID_ERROR_CONDITION_DECLARATION.QUALIFIED_CONDITION_NAME",
+ parameters = Map("conditionName" -> "QUALIFIED.CONDITION.NAME"))
+ }
+
+ test("declare handler in wrong place") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | SELECT 1;
+ | DECLARE EXIT HANDLER FOR test_condition BEGIN SELECT 1; END;
+ |END""".stripMargin
+ val exception = intercept[SqlScriptingException] {
+ parsePlan(sqlScriptText)
+ }
+ checkError(
+ exception = exception,
+ condition = "INVALID_HANDLER_DECLARATION.WRONG_PLACE_OF_DECLARATION",
+ parameters = Map.empty)
+ assert(exception.origin.line.contains(2))
+ }
+
+ test("duplicate condition in handler declaration") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE flag INT = -1;
+ | DECLARE EXIT HANDLER FOR duplicate_condition, duplicate_condition
+ | BEGIN
+ | SET VAR flag = 1;
+ | END;
+ | SELECT 1/0;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SqlScriptingException] {
+ parsePlan(sqlScript)
+ },
+ condition =
"INVALID_HANDLER_DECLARATION.DUPLICATE_CONDITION_IN_HANDLER_DECLARATION",
+ parameters = Map("condition" -> "duplicate_condition"))
+ }
+
+ test("duplicate sqlState in handler declaration") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE flag INT = -1;
+ | DECLARE EXIT HANDLER FOR SQLSTATE '12345', SQLSTATE '12345'
+ | BEGIN
+ | SET VAR flag = 1;
+ | END;
+ | SELECT 1/0;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SqlScriptingException] {
+ parsePlan(sqlScript)
+ },
+ condition =
"INVALID_HANDLER_DECLARATION.DUPLICATE_SQLSTATE_IN_HANDLER_DECLARATION",
+ parameters = Map("sqlState" -> "12345"))
+ }
+
+ test("invalid condition combination in handler declaration") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE flag INT = -1;
+ | DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLSTATE '12345'
+ | BEGIN
+ | SET VAR flag = 1;
+ | END;
+ | SELECT 1/0;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SqlScriptingException] {
+ parsePlan(sqlScript)
+ },
+ condition = "INVALID_HANDLER_DECLARATION.INVALID_CONDITION_COMBINATION",
+ parameters = Map.empty)
+ }
+
+ test("declare handler with compound body") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE EXIT HANDLER FOR test_condition BEGIN SELECT 1; END;
+ |END""".stripMargin
+ val tree = parsePlan(sqlScriptText).asInstanceOf[CompoundBody]
+ assert(tree.handlers.length == 1)
+ assert(tree.handlers.head.isInstanceOf[ExceptionHandler])
+ assert(tree.handlers.head.exceptionHandlerTriggers.conditions.size == 1)
+
assert(tree.handlers.head.exceptionHandlerTriggers.conditions.contains("test_condition"))
+ assert(tree.handlers.head.body.collection.size == 1)
+
assert(tree.handlers.head.body.collection.head.isInstanceOf[SingleStatement])
+
assert(tree.handlers.head.body.collection.head.asInstanceOf[SingleStatement]
+ .parsedPlan.isInstanceOf[Project])
+ }
+
+ // This test works because END is not keyword here but a part of the
statement.
+ // It represents the name of the column in returned dataframe.
+ test("declare handler single statement with END") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE EXIT HANDLER FOR test_condition SELECT 1 END;
+ |END""".stripMargin
+ val tree = parsePlan(sqlScriptText).asInstanceOf[CompoundBody]
+ assert(tree.handlers.length == 1)
+ assert(tree.handlers.head.isInstanceOf[ExceptionHandler])
+ assert(tree.handlers.head.exceptionHandlerTriggers.conditions.size == 1)
+
assert(tree.handlers.head.exceptionHandlerTriggers.conditions.contains("test_condition"))
+ assert(tree.handlers.head.body.collection.size == 1)
+
assert(tree.handlers.head.body.collection.head.isInstanceOf[SingleStatement])
+
assert(tree.handlers.head.body.collection.head.asInstanceOf[SingleStatement]
+ .parsedPlan.isInstanceOf[Project])
+ }
+
+ test("declare handler single statement") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE EXIT HANDLER FOR test_condition SELECT 1;
+ |END""".stripMargin
+ val tree = parsePlan(sqlScriptText).asInstanceOf[CompoundBody]
+ assert(tree.handlers.length == 1)
+ assert(tree.handlers.head.isInstanceOf[ExceptionHandler])
+ assert(tree.handlers.head.exceptionHandlerTriggers.conditions.size == 1)
+
assert(tree.handlers.head.exceptionHandlerTriggers.conditions.contains("test_condition"))
+ assert(tree.handlers.head.body.collection.size == 1)
+
assert(tree.handlers.head.body.collection.head.isInstanceOf[SingleStatement])
+
assert(tree.handlers.head.body.collection.head.asInstanceOf[SingleStatement]
+ .parsedPlan.isInstanceOf[Project])
+ }
+
+ test("declare handler set statement") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE EXIT HANDLER FOR test_condition SET test_var = 1;
+ |END""".stripMargin
+ val tree = parsePlan(sqlScriptText).asInstanceOf[CompoundBody]
+ assert(tree.handlers.length == 1)
+ assert(tree.handlers.head.isInstanceOf[ExceptionHandler])
+ assert(tree.handlers.head.exceptionHandlerTriggers.conditions.size == 1)
+
assert(tree.handlers.head.exceptionHandlerTriggers.conditions.contains("test_condition"))
+ assert(tree.handlers.head.body.collection.size == 1)
+
assert(tree.handlers.head.body.collection.head.isInstanceOf[SingleStatement])
+
assert(tree.handlers.head.body.collection.head.asInstanceOf[SingleStatement]
+ .parsedPlan.isInstanceOf[SetVariable])
+ }
+
+ test("declare handler with multiple conditions/sqlstates") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE EXIT HANDLER FOR SQLSTATE '22012', test_condition_1,
test_condition_2
+ | SET test_var = 1;
+ |END""".stripMargin
+ val tree = parsePlan(sqlScriptText).asInstanceOf[CompoundBody]
+ assert(tree.handlers.length == 1)
+ assert(tree.handlers.head.isInstanceOf[ExceptionHandler])
+ assert(tree.handlers.head.exceptionHandlerTriggers.conditions.size == 2)
+
assert(tree.handlers.head.exceptionHandlerTriggers.conditions.contains("test_condition_1"))
+
assert(tree.handlers.head.exceptionHandlerTriggers.conditions.contains("test_condition_2"))
+ assert(tree.handlers.head.exceptionHandlerTriggers.sqlStates.size == 1)
+
assert(tree.handlers.head.exceptionHandlerTriggers.sqlStates.contains("22012"))
+ assert(tree.handlers.head.body.collection.size == 1)
+
assert(tree.handlers.head.body.collection.head.isInstanceOf[SingleStatement])
+
assert(tree.handlers.head.body.collection.head.asInstanceOf[SingleStatement]
+ .parsedPlan.isInstanceOf[SetVariable])
+ }
+
+ test("declare handler for SQLEXCEPTION") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE EXIT HANDLER FOR SQLEXCEPTION SET test_var = 1;
+ |END""".stripMargin
+ val tree = parsePlan(sqlScriptText).asInstanceOf[CompoundBody]
+ assert(tree.handlers.length == 1)
+ assert(tree.handlers.head.isInstanceOf[ExceptionHandler])
+ assert(tree.handlers.head.exceptionHandlerTriggers.conditions.isEmpty)
+ assert(tree.handlers.head.exceptionHandlerTriggers.sqlStates.isEmpty)
+ assert(tree.handlers.head.exceptionHandlerTriggers.sqlException) // true
+ assert(!tree.handlers.head.exceptionHandlerTriggers.notFound) // false
+ assert(tree.handlers.head.body.collection.size == 1)
+ }
+
+ test("declare handler for NOT FOUND") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE EXIT HANDLER FOR NOT FOUND SET test_var = 1;
+ |END""".stripMargin
+ val tree = parsePlan(sqlScriptText).asInstanceOf[CompoundBody]
+ assert(tree.handlers.length == 1)
+ assert(tree.handlers.head.isInstanceOf[ExceptionHandler])
+ assert(tree.handlers.head.exceptionHandlerTriggers.conditions.isEmpty)
+ assert(tree.handlers.head.exceptionHandlerTriggers.sqlStates.isEmpty)
+ assert(!tree.handlers.head.exceptionHandlerTriggers.sqlException) // true
+ assert(tree.handlers.head.exceptionHandlerTriggers.notFound) // false
+ assert(tree.handlers.head.body.collection.size == 1)
+ }
+
+ test("declare handler with condition and sqlstate with same value") {
+ val sqlScriptText =
+ """
+ |BEGIN
+ | DECLARE K2000 CONDITION FOR SQLSTATE '12345';
+ | DECLARE EXIT HANDLER FOR K2000, SQLSTATE VALUE 'K2000' SET test_var
= 1;
Review Comment:
I'd like to confirm the by-design behavior: the condition name in the error
handler can either reference to a user-defined error condition in the same
compound statement, or a spark-defined error condition like DIVIDE_BY_ZERO? Can
the user-define error condition use the same name to overwrite spark-define
error condition?
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
