[SPARK-18872][SQL][TESTS] New test cases for EXISTS subquery (Aggregate, Having, Orderby, Limit)

## What changes were proposed in this pull request?
This PR adds the second set of tests for EXISTS subquery.

File name                        | Brief description
------------------------| -----------------
exists-aggregate.sql              |Tests aggregate expressions in outer query and EXISTS subquery.
exists-having.sql|Tests HAVING clause in subquery.
exists-orderby-limit.sql|Tests EXISTS subquery support with ORDER BY and LIMIT clauses.

DB2 results are attached here as reference :

[exists-aggregate-db2.txt](https://github.com/apache/spark/files/743287/exists-aggregate-db2.txt)
[exists-having-db2.txt](https://github.com/apache/spark/files/743286/exists-having-db2.txt)
[exists-orderby-limit-db2.txt](https://github.com/apache/spark/files/743288/exists-orderby-limit-db2.txt)

##  How the patch was 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: Dilip Biswal <dbiswal@us.ibm.com>

Closes #16760 from dilipbiswal/exists-pr2.

3 files changed, 558 insertions, 0 deletions

diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-aggregate.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-aggregate.sql.out
new file mode 100644
index 0000000000..97f494cc05
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-aggregate.sql.out
@@ -0,0 +1,183 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 11
+
+
+-- !query 0
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+  (100, "emp 1", date "2005-01-01", 100.00D, 10),
+  (100, "emp 1", date "2005-01-01", 100.00D, 10),
+  (200, "emp 2", date "2003-01-01", 200.00D, 10),
+  (300, "emp 3", date "2002-01-01", 300.00D, 20),
+  (400, "emp 4", date "2005-01-01", 400.00D, 30),
+  (500, "emp 5", date "2001-01-01", 400.00D, NULL),
+  (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
+  (700, "emp 7", date "2010-01-01", 400.00D, 100),
+  (800, "emp 8", date "2016-01-01", 150.00D, 70)
+AS EMP(id, emp_name, hiredate, salary, dept_id)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+  (10, "dept 1", "CA"),
+  (20, "dept 2", "NY"),
+  (30, "dept 3", "TX"),
+  (40, "dept 4 - unassigned", "OR"),
+  (50, "dept 5 - unassigned", "NJ"),
+  (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
+  ("emp 1", 10.00D),
+  ("emp 1", 20.00D),
+  ("emp 2", 300.00D),
+  ("emp 2", 100.00D),
+  ("emp 3", 300.00D),
+  ("emp 4", 100.00D),
+  ("emp 5", 1000.00D),
+  ("emp 6 - no dept", 500.00D)
+AS BONUS(emp_name, bonus_amt)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT emp.dept_id, 
+       avg(salary),
+       sum(salary)
+FROM   emp 
+WHERE  EXISTS (SELECT state 
+               FROM   dept 
+               WHERE  dept.dept_id = emp.dept_id) 
+GROUP  BY dept_id
+-- !query 3 schema
+struct<dept_id:int,avg(salary):double,sum(salary):double>
+-- !query 3 output
+10	133.33333333333334	400.0
+20	300.0	300.0
+30	400.0	400.0
+70	150.0	150.0
+
+
+-- !query 4
+SELECT emp_name 
+FROM   emp 
+WHERE  EXISTS (SELECT max(dept.dept_id) a 
+               FROM   dept 
+               WHERE  dept.dept_id = emp.dept_id 
+               GROUP  BY dept.dept_id)
+-- !query 4 schema
+struct<emp_name:string>
+-- !query 4 output
+emp 1
+emp 1
+emp 2
+emp 3
+emp 4
+emp 8
+
+
+-- !query 5
+SELECT count(*) 
+FROM   emp 
+WHERE  EXISTS (SELECT max(dept.dept_id) a 
+               FROM   dept 
+               WHERE  dept.dept_id = emp.dept_id 
+               GROUP  BY dept.dept_id)
+-- !query 5 schema
+struct<count(1):bigint>
+-- !query 5 output
+6
+
+
+-- !query 6
+SELECT * 
+FROM   bonus 
+WHERE  EXISTS (SELECT 1 
+               FROM   emp 
+               WHERE  emp.emp_name = bonus.emp_name 
+                      AND EXISTS (SELECT max(dept.dept_id) 
+                                  FROM   dept 
+                                  WHERE  emp.dept_id = dept.dept_id 
+                                  GROUP  BY dept.dept_id))
+-- !query 6 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 6 output
+emp 1	10.0
+emp 1	20.0
+emp 2	100.0
+emp 2	300.0
+emp 3	300.0
+emp 4	100.0
+
+
+-- !query 7
+SELECT emp.dept_id, 
+       Avg(salary), 
+       Sum(salary) 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT state 
+                   FROM   dept 
+                   WHERE  dept.dept_id = emp.dept_id) 
+GROUP  BY dept_id
+-- !query 7 schema
+struct<dept_id:int,avg(salary):double,sum(salary):double>
+-- !query 7 output
+100	400.0	800.0
+NULL	400.0	400.0
+
+
+-- !query 8
+SELECT emp_name 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT max(dept.dept_id) a 
+                   FROM   dept 
+                   WHERE  dept.dept_id = emp.dept_id 
+                   GROUP  BY dept.dept_id)
+-- !query 8 schema
+struct<emp_name:string>
+-- !query 8 output
+emp 5
+emp 6 - no dept
+emp 7
+
+
+-- !query 9
+SELECT count(*) 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT max(dept.dept_id) a 
+                   FROM   dept 
+                   WHERE  dept.dept_id = emp.dept_id 
+                   GROUP  BY dept.dept_id)
+-- !query 9 schema
+struct<count(1):bigint>
+-- !query 9 output
+3
+
+
+-- !query 10
+SELECT * 
+FROM   bonus 
+WHERE  NOT EXISTS (SELECT 1 
+                   FROM   emp 
+                   WHERE  emp.emp_name = bonus.emp_name 
+                          AND EXISTS (SELECT Max(dept.dept_id) 
+                                      FROM   dept 
+                                      WHERE  emp.dept_id = dept.dept_id 
+                                      GROUP  BY dept.dept_id))
+-- !query 10 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 10 output
+emp 5	1000.0
+emp 6 - no dept	500.0
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-having.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-having.sql.out
new file mode 100644
index 0000000000..de90f5e260
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-having.sql.out
@@ -0,0 +1,153 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 8
+
+
+-- !query 0
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+  (100, "emp 1", date "2005-01-01", 100.00D, 10),
+  (100, "emp 1", date "2005-01-01", 100.00D, 10),
+  (200, "emp 2", date "2003-01-01", 200.00D, 10),
+  (300, "emp 3", date "2002-01-01", 300.00D, 20),
+  (400, "emp 4", date "2005-01-01", 400.00D, 30),
+  (500, "emp 5", date "2001-01-01", 400.00D, NULL),
+  (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
+  (700, "emp 7", date "2010-01-01", 400.00D, 100),
+  (800, "emp 8", date "2016-01-01", 150.00D, 70)
+AS EMP(id, emp_name, hiredate, salary, dept_id)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+  (10, "dept 1", "CA"),
+  (20, "dept 2", "NY"),
+  (30, "dept 3", "TX"),
+  (40, "dept 4 - unassigned", "OR"),
+  (50, "dept 5 - unassigned", "NJ"),
+  (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
+  ("emp 1", 10.00D),
+  ("emp 1", 20.00D),
+  ("emp 2", 300.00D),
+  ("emp 2", 100.00D),
+  ("emp 3", 300.00D),
+  ("emp 4", 100.00D),
+  ("emp 5", 1000.00D),
+  ("emp 6 - no dept", 500.00D)
+AS BONUS(emp_name, bonus_amt)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT dept_id, count(*) 
+FROM   emp 
+GROUP  BY dept_id 
+HAVING EXISTS (SELECT 1 
+               FROM   bonus 
+               WHERE  bonus_amt < min(emp.salary))
+-- !query 3 schema
+struct<dept_id:int,count(1):bigint>
+-- !query 3 output
+10	3
+100	2
+20	1
+30	1
+70	1
+NULL	1
+
+
+-- !query 4
+SELECT * 
+FROM   dept 
+WHERE  EXISTS (SELECT dept_id, 
+                      Count(*) 
+               FROM   emp 
+               GROUP  BY dept_id 
+               HAVING EXISTS (SELECT 1 
+                              FROM   bonus 
+                              WHERE bonus_amt < Min(emp.salary)))
+-- !query 4 schema
+struct<dept_id:int,dept_name:string,state:string>
+-- !query 4 output
+10	dept 1	CA
+20	dept 2	NY
+30	dept 3	TX
+40	dept 4 - unassigned	OR
+50	dept 5 - unassigned	NJ
+70	dept 7	FL
+
+
+-- !query 5
+SELECT dept_id, 
+       Max(salary) 
+FROM   emp gp 
+WHERE  EXISTS (SELECT dept_id, 
+                      Count(*) 
+               FROM   emp p
+               GROUP  BY dept_id 
+               HAVING EXISTS (SELECT 1 
+                              FROM   bonus 
+                              WHERE  bonus_amt < Min(p.salary))) 
+GROUP  BY gp.dept_id
+-- !query 5 schema
+struct<dept_id:int,max(salary):double>
+-- !query 5 output
+10	200.0
+100	400.0
+20	300.0
+30	400.0
+70	150.0
+NULL	400.0
+
+
+-- !query 6
+SELECT * 
+FROM   dept 
+WHERE  EXISTS (SELECT dept_id, 
+                        Count(*) 
+                 FROM   emp 
+                 GROUP  BY dept_id 
+                 HAVING EXISTS (SELECT 1 
+                                FROM   bonus 
+                                WHERE  bonus_amt > Min(emp.salary)))
+-- !query 6 schema
+struct<dept_id:int,dept_name:string,state:string>
+-- !query 6 output
+10	dept 1	CA
+20	dept 2	NY
+30	dept 3	TX
+40	dept 4 - unassigned	OR
+50	dept 5 - unassigned	NJ
+70	dept 7	FL
+
+
+-- !query 7
+SELECT * 
+FROM   dept 
+WHERE  EXISTS (SELECT dept_id, 
+                      count(emp.dept_id)
+               FROM   emp 
+               WHERE  dept.dept_id = dept_id 
+               GROUP  BY dept_id 
+               HAVING EXISTS (SELECT 1 
+                              FROM   bonus 
+                              WHERE  ( bonus_amt > min(emp.salary) 
+                                       AND count(emp.dept_id) > 1 )))
+-- !query 7 schema
+struct<dept_id:int,dept_name:string,state:string>
+-- !query 7 output
+10	dept 1	CA
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-orderby-limit.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-orderby-limit.sql.out
new file mode 100644
index 0000000000..ee13ff2c4f
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-orderby-limit.sql.out
@@ -0,0 +1,222 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 12
+
+
+-- !query 0
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+  (100, "emp 1", date "2005-01-01", 100.00D, 10),
+  (100, "emp 1", date "2005-01-01", 100.00D, 10),
+  (200, "emp 2", date "2003-01-01", 200.00D, 10),
+  (300, "emp 3", date "2002-01-01", 300.00D, 20),
+  (400, "emp 4", date "2005-01-01", 400.00D, 30),
+  (500, "emp 5", date "2001-01-01", 400.00D, NULL),
+  (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
+  (700, "emp 7", date "2010-01-01", 400.00D, 100),
+  (800, "emp 8", date "2016-01-01", 150.00D, 70)
+AS EMP(id, emp_name, hiredate, salary, dept_id)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+  (10, "dept 1", "CA"),
+  (20, "dept 2", "NY"),
+  (30, "dept 3", "TX"),
+  (40, "dept 4 - unassigned", "OR"),
+  (50, "dept 5 - unassigned", "NJ"),
+  (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
+  ("emp 1", 10.00D),
+  ("emp 1", 20.00D),
+  ("emp 2", 300.00D),
+  ("emp 2", 100.00D),
+  ("emp 3", 300.00D),
+  ("emp 4", 100.00D),
+  ("emp 5", 1000.00D),
+  ("emp 6 - no dept", 500.00D)
+AS BONUS(emp_name, bonus_amt)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+SELECT * 
+FROM   emp 
+WHERE  EXISTS (SELECT dept.dept_id 
+               FROM   dept 
+               WHERE  emp.dept_id = dept.dept_id 
+               ORDER  BY state) 
+ORDER  BY hiredate
+-- !query 3 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 3 output
+300	emp 3	2002-01-01	300.0	20
+200	emp 2	2003-01-01	200.0	10
+100	emp 1	2005-01-01	100.0	10
+100	emp 1	2005-01-01	100.0	10
+400	emp 4	2005-01-01	400.0	30
+800	emp 8	2016-01-01	150.0	70
+
+
+-- !query 4
+SELECT id, 
+       hiredate 
+FROM   emp 
+WHERE  EXISTS (SELECT dept.dept_id 
+               FROM   dept 
+               WHERE  emp.dept_id = dept.dept_id 
+               ORDER  BY state) 
+ORDER  BY hiredate DESC
+-- !query 4 schema
+struct<id:int,hiredate:date>
+-- !query 4 output
+800	2016-01-01
+100	2005-01-01
+100	2005-01-01
+400	2005-01-01
+200	2003-01-01
+300	2002-01-01
+
+
+-- !query 5
+SELECT * 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT dept.dept_id 
+                   FROM   dept 
+                   WHERE  emp.dept_id = dept.dept_id 
+                   ORDER  BY state) 
+ORDER  BY hiredate
+-- !query 5 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 5 output
+500	emp 5	2001-01-01	400.0	NULL
+600	emp 6 - no dept	2001-01-01	400.0	100
+700	emp 7	2010-01-01	400.0	100
+
+
+-- !query 6
+SELECT emp_name 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT max(dept.dept_id) a 
+                   FROM   dept 
+                   WHERE  dept.dept_id = emp.dept_id 
+                   GROUP  BY state 
+                   ORDER  BY state)
+-- !query 6 schema
+struct<emp_name:string>
+-- !query 6 output
+emp 5
+emp 6 - no dept
+emp 7
+
+
+-- !query 7
+SELECT count(*) 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT max(dept.dept_id) a 
+                   FROM   dept 
+                   WHERE  dept.dept_id = emp.dept_id 
+                   GROUP  BY dept_id 
+                   ORDER  BY dept_id)
+-- !query 7 schema
+struct<count(1):bigint>
+-- !query 7 output
+3
+
+
+-- !query 8
+SELECT * 
+FROM   emp 
+WHERE  EXISTS (SELECT dept.dept_name 
+               FROM   dept 
+               WHERE  dept.dept_id > 10 
+               LIMIT  1)
+-- !query 8 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 8 output
+100	emp 1	2005-01-01	100.0	10
+100	emp 1	2005-01-01	100.0	10
+200	emp 2	2003-01-01	200.0	10
+300	emp 3	2002-01-01	300.0	20
+400	emp 4	2005-01-01	400.0	30
+500	emp 5	2001-01-01	400.0	NULL
+600	emp 6 - no dept	2001-01-01	400.0	100
+700	emp 7	2010-01-01	400.0	100
+800	emp 8	2016-01-01	150.0	70
+
+
+-- !query 9
+SELECT * 
+FROM   emp 
+WHERE  EXISTS (SELECT max(dept.dept_id) 
+               FROM   dept 
+               GROUP  BY state 
+               LIMIT  1)
+-- !query 9 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 9 output
+100	emp 1	2005-01-01	100.0	10
+100	emp 1	2005-01-01	100.0	10
+200	emp 2	2003-01-01	200.0	10
+300	emp 3	2002-01-01	300.0	20
+400	emp 4	2005-01-01	400.0	30
+500	emp 5	2001-01-01	400.0	NULL
+600	emp 6 - no dept	2001-01-01	400.0	100
+700	emp 7	2010-01-01	400.0	100
+800	emp 8	2016-01-01	150.0	70
+
+
+-- !query 10
+SELECT * 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT dept.dept_name 
+                   FROM   dept 
+                   WHERE  dept.dept_id > 100 
+                   LIMIT  1)
+-- !query 10 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 10 output
+100	emp 1	2005-01-01	100.0	10
+100	emp 1	2005-01-01	100.0	10
+200	emp 2	2003-01-01	200.0	10
+300	emp 3	2002-01-01	300.0	20
+400	emp 4	2005-01-01	400.0	30
+500	emp 5	2001-01-01	400.0	NULL
+600	emp 6 - no dept	2001-01-01	400.0	100
+700	emp 7	2010-01-01	400.0	100
+800	emp 8	2016-01-01	150.0	70
+
+
+-- !query 11
+SELECT * 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT max(dept.dept_id) 
+                   FROM   dept 
+                   WHERE  dept.dept_id > 100 
+                   GROUP  BY state 
+                   LIMIT  1)
+-- !query 11 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 11 output
+100	emp 1	2005-01-01	100.0	10
+100	emp 1	2005-01-01	100.0	10
+200	emp 2	2003-01-01	200.0	10
+300	emp 3	2002-01-01	300.0	20
+400	emp 4	2005-01-01	400.0	30
+500	emp 5	2001-01-01	400.0	NULL
+600	emp 6 - no dept	2001-01-01	400.0	100
+700	emp 7	2010-01-01	400.0	100
+800	emp 8	2016-01-01	150.0	70