dusantism-db commented on code in PR #49427:
URL: https://github.com/apache/spark/pull/49427#discussion_r1923910371
##########
sql/core/src/test/scala/org/apache/spark/sql/scripting/SqlScriptingExecutionSuite.scala:
##########
@@ -65,6 +68,426 @@ class SqlScriptingExecutionSuite extends QueryTest with
SharedSparkSession {
}
}
+ // Handler tests
+ 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] {
+ verifySqlScriptResult(sqlScript, Seq.empty)
+ },
+ condition = "UNSUPPORTED_FEATURE.CONTINUE_ERROR_HANDLER",
+ parameters = Map.empty)
+ }
+
+ test("duplicate handler") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE flag INT = -1;
+ | DECLARE EXIT HANDLER FOR SQLSTATE '22012'
+ | BEGIN
+ | SET VAR flag = 1;
+ | END;
+ | DECLARE EXIT HANDLER FOR SQLSTATE '22012'
+ | BEGIN
+ | SET VAR flag = 2;
+ | END;
+ | SELECT 1/0;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SqlScriptingException] {
+ verifySqlScriptResult(sqlScript, Seq.empty)
+ },
+ condition = "DUPLICATE_HANDLER_FOR_SAME_CONDITION",
+ parameters = Map("condition" -> "22012"))
+ }
+
+ test("Specific condition takes precedence over sqlState") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE flag INT = -1;
+ | BEGIN
+ | DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
+ | BEGIN
+ | SELECT flag;
+ | SET VAR flag = 1;
+ | END;
+ | DECLARE EXIT HANDLER FOR SQLSTATE '22012'
+ | BEGIN
+ | SELECT flag;
+ | SET VAR flag = 2;
+ | END;
+ | SELECT 1/0;
+ | END;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq(Row(-1)), // select flag
+ Seq(Row(1)) // select flag from the outer body
+ )
+ verifySqlScriptResult(sqlScript, expected = expected)
+ }
+
+ test("Innermost handler takes precedence over other handlers") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE flag INT = -1;
+ | DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
+ | BEGIN
+ | SELECT flag;
+ | SET VAR flag = 1;
+ | END;
+ | BEGIN
+ | DECLARE EXIT HANDLER FOR SQLSTATE '22012'
+ | BEGIN
+ | SELECT flag;
+ | SET VAR flag = 2;
+ | END;
+ | SELECT 1/0;
+ | END;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq(Row(-1)), // select flag
+ Seq(Row(2)) // select flag from the outer body
+ )
+ verifySqlScriptResult(sqlScript, expected = expected)
+ }
+
+ test("handler - exit resolve in the same block") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE VARIABLE flag INT = -1;
+ | scope_to_exit: BEGIN
+ | DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
+ | BEGIN
+ | SELECT flag;
+ | SET VAR flag = 1;
+ | END;
+ | SELECT 2;
+ | SELECT 3;
+ | SELECT 1/0;
+ | SELECT 4;
+ | SELECT 5;
+ | END;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq(Row(2)), // select
+ Seq(Row(3)), // select
+ Seq(Row(-1)), // select flag
+ Seq(Row(1)) // select flag from the outer body
+ )
+ verifySqlScriptResult(sqlScript, expected = expected)
+ }
+
+ test("handler - exit resolve in the same block when if condition fails") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE VARIABLE flag INT = -1;
+ | scope_to_exit: BEGIN
+ | DECLARE EXIT HANDLER FOR SQLSTATE '22012'
+ | BEGIN
+ | SELECT flag;
+ | SET VAR flag = 1;
+ | END;
+ | SELECT 2;
+ | SELECT 3;
+ | IF 1 > 1/0 THEN
+ | SELECT 10;
+ | END IF;
+ | SELECT 4;
+ | SELECT 5;
+ | END;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq(Row(2)), // select
+ Seq(Row(3)), // select
+ Seq(Row(-1)), // select flag
+ Seq(Row(1)) // select flag from the outer body
+ )
+ verifySqlScriptResult(sqlScript, expected = expected)
+ }
+
+ test("handler - exit resolve in outer block") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE OR REPLACE VARIABLE flag INT = -1;
+ | l1: BEGIN
+ | DECLARE EXIT HANDLER FOR SQLSTATE '22012'
+ | BEGIN
+ | SELECT flag;
+ | SET VAR flag = 1;
+ | END;
+ | SELECT 2;
+ | SELECT 3;
+ | l2: BEGIN
+ | SELECT 4;
+ | SELECT 1/0;
+ | SELECT 5;
+ | END;
+ | SELECT 6;
+ | END;
+ | SELECT flag;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq(Row(2)), // select
+ Seq(Row(3)), // select
+ Seq(Row(4)), // select
+ Seq(Row(-1)), // select flag
+ Seq(Row(1)) // select flag from the outer body
+ )
+ verifySqlScriptResult(sqlScript, expected = expected)
+ }
+
+ test("handler - double chained handlers") {
Review Comment:
Maybe it would useful to have a test similar to this one, but catch specific
exceptions instead of SQLEXCEPTION. For example, inner body throws and catches
divide by zero and outer throws unresolved var. The purpose would be to make
sure the proper error gets propagated when chaining handlers.
--
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: reviews-unsubscr...@spark.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org
For additional commands, e-mail: reviews-h...@spark.apache.org
