dusantism-db commented on code in PR #48794:
URL: https://github.com/apache/spark/pull/48794#discussion_r1858462672
##########
sql/core/src/test/scala/org/apache/spark/sql/scripting/SqlScriptingInterpreterSuite.scala:
##########
@@ -1547,4 +1547,995 @@ class SqlScriptingInterpreterSuite extends QueryTest
with SharedSparkSession {
)
verifySqlScriptResult(sqlScriptText, expected)
}
+
+ test("for statement - enters body once") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT, stringCol STRING, doubleCol DOUBLE)
using parquet;
+ | INSERT INTO t VALUES (1, 'first', 1.0);
+ | FOR row AS SELECT * FROM t DO
+ | SELECT row.intCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(1)), // select row.intCol
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - enters body with multiple statements multiple times") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT, stringCol STRING, doubleCol DOUBLE)
using parquet;
+ | INSERT INTO t VALUES (1, 'first', 1.0);
+ | INSERT INTO t VALUES (2, 'second', 2.0);
+ | FOR row AS SELECT * FROM t ORDER BY intCol DO
+ | SELECT row.intCol;
+ | SELECT intCol;
+ | SELECT row.stringCol;
+ | SELECT stringCol;
+ | SELECT row.doubleCol;
+ | SELECT doubleCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(1)), // select row.intCol
+ Seq(Row(1)), // select intCol
+ Seq(Row("first")), // select row.stringCol
+ Seq(Row("first")), // select stringCol
+ Seq(Row(1.0)), // select row.doubleCol
+ Seq(Row(1.0)), // select doubleCol
+ Seq(Row(2)), // select row.intCol
+ Seq(Row(2)), // select intCol
+ Seq(Row("second")), // select row.stringCol
+ Seq(Row("second")), // select stringCol
+ Seq(Row(2.0)), // select row.doubleCol
+ Seq(Row(2.0)), // select doubleCol
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - map, struct, array") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (int_column INT, map_column MAP<STRING, INT>,
+ | struct_column STRUCT<name: STRING, age: INT>, array_column
ARRAY<STRING>);
+ | INSERT INTO t VALUES
+ | (1, MAP('a', 1), STRUCT('John', 25), ARRAY('apricot', 'quince')),
+ | (2, MAP('b', 2), STRUCT('Jane', 30), ARRAY('plum', 'pear'));
+ | FOR row AS SELECT * FROM t ORDER BY int_column DO
+ | SELECT row.map_column;
+ | SELECT map_column;
+ | SELECT row.struct_column;
+ | SELECT struct_column;
+ | SELECT row.array_column;
+ | SELECT array_column;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(Map("a" -> 1))), // select row.map_column
+ Seq(Row(Map("a" -> 1))), // select map_column
+ Seq(Row(Row("John", 25))), // select row.struct_column
+ Seq(Row(Row("John", 25))), // select struct_column
+ Seq(Row(Array("apricot", "quince"))), // select row.array_column
+ Seq(Row(Array("apricot", "quince"))), // select array_column
+ Seq(Row(Map("b" -> 2))), // select row.map_column
+ Seq(Row(Map("b" -> 2))), // select map_column
+ Seq(Row(Row("Jane", 30))), // select row.struct_column
+ Seq(Row(Row("Jane", 30))), // select struct_column
+ Seq(Row(Array("plum", "pear"))), // select row.array_column
+ Seq(Row(Array("plum", "pear"))), // select array_column
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - nested struct") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t
+ | (int_column INT,
+ | struct_column STRUCT<num: INT, struct2: STRUCT<struct3:
STRUCT<name: STRING>>>);
+ | INSERT INTO t VALUES
+ | (1, STRUCT(1, STRUCT(STRUCT("one")))),
+ | (2, STRUCT(2, STRUCT(STRUCT("two"))));
+ | FOR row AS SELECT * FROM t ORDER BY int_column DO
+ | SELECT row.struct_column;
+ | SELECT struct_column;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(Row(1, Row(Row("one"))))), // select row.struct_column
+ Seq(Row(Row(1, Row(Row("one"))))), // select struct_column
+ Seq(Row(Row(2, Row(Row("two"))))), // select row.struct_column
+ Seq(Row(Row(2, Row(Row("two"))))), // select struct_column
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - nested map") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (int_column INT, map_column MAP<STRING, MAP<INT,
MAP<BOOLEAN, INT>>>);
+ | INSERT INTO t VALUES
+ | (1, MAP('a', MAP(1, MAP(false, 10)))),
+ | (2, MAP('b', MAP(2, MAP(true, 20))));
+ | FOR row AS SELECT * FROM t ORDER BY int_column DO
+ | SELECT row.map_column;
+ | SELECT map_column;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(Map("a" -> Map(1 -> Map(false -> 10))))), // select
row.map_column
+ Seq(Row(Map("a" -> Map(1 -> Map(false -> 10))))), // select map_column
+ Seq(Row(Map("b" -> Map(2 -> Map(true -> 20))))), // select
row.map_column
+ Seq(Row(Map("b" -> Map(2 -> Map(true -> 20))))), // select map_column
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - nested array") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t
+ | (int_column INT, array_column ARRAY<ARRAY<ARRAY<INT>>>);
+ | INSERT INTO t VALUES
+ | (1, ARRAY(ARRAY(ARRAY(1, 2), ARRAY(3, 4)), ARRAY(ARRAY(5, 6)))),
+ | (2, ARRAY(ARRAY(ARRAY(7, 8), ARRAY(9, 10)), ARRAY(ARRAY(11,
12))));
+ | FOR row AS SELECT * FROM t ORDER BY int_column DO
+ | SELECT row.array_column;
+ | SELECT array_column;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(Seq(Seq(Seq(1, 2), Seq(3, 4)), Seq(Seq(5, 6))))), //
row.array_column
+ Seq(Row(Seq(Seq(Seq(1, 2), Seq(3, 4)), Seq(Seq(5, 6))))), //
array_column
+ Seq(Row(Array(Seq(Seq(7, 8), Seq(9, 10)), Seq(Seq(11, 12))))), //
row.array_column
+ Seq(Row(Array(Seq(Seq(7, 8), Seq(9, 10)), Seq(Seq(11, 12))))), //
array_column
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement empty result") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | FOR row AS SELECT * FROM t ORDER BY intCol DO
+ | SELECT row.intCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row] // create table
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement iterate") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT, stringCol STRING) using parquet;
+ | INSERT INTO t VALUES (1, 'first'), (2, 'second'), (3, 'third'),
(4, 'fourth');
+ |
+ | lbl: FOR x AS SELECT * FROM t ORDER BY intCol DO
+ | IF x.intCol = 2 THEN
+ | ITERATE lbl;
+ | END IF;
+ | SELECT stringCol;
+ | SELECT x.stringCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row("first")), // select stringCol
+ Seq(Row("first")), // select x.stringCol
+ Seq(Row("third")), // select stringCol
+ Seq(Row("third")), // select x.stringCol
+ Seq(Row("fourth")), // select stringCol
+ Seq(Row("fourth")), // select x.stringCol
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement leave") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT, stringCol STRING) using parquet;
+ | INSERT INTO t VALUES (1, 'first'), (2, 'second'), (3, 'third'),
(4, 'fourth');
+ |
+ | lbl: FOR x AS SELECT * FROM t ORDER BY intCol DO
+ | IF x.intCol = 3 THEN
+ | LEAVE lbl;
+ | END IF;
+ | SELECT stringCol;
+ | SELECT x.stringCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row("first")), // select stringCol
+ Seq(Row("first")), // select x.stringCol
+ Seq(Row("second")), // select stringCol
+ Seq(Row("second")) // select x.stringCol
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - nested - in while") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE cnt = 0;
+ | CREATE TABLE t (intCol INT) using parquet;
+ | INSERT INTO t VALUES (0);
+ | WHILE cnt < 2 DO
+ | SET cnt = cnt + 1;
+ | FOR x AS SELECT * FROM t ORDER BY intCol DO
+ | SELECT x.intCol;
+ | END FOR;
+ | INSERT INTO t VALUES (cnt);
+ | END WHILE;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // declare cnt
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // set cnt
+ Seq(Row(0)), // select intCol
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // set cnt
+ Seq(Row(0)), // select intCol
+ Seq(Row(1)), // select intCol
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop cnt
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - nested - in other for") {
+ withTable("t", "t2") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | CREATE TABLE t2 (intCol2 INT) using parquet;
+ | INSERT INTO t VALUES (0), (1);
+ | INSERT INTO t2 VALUES (2), (3);
+ | FOR x as SELECT * FROM t ORDER BY intCol DO
+ | FOR y AS SELECT * FROM t2 ORDER BY intCol2 DESC DO
+ | SELECT x.intCol;
+ | SELECT intCol;
+ | SELECT y.intCol2;
+ | SELECT intCol2;
+ | END FOR;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(0)), // select x.intCol
+ Seq(Row(0)), // select intCol
+ Seq(Row(3)), // select y.intCol2
+ Seq(Row(3)), // select intCol2
+ Seq(Row(0)), // select x.intCol
+ Seq(Row(0)), // select intCol
+ Seq(Row(2)), // select y.intCol2
+ Seq(Row(2)), // select intCol2
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq(Row(1)), // select x.intCol
+ Seq(Row(1)), // select intCol
+ Seq(Row(3)), // select y.intCol2
+ Seq(Row(3)), // select intCol2
+ Seq(Row(1)), // select x.intCol
+ Seq(Row(1)), // select intCol
+ Seq(Row(2)), // select y.intCol2
+ Seq(Row(2)), // select intCol2
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop outer var
+ Seq.empty[Row] // drop outer var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ // ignored until loops are fixed to support empty bodies
+ ignore("for statement - nested - empty result set") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | REPEAT
+ | FOR x AS SELECT * FROM t ORDER BY intCol DO
+ | SELECT x.intCol;
+ | END FOR;
+ | UNTIL 1 = 1
+ | END REPEAT;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // declare cnt
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // set cnt
+ Seq(Row(0)), // select intCol
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // set cnt
+ Seq(Row(0)), // select intCol
+ Seq(Row(1)), // select intCol
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop cnt
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - nested - iterate outer loop") {
+ withTable("t", "t2") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | CREATE TABLE t2 (intCol2 INT) using parquet;
+ | INSERT INTO t VALUES (0), (1);
+ | INSERT INTO t2 VALUES (2), (3);
+ | lbl1: FOR x as SELECT * FROM t ORDER BY intCol DO
+ | lbl2: FOR y AS SELECT * FROM t2 ORDER BY intCol2 DESC DO
+ | SELECT y.intCol2;
+ | SELECT intCol2;
+ | ITERATE lbl1;
+ | SELECT 1;
+ | END FOR;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(3)), // select y.intCol2
+ Seq(Row(3)), // select intCol2
+ Seq(Row(3)), // select y.intCol2
+ Seq(Row(3)), // select intCol2
+ Seq.empty[Row], // drop outer var
+ Seq.empty[Row] // drop outer var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - nested - leave outer loop") {
+ withTable("t", "t2") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | CREATE TABLE t2 (intCol2 INT) using parquet;
+ | INSERT INTO t VALUES (0), (1);
+ | INSERT INTO t2 VALUES (2), (3);
+ | lbl1: FOR x as SELECT * FROM t ORDER BY intCol DO
+ | lbl2: FOR y AS SELECT * FROM t2 ORDER BY intCol2 DESC DO
+ | SELECT y.intCol2;
+ | SELECT intCol2;
+ | LEAVE lbl1;
+ | SELECT 1;
+ | END FOR;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(3)), // select y.intCol2
+ Seq(Row(3)) // select intCol2
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - nested - leave inner loop") {
+ withTable("t", "t2") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | CREATE TABLE t2 (intCol2 INT) using parquet;
+ | INSERT INTO t VALUES (0), (1);
+ | INSERT INTO t2 VALUES (2), (3);
+ | lbl1: FOR x as SELECT * FROM t ORDER BY intCol DO
+ | lbl2: FOR y AS SELECT * FROM t2 ORDER BY intCol2 DESC DO
+ | SELECT y.intCol2;
+ | SELECT intCol2;
+ | LEAVE lbl2;
+ | SELECT 1;
+ | END FOR;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(3)), // select y.intCol2
+ Seq(Row(3)), // select intCol2
+ Seq(Row(3)), // select y.intCol2
+ Seq(Row(3)), // select intCol2
+ Seq.empty[Row], // drop outer var
+ Seq.empty[Row] // drop outer var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - enters body once") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT, stringCol STRING, doubleCol DOUBLE)
using parquet;
+ | INSERT INTO t VALUES (1, 'first', 1.0);
+ | FOR SELECT * FROM t DO
+ | SELECT intCol;
+ | SELECT stringCol;
+ | SELECT doubleCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(1)), // select intCol
+ Seq(Row("first")), // select stringCol
+ Seq(Row(1.0)), // select doubleCol
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - enters body with multiple statements
multiple times") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT, stringCol STRING, doubleCol DOUBLE)
using parquet;
+ | INSERT INTO t VALUES (1, 'first', 1.0);
+ | INSERT INTO t VALUES (2, 'second', 2.0);
+ | FOR SELECT * FROM t ORDER BY intCol DO
+ | SELECT intCol;
+ | SELECT stringCol;
+ | SELECT doubleCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(1)), // select intCol
+ Seq(Row("first")), // select stringCol
+ Seq(Row(1.0)), // select doubleCol
+ Seq(Row(2)), // select intCol
+ Seq(Row("second")), // select stringCol
+ Seq(Row(2.0)), // select doubleCol
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - map, struct, array") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (int_column INT, map_column MAP<STRING, INT>,
+ | struct_column STRUCT<name: STRING, age: INT>, array_column
ARRAY<STRING>);
+ | INSERT INTO t VALUES
+ | (1, MAP('a', 1), STRUCT('John', 25), ARRAY('apricot', 'quince')),
+ | (2, MAP('b', 2), STRUCT('Jane', 30), ARRAY('plum', 'pear'));
+ | FOR SELECT * FROM t ORDER BY int_column DO
+ | SELECT map_column;
+ | SELECT struct_column;
+ | SELECT array_column;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(Map("a" -> 1))), // select map_column
+ Seq(Row(Row("John", 25))), // select struct_column
+ Seq(Row(Array("apricot", "quince"))), // select array_column
+ Seq(Row(Map("b" -> 2))), // select map_column
+ Seq(Row(Row("Jane", 30))), // select struct_column
+ Seq(Row(Array("plum", "pear"))), // select array_column
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - nested struct") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (int_column INT,
+ | struct_column STRUCT<num: INT, struct2: STRUCT<struct3:
STRUCT<name: STRING>>>);
+ | INSERT INTO t VALUES
+ | (1, STRUCT(1, STRUCT(STRUCT("one")))),
+ | (2, STRUCT(2, STRUCT(STRUCT("two"))));
+ | FOR SELECT * FROM t ORDER BY int_column DO
+ | SELECT struct_column;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(Row(1, Row(Row("one"))))), // select struct_column
+ Seq(Row(Row(2, Row(Row("two"))))), // select struct_column
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - nested map") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (int_column INT, map_column MAP<STRING, MAP<INT,
MAP<BOOLEAN, INT>>>);
+ | INSERT INTO t VALUES
+ | (1, MAP('a', MAP(1, MAP(false, 10)))),
+ | (2, MAP('b', MAP(2, MAP(true, 20))));
+ | FOR SELECT * FROM t ORDER BY int_column DO
+ | SELECT map_column;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(Map("a" -> Map(1 -> Map(false -> 10))))), // select map_column
+ Seq(Row(Map("b" -> Map(2 -> Map(true -> 20))))), // select map_column
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - nested array") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t
+ | (int_column INT, array_column ARRAY<ARRAY<ARRAY<INT>>>);
+ | INSERT INTO t VALUES
+ | (1, ARRAY(ARRAY(ARRAY(1, 2), ARRAY(3, 4)), ARRAY(ARRAY(5, 6)))),
+ | (2, ARRAY(ARRAY(ARRAY(7, 8), ARRAY(9, 10)), ARRAY(ARRAY(11,
12))));
+ | FOR SELECT * FROM t ORDER BY int_column DO
+ | SELECT array_column;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row(Seq(Seq(Seq(1, 2), Seq(3, 4)), Seq(Seq(5, 6))))), //
array_column
+ Seq(Row(Array(Seq(Seq(7, 8), Seq(9, 10)), Seq(Seq(11, 12))))), //
array_column
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - empty result") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | FOR SELECT * FROM t ORDER BY intCol DO
+ | SELECT intCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row] // create table
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - iterate") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT, stringCol STRING) using parquet;
+ | INSERT INTO t VALUES (1, 'first'), (2, 'second'), (3, 'third'),
(4, 'fourth');
+ |
+ | lbl: FOR SELECT * FROM t ORDER BY intCol DO
+ | IF intCol = 2 THEN
+ | ITERATE lbl;
+ | END IF;
+ | SELECT stringCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row("first")), // select stringCol
+ Seq(Row("third")), // select stringCol
+ Seq(Row("fourth")), // select stringCol
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop local var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - leave") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT, stringCol STRING) using parquet;
+ | INSERT INTO t VALUES (1, 'first'), (2, 'second'), (3, 'third'),
(4, 'fourth');
+ |
+ | lbl: FOR SELECT * FROM t ORDER BY intCol DO
+ | IF intCol = 3 THEN
+ | LEAVE lbl;
+ | END IF;
+ | SELECT stringCol;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq(Row("first")), // select stringCol
+ Seq(Row("second")) // select stringCol
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - nested - in while") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | DECLARE cnt = 0;
+ | CREATE TABLE t (intCol INT) using parquet;
+ | INSERT INTO t VALUES (0);
+ | WHILE cnt < 2 DO
+ | SET cnt = cnt + 1;
+ | FOR SELECT * FROM t ORDER BY intCol DO
+ | SELECT intCol;
+ | END FOR;
+ | INSERT INTO t VALUES (cnt);
+ | END WHILE;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // declare cnt
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // set cnt
+ Seq(Row(0)), // select intCol
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // set cnt
+ Seq(Row(0)), // select intCol
+ Seq(Row(1)), // select intCol
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop cnt
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - nested - in other for") {
+ withTable("t", "t2") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | CREATE TABLE t2 (intCol2 INT) using parquet;
+ | INSERT INTO t VALUES (0), (1);
+ | INSERT INTO t2 VALUES (2), (3);
+ | FOR SELECT * FROM t ORDER BY intCol DO
+ | FOR SELECT * FROM t2 ORDER BY intCol2 DESC DO
+ | SELECT intCol;
+ | SELECT intCol2;
+ | END FOR;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(0)), // select intCol
+ Seq(Row(3)), // select intCol2
+ Seq(Row(0)), // select intCol
+ Seq(Row(2)), // select intCol2
+ Seq.empty[Row], // drop local var
+ Seq(Row(1)), // select intCol
+ Seq(Row(3)), // select intCol2
+ Seq(Row(1)), // select intCol
+ Seq(Row(2)), // select intCol2
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop outer var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ // ignored until loops are fixed to support empty bodies
+ ignore("for statement - no variable - nested - empty result set") {
+ withTable("t") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | REPEAT
+ | FOR SELECT * FROM t ORDER BY intCol DO
+ | SELECT intCol;
+ | END FOR;
+ | UNTIL 1 = 1
+ | END REPEAT;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // declare cnt
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // set cnt
+ Seq(Row(0)), // select intCol
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row], // set cnt
+ Seq(Row(0)), // select intCol
+ Seq(Row(1)), // select intCol
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // drop local var
+ Seq.empty[Row] // drop cnt
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - nested - iterate outer loop") {
+ withTable("t", "t2") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | CREATE TABLE t2 (intCol2 INT) using parquet;
+ | INSERT INTO t VALUES (0), (1);
+ | INSERT INTO t2 VALUES (2), (3);
+ | lbl1: FOR SELECT * FROM t ORDER BY intCol DO
+ | lbl2: FOR SELECT * FROM t2 ORDER BY intCol2 DESC DO
+ | SELECT intCol2;
+ | ITERATE lbl1;
+ | SELECT 1;
+ | END FOR;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(3)), // select intCol2
+ Seq(Row(3)), // select intCol2
+ Seq.empty[Row] // drop outer var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - nested - leave outer loop") {
+ withTable("t", "t2") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | CREATE TABLE t2 (intCol2 INT) using parquet;
+ | INSERT INTO t VALUES (0), (1);
+ | INSERT INTO t2 VALUES (2), (3);
+ | lbl1: FOR SELECT * FROM t ORDER BY intCol DO
+ | lbl2: FOR SELECT * FROM t2 ORDER BY intCol2 DESC DO
+ | SELECT intCol2;
+ | LEAVE lbl1;
+ | SELECT 1;
+ | END FOR;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(3)) // select intCol2
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
+
+ test("for statement - no variable - nested - leave inner loop") {
+ withTable("t", "t2") {
+ val sqlScript =
+ """
+ |BEGIN
+ | CREATE TABLE t (intCol INT) using parquet;
+ | CREATE TABLE t2 (intCol2 INT) using parquet;
+ | INSERT INTO t VALUES (0), (1);
+ | INSERT INTO t2 VALUES (2), (3);
+ | lbl1: FOR SELECT * FROM t ORDER BY intCol DO
+ | lbl2: FOR SELECT * FROM t2 ORDER BY intCol2 DESC DO
+ | SELECT intCol2;
+ | LEAVE lbl2;
+ | SELECT 1;
+ | END FOR;
+ | END FOR;
+ |END
+ |""".stripMargin
+
+ val expected = Seq(
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // create table
+ Seq.empty[Row], // insert
+ Seq.empty[Row], // insert
+ Seq(Row(3)), // select intCol2
+ Seq(Row(3)), // select intCol2
+ Seq.empty[Row] // drop outer var
+ )
+ verifySqlScriptResult(sqlScript, expected)
+ }
+ }
}
Review Comment:
done
--
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]
