[SPARK-18873][SQL][TEST] New test cases for scalar subquery (part 2 of 2) - scalar subquery in predicate context

## What changes were proposed in this pull request?
This PR adds new test cases for scalar subquery in predicate context

## How was this patch tested?
The test result is compared with the result run from another SQL engine (in this case is IBM DB2). If the result are equivalent, we assume the result is correct.

Author: Nattavut Sutyanyong <nsy.can@gmail.com>

Closes #16798 from nsyca/18873-2.

1 files changed, 430 insertions, 0 deletions

diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
new file mode 100644
index 0000000000..8b29300e71
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
@@ -0,0 +1,430 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 26
+
+
+-- !query 0
+CREATE OR REPLACE TEMPORARY VIEW p AS VALUES (1, 1) AS T(pk, pv)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+CREATE OR REPLACE TEMPORARY VIEW c AS VALUES (1, 1) AS T(ck, cv)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+SELECT pk, cv
+FROM   p, c
+WHERE  p.pk = c.ck
+AND    c.cv = (SELECT avg(c1.cv)
+               FROM   c c1
+               WHERE  c1.ck = p.pk)
+-- !query 2 schema
+struct<pk:int,cv:int>
+-- !query 2 output
+1	1
+
+
+-- !query 3
+SELECT pk, cv
+FROM   p, c
+WHERE  p.pk = c.ck
+AND    c.cv = (SELECT max(avg)
+               FROM   (SELECT   c1.cv, avg(c1.cv) avg
+                       FROM     c c1
+                       WHERE    c1.ck = p.pk
+                       GROUP BY c1.cv))
+-- !query 3 schema
+struct<pk:int,cv:int>
+-- !query 3 output
+1	1
+
+
+-- !query 4
+create temporary view t1 as select * from values
+  ('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 00:00:00.000', date '2014-04-04'),
+  ('val1b', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ('val1a', 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
+  ('val1a', 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ('val1c', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
+  ('val1d', null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
+  ('val1d', null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
+  ('val1e', 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
+  ('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
+  ('val1d', 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
+  ('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
+  as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
+-- !query 4 schema
+struct<>
+-- !query 4 output
+
+
+
+-- !query 5
+create temporary view t2 as select * from values
+  ('val2a', 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
+  ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ('val1b', 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
+  ('val1c', 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
+  ('val1b', null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
+  ('val2e', 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ('val1f', 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
+  ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
+  ('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
+  ('val1c', 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
+  ('val1e', 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
+  ('val1f', 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
+  ('val1b', null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
+  as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
+-- !query 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+create temporary view t3 as select * from values
+  ('val3a', 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
+  ('val3a', 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ('val1b', 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ('val1b', 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
+  ('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
+  ('val3c', 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
+  ('val3c', 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
+  ('val1b', null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
+  ('val1b', null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
+  ('val3b', 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
+  ('val3b', 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
+  as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
+-- !query 6 schema
+struct<>
+-- !query 6 output
+
+
+
+-- !query 7
+SELECT t1a, t1b
+FROM   t1
+WHERE  t1c = (SELECT max(t2c)
+              FROM   t2)
+-- !query 7 schema
+struct<t1a:string,t1b:smallint>
+-- !query 7 output
+val1b	8
+val1c	8
+val1d	NULL
+val1d	NULL
+
+
+-- !query 8
+SELECT t1a, t1d, t1f
+FROM   t1
+WHERE  t1c = (SELECT max(t2c)
+              FROM   t2)
+AND    t1b > (SELECT min(t3b)
+              FROM   t3)
+-- !query 8 schema
+struct<t1a:string,t1d:bigint,t1f:double>
+-- !query 8 output
+val1b	19	25.0
+val1c	19	25.0
+
+
+-- !query 9
+SELECT t1a, t1h
+FROM   t1
+WHERE  t1c = (SELECT max(t2c)
+              FROM   t2)
+OR     t1b = (SELECT min(t3b)
+              FROM   t3
+              WHERE  t3b > 10)
+-- !query 9 schema
+struct<t1a:string,t1h:timestamp>
+-- !query 9 output
+val1b	2014-05-04 01:01:00
+val1c	2014-05-04 01:02:00.001
+val1d	2014-06-04 01:01:00
+val1d	2014-07-04 01:02:00.001
+
+
+-- !query 10
+SELECT t1a, t1b, t2d
+FROM   t1 LEFT JOIN t2
+       ON t1a = t2a
+WHERE  t1b = (SELECT min(t3b)
+              FROM   t3)
+-- !query 10 schema
+struct<t1a:string,t1b:smallint,t2d:bigint>
+-- !query 10 output
+val1a	6	NULL
+val1a	6	NULL
+
+
+-- !query 11
+SELECT t1a, t1b, t1g
+FROM   t1
+WHERE  t1c + 5 = (SELECT max(t2e)
+                  FROM   t2)
+-- !query 11 schema
+struct<t1a:string,t1b:smallint,t1g:decimal(2,-2)>
+-- !query 11 output
+val1a	16	2000
+val1a	16	2000
+
+
+-- !query 12
+SELECT t1a, t1h
+FROM   t1
+WHERE  date(t1h) = (SELECT min(t2i)
+                    FROM   t2)
+-- !query 12 schema
+struct<t1a:string,t1h:timestamp>
+-- !query 12 output
+val1a	2014-04-04 00:00:00
+val1a	2014-04-04 01:02:00.001
+
+
+-- !query 13
+SELECT t2d, t1a
+FROM   t1, t2
+WHERE  t1b = t2b
+AND    t2c + 1 = (SELECT max(t2c) + 1
+                  FROM   t2, t1
+                  WHERE  t2b = t1b)
+-- !query 13 schema
+struct<t2d:bigint,t1a:string>
+-- !query 13 output
+119	val1b
+119	val1c
+19	val1b
+19	val1c
+
+
+-- !query 14
+SELECT DISTINCT t2a, max_t1g
+FROM   t2, (SELECT   max(t1g) max_t1g, t1a
+            FROM     t1
+            GROUP BY t1a) t1
+WHERE  t2a = t1a
+AND    max_t1g = (SELECT max(t1g)
+                  FROM   t1)
+-- !query 14 schema
+struct<t2a:string,max_t1g:decimal(2,-2)>
+-- !query 14 output
+val1b	2600
+val1c	2600
+val1e	2600
+
+
+-- !query 15
+SELECT t3b, t3c
+FROM   t3
+WHERE  (SELECT max(t3c)
+        FROM   t3
+        WHERE  t3b > 10) >=
+       (SELECT min(t3b)
+        FROM   t3
+        WHERE  t3c > 0)
+AND    (t3b is null or t3c is null)
+-- !query 15 schema
+struct<t3b:smallint,t3c:int>
+-- !query 15 output
+8	NULL
+8	NULL
+NULL	16
+NULL	16
+
+
+-- !query 16
+SELECT t1a
+FROM   t1
+WHERE  t1a < (SELECT   max(t2a)
+              FROM     t2
+              WHERE    t2c = t1c
+              GROUP BY t2c)
+-- !query 16 schema
+struct<t1a:string>
+-- !query 16 output
+val1a
+val1a
+val1b
+
+
+-- !query 17
+SELECT t1a, t1c
+FROM   t1
+WHERE  (SELECT   max(t2a)
+        FROM     t2
+        WHERE    t2c = t1c
+        GROUP BY t2c) IS NULL
+-- !query 17 schema
+struct<t1a:string,t1c:int>
+-- !query 17 output
+val1a	8
+val1a	8
+val1d	NULL
+val1e	NULL
+val1e	NULL
+val1e	NULL
+
+
+-- !query 18
+SELECT t1a
+FROM   t1
+WHERE  t1a = (SELECT   max(t2a)
+              FROM     t2
+              WHERE    t2c = t1c
+              GROUP BY t2c
+              HAVING   count(*) >= 0)
+OR     t1i > '2014-12-31'
+-- !query 18 schema
+struct<t1a:string>
+-- !query 18 output
+val1c
+val1d
+
+
+-- !query 19
+SELECT count(t1a)
+FROM   t1 RIGHT JOIN t2
+ON     t1d = t2d
+WHERE  t1a < (SELECT   max(t2a)
+              FROM     t2
+              WHERE    t2c = t1c
+              GROUP BY t2c)
+-- !query 19 schema
+struct<count(t1a):bigint>
+-- !query 19 output
+7
+
+
+-- !query 20
+SELECT t1a
+FROM   t1
+WHERE  t1b <= (SELECT   max(t2b)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+AND    t1b >= (SELECT   min(t2b)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+-- !query 20 schema
+struct<t1a:string>
+-- !query 20 output
+val1b
+val1c
+
+
+-- !query 21
+SELECT t1a
+FROM   t1
+WHERE  t1a <= (SELECT   max(t2a)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+INTERSECT
+SELECT t1a
+FROM   t1
+WHERE  t1a >= (SELECT   min(t2a)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+-- !query 21 schema
+struct<t1a:string>
+-- !query 21 output
+val1b
+val1c
+
+
+-- !query 22
+SELECT t1a
+FROM   t1
+WHERE  t1a <= (SELECT   max(t2a)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+UNION ALL
+SELECT t1a
+FROM   t1
+WHERE  t1a >= (SELECT   min(t2a)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+-- !query 22 schema
+struct<t1a:string>
+-- !query 22 output
+val1a
+val1a
+val1b
+val1b
+val1c
+val1c
+val1d
+val1d
+
+
+-- !query 23
+SELECT t1a
+FROM   t1
+WHERE  t1a <= (SELECT   max(t2a)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+UNION DISTINCT
+SELECT t1a
+FROM   t1
+WHERE  t1a >= (SELECT   min(t2a)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+-- !query 23 schema
+struct<t1a:string>
+-- !query 23 output
+val1a
+val1b
+val1c
+val1d
+
+
+-- !query 24
+SELECT t1a
+FROM   t1
+WHERE  t1a <= (SELECT   max(t2a)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+MINUS
+SELECT t1a
+FROM   t1
+WHERE  t1a >= (SELECT   min(t2a)
+               FROM     t2
+               WHERE    t2c = t1c
+               GROUP BY t2c)
+-- !query 24 schema
+struct<t1a:string>
+-- !query 24 output
+val1a
+
+
+-- !query 25
+SELECT   t1a
+FROM     t1
+GROUP BY t1a, t1c
+HAVING   max(t1b) <= (SELECT   max(t2b)
+                      FROM     t2
+                      WHERE    t2c = t1c
+                      GROUP BY t2c)
+-- !query 25 schema
+struct<t1a:string>
+-- !query 25 output
+val1b
+val1c