dusantism-db commented on code in PR #47672:
URL: https://github.com/apache/spark/pull/47672#discussion_r1750339974
##########
sql/core/src/test/scala/org/apache/spark/sql/scripting/SqlScriptingInterpreterSuite.scala:
##########
@@ -368,6 +368,238 @@ class SqlScriptingInterpreterSuite extends QueryTest with
SharedSparkSession {
}
}
+ test("searched case") {
+ val commands =
+ """
+ |BEGIN
+ | CASE
+ | WHEN 1 = 1 THEN
+ | SELECT 42;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(42)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("searched case nested") {
+ val commands =
+ """
+ |BEGIN
+ | CASE
+ | WHEN 1=1 THEN
+ | CASE
+ | WHEN 2=1 THEN
+ | SELECT 41;
+ | ELSE
+ | SELECT 42;
+ | END CASE;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(42)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("searched case second case") {
+ val commands =
+ """
+ |BEGIN
+ | CASE
+ | WHEN 1 = (SELECT 2) THEN
+ | SELECT 1;
+ | WHEN 2 = 2 THEN
+ | SELECT 42;
+ | WHEN (SELECT * FROM t) THEN
+ | SELECT * FROM b;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(42)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("searched case going in else") {
+ val commands =
+ """
+ |BEGIN
+ | CASE
+ | WHEN 2 = 1 THEN
+ | SELECT 1;
+ | WHEN 3 IN (1,2) THEN
+ | SELECT 2;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(43)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("searched case with count") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ |CREATE TABLE t (a INT, b STRING, c DOUBLE) USING parquet;
+ |INSERT INTO t VALUES (1, 'a', 1.0);
+ |INSERT INTO t VALUES (1, 'a', 1.0);
+ |CASE
+ | WHEN (SELECT COUNT(*) > 2 FROM t) THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(Seq.empty[Row], Seq.empty[Row], Seq.empty[Row],
Seq(Row(43)))
+ verifySqlScriptResult(commands, expected)
+ }
+ }
+
+ test("searched case else with count") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | CREATE TABLE t (a INT, b STRING, c DOUBLE) USING parquet;
+ | INSERT INTO t VALUES (1, 'a', 1.0);
+ | INSERT INTO t VALUES (1, 'a', 1.0);
+ | CASE
+ | WHEN (SELECT COUNT(*) > 2 FROM t) THEN
+ | SELECT 42;
+ | WHEN (SELECT COUNT(*) > 1 FROM t) THEN
+ | SELECT 43;
+ | ELSE
+ | SELECT 44;
+ | END CASE;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(Seq.empty[Row], Seq.empty[Row], Seq.empty[Row],
Seq(Row(43)))
+ verifySqlScriptResult(commands, expected)
+ }
+ }
+
+ test("simple case") {
+ val commands =
+ """
+ |BEGIN
+ | CASE 1
+ | WHEN 1 THEN
+ | SELECT 42;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(42)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("simple case nested") {
+ val commands =
+ """
+ |BEGIN
+ | CASE 1
+ | WHEN 1 THEN
+ | CASE 2
+ | WHEN (SELECT 3) THEN
+ | SELECT 41;
+ | ELSE
+ | SELECT 42;
+ | END CASE;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(42)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("simple case second case") {
+ val commands =
+ """
+ |BEGIN
+ | CASE (SELECT 2)
+ | WHEN 1 THEN
+ | SELECT 1;
+ | WHEN 2 THEN
+ | SELECT 42;
+ | WHEN (SELECT * FROM t) THEN
+ | SELECT * FROM b;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(42)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("simple case going in else") {
+ val commands =
+ """
+ |BEGIN
+ | CASE 1
+ | WHEN 2 THEN
+ | SELECT 1;
+ | WHEN 3 THEN
+ | SELECT 2;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(43)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("simple case with count") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ |CREATE TABLE t (a INT, b STRING, c DOUBLE) USING parquet;
+ |INSERT INTO t VALUES (1, 'a', 1.0);
+ |INSERT INTO t VALUES (1, 'a', 1.0);
+ |CASE (SELECT COUNT(*) FROM t)
+ | WHEN 1 THEN
+ | SELECT 41;
+ | WHEN 2 THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(Seq.empty[Row], Seq.empty[Row], Seq.empty[Row],
Seq(Row(42)))
+ verifySqlScriptResult(commands, expected)
+ }
+ }
+
+ test("simple case else with count") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | CREATE TABLE t (a INT, b STRING, c DOUBLE) USING parquet;
+ | INSERT INTO t VALUES (1, 'a', 1.0);
+ | INSERT INTO t VALUES (1, 'a', 1.0);
+ | CASE (SELECT COUNT(*) FROM t)
+ | WHEN 1 THEN
+ | SELECT 42;
+ | WHEN 3 THEN
+ | SELECT 43;
+ | ELSE
+ | SELECT 44;
+ | END CASE;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(Seq.empty[Row], Seq.empty[Row], Seq.empty[Row],
Seq(Row(44)))
+ verifySqlScriptResult(commands, expected)
+ }
+ }
Review Comment:
added negative tests
--
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]
