[SPARK-18873][SQL][TEST] New test cases for scalar subquery (part 1 of 2) - scalar subquery in SELECT clause

## What changes were proposed in this pull request?
This PR adds new test cases for scalar subquery in SELECT clause.

## 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 #16712 from nsyca/18873.

1 files changed, 198 insertions, 0 deletions

diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-select.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-select.sql.out
new file mode 100644
index 0000000000..807bb47221
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-select.sql.out
@@ -0,0 +1,198 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 11
+
+
+-- !query 0
+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 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+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 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+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 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT (SELECT min(t3d) FROM t3) min_t3d,
+       (SELECT max(t2h) FROM t2) max_t2h
+FROM   t1
+WHERE  t1a = 'val1c'
+-- !query 3 schema
+struct<min_t3d:bigint,max_t2h:timestamp>
+-- !query 3 output
+10	2017-05-04 01:01:00
+
+
+-- !query 4
+SELECT   t1a, count(*)
+FROM     t1
+WHERE    t1c IN (SELECT   (SELECT min(t3c) FROM t3)
+                 FROM     t2
+                 GROUP BY t2g
+                 HAVING   count(*) > 1)
+GROUP BY t1a
+-- !query 4 schema
+struct<t1a:string,count(1):bigint>
+-- !query 4 output
+val1a	2
+
+
+-- !query 5
+SELECT (SELECT min(t3d) FROM t3) min_t3d,
+       null
+FROM   t1
+WHERE  t1a = 'val1c'
+UNION
+SELECT null,
+       (SELECT max(t2h) FROM t2) max_t2h
+FROM   t1
+WHERE  t1a = 'val1c'
+-- !query 5 schema
+struct<min_t3d:bigint,NULL:timestamp>
+-- !query 5 output
+10	NULL
+NULL	2017-05-04 01:01:00
+
+
+-- !query 6
+SELECT (SELECT min(t3c) FROM t3) min_t3d
+FROM   t1
+WHERE  t1a = 'val1a'
+INTERSECT
+SELECT (SELECT min(t2c) FROM t2) min_t2d
+FROM   t1
+WHERE  t1a = 'val1d'
+-- !query 6 schema
+struct<min_t3d:int>
+-- !query 6 output
+12
+
+
+-- !query 7
+SELECT q1.t1a, q2.t2a, q1.min_t3d, q2.avg_t3d
+FROM   (SELECT t1a, (SELECT min(t3d) FROM t3) min_t3d
+        FROM   t1
+        WHERE  t1a IN ('val1e', 'val1c')) q1
+       FULL OUTER JOIN
+       (SELECT t2a, (SELECT avg(t3d) FROM t3) avg_t3d
+        FROM   t2
+        WHERE  t2a IN ('val1c', 'val2a')) q2
+ON     q1.t1a = q2.t2a
+AND    q1.min_t3d < q2.avg_t3d
+-- !query 7 schema
+struct<t1a:string,t2a:string,min_t3d:bigint,avg_t3d:double>
+-- !query 7 output
+NULL	val2a	NULL	200.83333333333334
+val1c	val1c	10	200.83333333333334
+val1c	val1c	10	200.83333333333334
+val1e	NULL	10	NULL
+val1e	NULL	10	NULL
+val1e	NULL	10	NULL
+
+
+-- !query 8
+SELECT (SELECT min(t3d) FROM t3 WHERE t3.t3a = t1.t1a) min_t3d,
+       (SELECT max(t2h) FROM t2 WHERE t2.t2a = t1.t1a) max_t2h
+FROM   t1
+WHERE  t1a = 'val1b'
+-- !query 8 schema
+struct<min_t3d:bigint,max_t2h:timestamp>
+-- !query 8 output
+19	2017-05-04 01:01:00
+
+
+-- !query 9
+SELECT (SELECT min(t3d) FROM t3 WHERE t3a = t1a) min_t3d
+FROM   t1
+WHERE  t1a = 'val1b'
+MINUS
+SELECT (SELECT min(t3d) FROM t3) abs_min_t3d
+FROM   t1
+WHERE  t1a = 'val1b'
+-- !query 9 schema
+struct<min_t3d:bigint>
+-- !query 9 output
+19
+
+
+-- !query 10
+SELECT t1a, t1b
+FROM   t1
+WHERE  NOT EXISTS (SELECT (SELECT max(t2b)
+                           FROM   t2 LEFT JOIN t1
+                           ON     t2a = t1a
+                           WHERE  t2c = t3c) dummy
+                   FROM   t3
+                   WHERE  t3b < (SELECT max(t2b)
+                                 FROM   t2 LEFT JOIN t1
+                                 ON     t2a = t1a
+                                 WHERE  t2c = t3c)
+                   AND    t3a = t1a)
+-- !query 10 schema
+struct<t1a:string,t1b:smallint>
+-- !query 10 output
+val1a	16
+val1a	16
+val1a	6
+val1a	6
+val1c	8
+val1d	10
+val1d	NULL
+val1d	NULL
+val1e	10
+val1e	10
+val1e	10