dongjoon-hyun commented on code in PR #47672:
URL: https://github.com/apache/spark/pull/47672#discussion_r1759952407
##########
sql/core/src/test/scala/org/apache/spark/sql/scripting/SqlScriptingInterpreterSuite.scala:
##########
@@ -368,6 +368,383 @@ 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("searched case no cases matched no else") {
+ val commands =
+ """
+ |BEGIN
+ | CASE
+ | WHEN 1 = 2 THEN
+ | SELECT 42;
+ | WHEN 1 = 3 THEN
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq()
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("searched case when evaluates to null") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | CREATE TABLE t (a BOOLEAN) USING parquet;
+ | CASE
+ | WHEN (SELECT * FROM t) THEN
+ | SELECT 42;
+ | END CASE;
+ |END
+ |""".stripMargin
+
+ checkError(
+ exception = intercept[SqlScriptingException] (
+ runSqlScript(commands)
+ ),
+ condition = "BOOLEAN_STATEMENT_WITH_EMPTY_ROW",
+ parameters = Map("invalidStatement" -> "(SELECT * FROM T)")
+ )
+ }
+ }
+
+ test("searched case with non boolean condition - constant") {
+ val commands =
+ """
+ |BEGIN
+ | CASE
+ | WHEN 1 THEN
+ | SELECT 42;
+ | END CASE;
+ |END
+ |""".stripMargin
+
+ checkError(
+ exception = intercept[SqlScriptingException] (
+ runSqlScript(commands)
+ ),
+ condition = "INVALID_BOOLEAN_STATEMENT",
+ parameters = Map("invalidStatement" -> "1")
+ )
+ }
+
+ test("searched case with too many rows in subquery condition") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | CREATE TABLE t (a BOOLEAN) USING parquet;
+ | INSERT INTO t VALUES (true);
+ | INSERT INTO t VALUES (true);
+ | CASE
+ | WHEN (SELECT * FROM t) THEN
+ | SELECT 1;
+ | END CASE;
+ |END
+ |""".stripMargin
+
+ checkError(
+ exception = intercept[SparkException] (
+ runSqlScript(commands)
+ ),
+ condition = "SCALAR_SUBQUERY_TOO_MANY_ROWS",
+ parameters = Map.empty,
+ context = ExpectedContext(fragment = "(SELECT * FROM t)", start = 124,
stop = 140)
+ )
+ }
+ }
+
+ 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 (2, 'b', 2.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)
+ }
+ }
+
+ test("simple case no cases matched no else") {
+ val commands =
+ """
+ |BEGIN
+ | CASE 1
+ | WHEN 2 THEN
+ | SELECT 42;
+ | WHEN 3 THEN
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq()
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("simple case mismatched types") {
+ val commands =
+ """
+ |BEGIN
+ | CASE 1
+ | WHEN "one" THEN
+ | SELECT 42;
+ | END CASE;
+ |END
+ |""".stripMargin
+
+ checkError(
+ exception = intercept[SparkNumberFormatException] (
Review Comment:
`SqlScriptingException` seems to be thrown instead of
`SparkNumberFormatException` .
```
[info] Cause: org.apache.spark.sql.exceptions.SqlScriptingException:
{LINE:4} [BOOLEAN_STATEMENT_WITH_EMPTY_ROW] Boolean statement "ONE" is invalid.
Expected single row with a value of the BOOLEAN type, but got an empty row.
SQLSTATE: 21000
```
--
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]
