This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 2367f5826119 [SPARK-52345][SQL] Fix NULL behavior in scripting
conditions
2367f5826119 is described below
commit 2367f5826119784b79c2957be017c07d758eb986
Author: David Milicevic <[email protected]>
AuthorDate: Thu May 29 21:17:37 2025 +0800
[SPARK-52345][SQL] Fix NULL behavior in scripting conditions
### What changes were proposed in this pull request?
`NULL` is a valid Boolean "value" in SQL. Scripting engine is not properly
handling cases when conditions (in if-else, case, while, repeat statements)
returned NULL.
Scripting engine throws an exception in such cases, stating that the NULL
is an invalid value.
Scripting engine should consider such NULLs as a False Boolean value.
### Why are the changes needed?
Fixes the wrong behavior in condition evaluation for scripting statements.
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
Old and new unit tests.
### Was this patch authored or co-authored using generative AI tooling?
No.
Closes #51047 from davidm-db/scripting_conditions_null_fix.
Authored-by: David Milicevic <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../src/main/resources/error/error-conditions.json | 6 -
.../spark/sql/errors/SqlScriptingErrors.scala | 10 -
.../sql/scripting/SqlScriptingExecutionNode.scala | 12 +-
.../scripting/SqlScriptingInterpreterSuite.scala | 915 ++++++++++++++-------
4 files changed, 619 insertions(+), 324 deletions(-)
diff --git a/common/utils/src/main/resources/error/error-conditions.json
b/common/utils/src/main/resources/error/error-conditions.json
index 731ecedb2a1e..3e689cecac3b 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -162,12 +162,6 @@
],
"sqlState" : "22003"
},
- "BOOLEAN_STATEMENT_WITH_EMPTY_ROW" : {
- "message" : [
- "Boolean statement <invalidStatement> is invalid. Expected single row
with a value of the BOOLEAN type, but got an empty row."
- ],
- "sqlState" : "21000"
- },
"CALL_ON_STREAMING_DATASET_UNSUPPORTED" : {
"message" : [
"The method <methodName> can not be called on streaming
Dataset/DataFrame."
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/SqlScriptingErrors.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/SqlScriptingErrors.scala
index ce0ed1f36a75..23b863f24bc8 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/SqlScriptingErrors.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/SqlScriptingErrors.scala
@@ -102,16 +102,6 @@ private[sql] object SqlScriptingErrors {
"sqlScriptingEnabled" -> toSQLConf(SQLConf.SQL_SCRIPTING_ENABLED.key)))
}
- def booleanStatementWithEmptyRow(
- origin: Origin,
- stmt: String): Throwable = {
- new SqlScriptingException(
- origin = origin,
- errorClass = "BOOLEAN_STATEMENT_WITH_EMPTY_ROW",
- cause = null,
- messageParameters = Map("invalidStatement" -> toSQLStmt(stmt)))
- }
-
def positionalParametersAreNotSupportedWithSqlScripting(): Throwable = {
new SqlScriptingException(
origin = null,
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/scripting/SqlScriptingExecutionNode.scala
b/sql/core/src/main/scala/org/apache/spark/sql/scripting/SqlScriptingExecutionNode.scala
index c95ef72a2b31..bf9762b505fb 100644
---
a/sql/core/src/main/scala/org/apache/spark/sql/scripting/SqlScriptingExecutionNode.scala
+++
b/sql/core/src/main/scala/org/apache/spark/sql/scripting/SqlScriptingExecutionNode.scala
@@ -84,18 +84,16 @@ trait NonLeafStatementExec extends CompoundStatementExec {
assert(!statement.isExecuted)
statement.isExecuted = true
- // DataFrame evaluates to True if it is single row, single column
- // of boolean type with value True.
+ // First, it is checked if DataFrame represents a valid Boolean
condition - single row,
+ // single column of Boolean type.
+ // If that is true, the condition evaluates to True only if the Boolean
value is True.
+ // Otherwise, if the Boolean value is False or NULL, the condition
evaluates to False.
val df = statement.buildDataFrame(session)
df.schema.fields match {
case Array(field) if field.dataType == BooleanType =>
df.limit(2).collect() match {
case Array(row) =>
- if (row.isNullAt(0)) {
- throw SqlScriptingErrors.booleanStatementWithEmptyRow(
- statement.origin, statement.getText)
- }
- row.getBoolean(0)
+ if (row.isNullAt(0)) false else row.getBoolean(0)
case _ =>
throw SparkException.internalError(
s"Boolean statement ${statement.getText} is invalid. It
returns more than one row.")
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/scripting/SqlScriptingInterpreterSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/scripting/SqlScriptingInterpreterSuite.scala
index 1c0f0ced5d49..7fb5a02aebe4 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/scripting/SqlScriptingInterpreterSuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/scripting/SqlScriptingInterpreterSuite.scala
@@ -601,29 +601,6 @@ class SqlScriptingInterpreterSuite extends QueryTest with
SharedSparkSession {
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 =
"""
@@ -644,32 +621,6 @@ class SqlScriptingInterpreterSuite extends QueryTest with
SharedSparkSession {
)
}
- 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 =
"""
@@ -846,6 +797,8 @@ class SqlScriptingInterpreterSuite extends QueryTest with
SharedSparkSession {
| CASE 1
| WHEN "one" THEN
| SELECT 42;
+ | ELSE
+ | SELECT 43;
| END CASE;
|END
|""".stripMargin
@@ -863,161 +816,8 @@ class SqlScriptingInterpreterSuite extends QueryTest with
SharedSparkSession {
context = ExpectedContext(fragment = "", start = -1, stop = -1))
}
withSQLConf(SQLConf.ANSI_ENABLED.key -> "false") {
- val e = intercept[SqlScriptingException](
- runSqlScript(commands)
- )
- checkError(
- exception = e,
- condition = "BOOLEAN_STATEMENT_WITH_EMPTY_ROW",
- parameters = Map("invalidStatement" -> "(1 = ONE)"))
- assert(e.origin.line.contains(3))
- }
- }
-
- test("simple case with empty query result") {
- withTable("t") {
- val commands =
- """
- |BEGIN
- |CREATE TABLE t (a INT) USING parquet;
- |CASE (SELECT * FROM t)
- | WHEN 1 THEN
- | SELECT 41;
- | WHEN 2 THEN
- | SELECT 42;
- | ELSE
- | SELECT 43;
- | END CASE;
- |END
- |""".stripMargin
-
- val e = intercept[SqlScriptingException] {
- verifySqlScriptResult(commands, Seq.empty)
- }
- checkError(
- exception = e,
- sqlState = "21000",
- condition = "BOOLEAN_STATEMENT_WITH_EMPTY_ROW",
- parameters = Map("invalidStatement" -> "(NULL = 1)")
- )
- assert(e.origin.line.contains(4))
- }
- }
-
- test("simple case with null comparison") {
- withTable("t") {
- val commands =
- """
- |BEGIN
- |CASE 1
- | WHEN NULL THEN
- | SELECT 41;
- | WHEN 2 THEN
- | SELECT 42;
- | ELSE
- | SELECT 43;
- | END CASE;
- |END
- |""".stripMargin
-
- val e = intercept[SqlScriptingException] {
- verifySqlScriptResult(commands, Seq.empty)
- }
- checkError(
- exception = e,
- sqlState = "21000",
- condition = "BOOLEAN_STATEMENT_WITH_EMPTY_ROW",
- parameters = Map("invalidStatement" -> "(1 = NULL)")
- )
- assert(e.origin.line.contains(3))
- }
- }
-
- test("simple case with null comparison 2") {
- withTable("t") {
- val commands =
- """
- |BEGIN
- |CASE NULL
- | WHEN 1 THEN
- | SELECT 41;
- | WHEN 2 THEN
- | SELECT 42;
- | ELSE
- | SELECT 43;
- | END CASE;
- |END
- |""".stripMargin
-
- val e = intercept[SqlScriptingException] {
- verifySqlScriptResult(commands, Seq.empty)
- }
- checkError(
- exception = e,
- sqlState = "21000",
- condition = "BOOLEAN_STATEMENT_WITH_EMPTY_ROW",
- parameters = Map("invalidStatement" -> "(NULL = 1)")
- )
- assert(e.origin.line.contains(3))
- }
- }
-
- test("simple case with multiple columns scalar subquery") {
- val commands =
- """
- |BEGIN
- |CASE (SELECT 1, 2)
- | WHEN 1 THEN
- | SELECT 41;
- | WHEN 2 THEN
- | SELECT 42;
- | ELSE
- | SELECT 43;
- | END CASE;
- |END
- |""".stripMargin
-
- val e = intercept[AnalysisException] {
- verifySqlScriptResult(commands, Seq.empty)
- }
- checkError(
- exception = e,
- sqlState = "42823",
- condition =
"INVALID_SUBQUERY_EXPRESSION.SCALAR_SUBQUERY_RETURN_MORE_THAN_ONE_OUTPUT_COLUMN",
- parameters = Map("number" -> "2"),
- context = ExpectedContext(fragment = "(SELECT 1, 2)", start = 12, stop =
24)
- )
- }
-
- test("simple case with multiple rows scalar subquery") {
- withTable("t") {
- val commands =
- """
- |BEGIN
- |CREATE TABLE t (a INT) USING parquet;
- |INSERT INTO t VALUES (1);
- |INSERT INTO t VALUES (1);
- |CASE (SELECT * FROM t)
- | WHEN 1 THEN
- | SELECT 41;
- | WHEN 2 THEN
- | SELECT 42;
- | ELSE
- | SELECT 43;
- | END CASE;
- |END
- |""".stripMargin
-
- val e = intercept[SparkException] {
- verifySqlScriptResult(commands, Seq.empty)
- }
- checkError(
- exception = e,
- sqlState = "21000",
- condition = "SCALAR_SUBQUERY_TOO_MANY_ROWS",
- parameters = Map.empty,
- context = ExpectedContext(fragment = "(SELECT * FROM t)", start = 102,
stop = 118)
- )
+ val expected = Seq(Seq(Row(43)))
+ verifySqlScriptResult(commands, expected)
}
}
@@ -1044,52 +844,6 @@ class SqlScriptingInterpreterSuite extends QueryTest with
SharedSparkSession {
}
}
- test("if's condition must return a single row data") {
- withTable("t1", "t2") {
- // empty row
- val commands1 =
- """
- |BEGIN
- | CREATE TABLE t1 (a BOOLEAN) USING parquet;
- | IF (SELECT * FROM t1) THEN
- | SELECT 46;
- | END IF;
- |END
- |""".stripMargin
- val exception = intercept[SqlScriptingException] {
- runSqlScript(commands1)
- }
- checkError(
- exception = exception,
- condition = "BOOLEAN_STATEMENT_WITH_EMPTY_ROW",
- parameters = Map("invalidStatement" -> "(SELECT * FROM T1)")
- )
- assert(exception.origin.line.isDefined)
- assert(exception.origin.line.get == 4)
-
- // too many rows ( > 1 )
- val commands2 =
- """
- |BEGIN
- | CREATE TABLE t2 (a BOOLEAN) USING parquet;
- | INSERT INTO t2 VALUES (true);
- | INSERT INTO t2 VALUES (true);
- | IF (SELECT * FROM t2) THEN
- | SELECT 46;
- | END IF;
- |END
- |""".stripMargin
- checkError(
- exception = intercept[SparkException] (
- runSqlScript(commands2)
- ),
- condition = "SCALAR_SUBQUERY_TOO_MANY_ROWS",
- parameters = Map.empty,
- context = ExpectedContext(fragment = "(SELECT * FROM t2)", start =
121, stop = 138)
- )
- }
- }
-
test("while") {
val commands =
"""
@@ -1362,57 +1116,6 @@ class SqlScriptingInterpreterSuite extends QueryTest
with SharedSparkSession {
)
}
- test("repeat with empty subquery condition") {
- withTable("t") {
- val commands =
- """
- |BEGIN
- | CREATE TABLE t (a BOOLEAN) USING parquet;
- | REPEAT
- | SELECT 1;
- | UNTIL
- | (SELECT * FROM t)
- | END REPEAT;
- |END
- |""".stripMargin
-
- checkError(
- exception = intercept[SqlScriptingException] (
- runSqlScript(commands)
- ),
- condition = "BOOLEAN_STATEMENT_WITH_EMPTY_ROW",
- parameters = Map("invalidStatement" -> "(SELECT * FROM T)")
- )
- }
- }
-
- test("repeat 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);
- | REPEAT
- | SELECT 1;
- | UNTIL
- | (SELECT * FROM t)
- | END REPEAT;
- |END
- |""".stripMargin
-
- checkError(
- exception = intercept[SparkException] (
- runSqlScript(commands)
- ),
- condition = "SCALAR_SUBQUERY_TOO_MANY_ROWS",
- parameters = Map.empty,
- context = ExpectedContext(fragment = "(SELECT * FROM t)", start = 141,
stop = 157)
- )
- }
- }
-
test("leave compound block") {
val sqlScriptText =
"""
@@ -3137,4 +2840,614 @@ class SqlScriptingInterpreterSuite extends QueryTest
with SharedSparkSession {
verifySqlScriptResult(sqlScript, expected)
}
}
+
+ test("condition evaluation - if statement - scalar exceptions") {
+ val commands1 =
+ """
+ |BEGIN
+ | IF (SELECT 1, 2) THEN
+ | SELECT 1;
+ | END IF;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[AnalysisException] {
+ runSqlScript(commands1)
+ },
+ sqlState = "42823",
+ condition =
"INVALID_SUBQUERY_EXPRESSION.SCALAR_SUBQUERY_RETURN_MORE_THAN_ONE_OUTPUT_COLUMN",
+ parameters = Map("number" -> "2"),
+ context = ExpectedContext(fragment = "(SELECT 1, 2)", start = 12, stop =
24)
+ )
+
+ withTable("t") {
+ val commands2 =
+ """
+ |BEGIN
+ | CREATE TABLE t (a BOOLEAN) USING parquet;
+ | INSERT INTO t VALUES (true), (true);
+ | IF (SELECT * FROM t) THEN
+ | SELECT 46;
+ | END IF;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SparkException] (
+ runSqlScript(commands2)
+ ),
+ condition = "SCALAR_SUBQUERY_TOO_MANY_ROWS",
+ parameters = Map.empty,
+ context = ExpectedContext(fragment = "(SELECT * FROM t)", start = 95,
stop = 111)
+ )
+ }
+ }
+
+ test("condition evaluation - searched case statement - scalar exceptions") {
+ val commands1 =
+ """
+ |BEGIN
+ |CASE
+ | WHEN (SELECT 1, 2) THEN
+ | SELECT 41;
+ | END CASE;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[AnalysisException] (
+ runSqlScript(commands1)
+ ),
+ sqlState = "42823",
+ condition =
"INVALID_SUBQUERY_EXPRESSION.SCALAR_SUBQUERY_RETURN_MORE_THAN_ONE_OUTPUT_COLUMN",
+ parameters = Map("number" -> "2"),
+ context = ExpectedContext(fragment = "(SELECT 1, 2)", start = 18, stop =
30)
+ )
+
+ withTable("t") {
+ val commands2 =
+ """
+ |BEGIN
+ | CREATE TABLE t (a BOOLEAN) USING parquet;
+ | INSERT INTO t VALUES (true), (true);
+ | CASE
+ | WHEN (SELECT * FROM t) THEN
+ | SELECT 1;
+ | END CASE;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SparkException] (
+ runSqlScript(commands2)
+ ),
+ condition = "SCALAR_SUBQUERY_TOO_MANY_ROWS",
+ parameters = Map.empty,
+ context = ExpectedContext(fragment = "(SELECT * FROM t)", start = 102,
stop = 118)
+ )
+ }
+ }
+
+ test("condition evaluation - simple case statement - scalar exceptions") {
+ val commands1 =
+ """
+ |BEGIN
+ |CASE (SELECT 1, 2)
+ | WHEN 1 THEN
+ | SELECT 41;
+ | END CASE;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[AnalysisException] {
+ runSqlScript(commands1)
+ },
+ sqlState = "42823",
+ condition =
"INVALID_SUBQUERY_EXPRESSION.SCALAR_SUBQUERY_RETURN_MORE_THAN_ONE_OUTPUT_COLUMN",
+ parameters = Map("number" -> "2"),
+ context = ExpectedContext(fragment = "(SELECT 1, 2)", start = 12, stop =
24)
+ )
+
+ withTable("t") {
+ val commands2 =
+ """
+ |BEGIN
+ |CREATE TABLE t (a INT) USING parquet;
+ |INSERT INTO t VALUES (1), (1);
+ |CASE (SELECT * FROM t)
+ | WHEN 1 THEN
+ | SELECT 41;
+ | END CASE;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SparkException] {
+ runSqlScript(commands2)
+ },
+ sqlState = "21000",
+ condition = "SCALAR_SUBQUERY_TOO_MANY_ROWS",
+ parameters = Map.empty[String, String],
+ context = ExpectedContext(fragment = "(SELECT * FROM t)", start = 81,
stop = 97)
+ )
+ }
+ }
+
+ test("condition evaluation - while statement - scalar exceptions") {
+ val commands1 =
+ """
+ |BEGIN
+ | WHILE (SELECT 1, 2) DO
+ | SELECT 41;
+ | END WHILE;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[AnalysisException] (
+ runSqlScript(commands1)
+ ),
+ sqlState = "42823",
+ condition =
"INVALID_SUBQUERY_EXPRESSION.SCALAR_SUBQUERY_RETURN_MORE_THAN_ONE_OUTPUT_COLUMN",
+ parameters = Map("number" -> "2"),
+ context = ExpectedContext(fragment = "(SELECT 1, 2)", start = 15, stop =
27)
+ )
+
+ withTable("t") {
+ val commands2 =
+ """
+ |BEGIN
+ | CREATE TABLE t (a BOOLEAN) USING parquet;
+ | INSERT INTO t VALUES (true), (true);
+ | WHILE (SELECT * FROM t) DO
+ | SELECT 1;
+ | END WHILE;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SparkException] (
+ runSqlScript(commands2)
+ ),
+ condition = "SCALAR_SUBQUERY_TOO_MANY_ROWS",
+ parameters = Map.empty,
+ context = ExpectedContext(fragment = "(SELECT * FROM t)", start = 98,
stop = 114)
+ )
+ }
+ }
+
+ test("condition evaluation - repeat statement - scalar exceptions") {
+ val commands1 =
+ """
+ |BEGIN
+ | REPEAT
+ | SELECT 41;
+ | UNTIL (SELECT 1, 2)
+ | END REPEAT;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[AnalysisException] (
+ runSqlScript(commands1)
+ ),
+ sqlState = "42823",
+ condition =
"INVALID_SUBQUERY_EXPRESSION.SCALAR_SUBQUERY_RETURN_MORE_THAN_ONE_OUTPUT_COLUMN",
+ parameters = Map("number" -> "2"),
+ context = ExpectedContext(fragment = "(SELECT 1, 2)", start = 39, stop =
51)
+ )
+
+ withTable("t") {
+ val commands2 =
+ """
+ |BEGIN
+ | CREATE TABLE t (a BOOLEAN) USING parquet;
+ | INSERT INTO t VALUES (true), (true);
+ | REPEAT
+ | SELECT 1;
+ | UNTIL (SELECT * FROM t)
+ | END REPEAT;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SparkException] (
+ runSqlScript(commands2)
+ ),
+ condition = "SCALAR_SUBQUERY_TOO_MANY_ROWS",
+ parameters = Map.empty,
+ context = ExpectedContext(fragment = "(SELECT * FROM t)", start = 121,
stop = 137)
+ )
+ }
+ }
+
+ test("condition evaluation - if statement - null boolean constant") {
+ val commands =
+ """
+ |BEGIN
+ | IF (NULL::BOOLEAN) THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END IF;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(43)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - if statement - null non-boolean constant") {
+ val commands =
+ """
+ |BEGIN
+ | IF NULL THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END IF;
+ |END
+ |""".stripMargin
+ checkError(
+ exception = intercept[SqlScriptingException] (
+ runSqlScript(commands)
+ ),
+ condition = "INVALID_BOOLEAN_STATEMENT",
+ parameters = Map("invalidStatement" -> "NULL")
+ )
+ }
+
+ test("condition evaluation - searched case statement - null boolean
constant") {
+ val commands =
+ """
+ |BEGIN
+ | CASE
+ | WHEN (NULL::BOOLEAN) THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(43)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - simple case statement - null boolean constant
1") {
+ val commands =
+ """
+ | BEGIN
+ | CASE (NULL::BOOLEAN)
+ | WHEN NULL::BOOLEAN THEN
+ | SELECT 41;
+ | WHEN true THEN
+ | SELECT 42;
+ | WHEN false THEN
+ | SELECT 43;
+ | ELSE
+ | SELECT 44;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(44)))
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - simple case statement - null boolean constant
2") {
+ val commands =
+ """
+ |BEGIN
+ | CASE true
+ | WHEN (NULL::BOOLEAN) THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(Seq(Row(43)))
+ verifySqlScriptResult(commands, expected)
+
+ val commands2 =
+ """
+ |BEGIN
+ | CASE false
+ | WHEN (NULL::BOOLEAN) THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected2 = Seq(Seq(Row(43)))
+ verifySqlScriptResult(commands2, expected2)
+ }
+
+ test("condition evaluation - while statement - null boolean constant") {
+ val commands =
+ """
+ |BEGIN
+ | WHILE (NULL::BOOLEAN) DO
+ | SELECT 42;
+ | END WHILE;
+ |END
+ |""".stripMargin
+ val expected = Seq.empty[Seq[Row]]
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - repeat statement - null boolean constant") {
+ val commands =
+ """
+ |BEGIN
+ | DECLARE cnt INT = 0;
+ | rlbl: REPEAT
+ | SELECT 1;
+ | IF cnt = 1 THEN
+ | LEAVE rlbl;
+ | END IF;
+ | SET cnt = cnt + 1;
+ | UNTIL
+ | (NULL::BOOLEAN)
+ | END REPEAT;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // declare
+ Seq(Row(1)), // select
+ Seq.empty[Row], // set
+ Seq(Row(1)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - if statement - null boolean variable") {
+ val commands =
+ """
+ |BEGIN
+ | DECLARE b BOOLEAN = NULL;
+ | IF b THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END IF;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row], // declare
+ Seq(Row(43)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - searched case statement - null boolean
variable") {
+ val commands =
+ """
+ |BEGIN
+ | DECLARE b BOOLEAN = NULL;
+ | CASE
+ | WHEN b THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row], // declare
+ Seq(Row(43)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - simple case statement - null boolean variable
1") {
+ val commands =
+ """
+ |BEGIN
+ | DECLARE b BOOLEAN = NULL;
+ | CASE b
+ | WHEN true THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row], // declare
+ Seq(Row(43)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - simple case statement - null boolean variable
2") {
+ val commands =
+ """
+ |BEGIN
+ | DECLARE b BOOLEAN = NULL;
+ | CASE true
+ | WHEN b THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row], // declare
+ Seq(Row(43)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - while statement - null boolean variable") {
+ val commands =
+ """
+ |BEGIN
+ | DECLARE b BOOLEAN = NULL;
+ | WHILE b DO
+ | SELECT 42;
+ | END WHILE;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row] // declare
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - repeat statement - null boolean variable") {
+ val commands =
+ """
+ |BEGIN
+ | DECLARE b BOOLEAN = NULL;
+ | DECLARE cnt INT = 0;
+ | rlbl: REPEAT
+ | SELECT 1;
+ | IF cnt = 1 THEN
+ | LEAVE rlbl;
+ | END IF;
+ | SET cnt = cnt + 1;
+ | UNTIL
+ | b
+ | END REPEAT;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // declare
+ Seq.empty[Row], // declare
+ Seq(Row(1)), // select
+ Seq.empty[Row], // set
+ Seq(Row(1)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+
+ test("condition evaluation - if statement - null boolean from table") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | CREATE TABLE t (null BOOLEAN) USING parquet;
+ | IF (SELECT * FROM t) THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END IF;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq(Row(43)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+ }
+
+ test("condition evaluation - searched case statement - null boolean from
table") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | CREATE TABLE t (null BOOLEAN) USING parquet;
+ | CASE
+ | WHEN (SELECT * FROM t) THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq(Row(43)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+ }
+
+ test("condition evaluation - simple case statement - null boolean from table
1") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | CREATE TABLE t (null BOOLEAN) USING parquet;
+ | CASE (SELECT * FROM t)
+ | WHEN true THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq(Row(43)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+ }
+
+ test("condition evaluation - simple case statement - null boolean from table
2") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | CREATE TABLE t (null BOOLEAN) USING parquet;
+ | CASE true
+ | WHEN (SELECT * FROM t) THEN
+ | SELECT 42;
+ | ELSE
+ | SELECT 43;
+ | END CASE;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq(Row(43)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+ }
+
+ test("condition evaluation - while statement - null boolean from table") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | CREATE TABLE t (null BOOLEAN) USING parquet;
+ | WHILE (SELECT * FROM t) DO
+ | SELECT 42;
+ | END WHILE;
+ |END
+ |""".stripMargin
+ val expected = Seq(
+ Seq.empty[Row] // create table
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+ }
+
+ test("condition evaluation - repeat statement - null boolean from table") {
+ withTable("t") {
+ val commands =
+ """
+ |BEGIN
+ | DECLARE cnt INT = 0;
+ | CREATE TABLE t (null BOOLEAN) USING parquet;
+ | rlbl: REPEAT
+ | SELECT 1;
+ | IF cnt = 1 THEN
+ | LEAVE rlbl;
+ | END IF;
+ | SET cnt = cnt + 1;
+ | UNTIL
+ | (SELECT * FROM t)
+ | END REPEAT;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // declare
+ Seq.empty[Row], // create table
+ Seq(Row(1)), // select
+ Seq.empty[Row], // set
+ Seq(Row(1)) // select
+ )
+ verifySqlScriptResult(commands, expected)
+ }
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
