[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.

6 files changed, 885 insertions, 0 deletions

diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-aggregate.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-aggregate.sql
new file mode 100644
index 0000000000..b5f458f2cb
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-aggregate.sql
@@ -0,0 +1,115 @@
+-- Tests aggregate expressions in outer query and EXISTS subquery.
+
+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);
+
+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);
+
+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);
+
+-- Aggregate in outer query block.
+-- TC.01.01
+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; 
+
+-- Aggregate in inner/subquery block
+-- TC.01.02
+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); 
+
+-- Aggregate expression in both outer and inner query block.
+-- TC.01.03
+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); 
+
+-- Nested exists with aggregate expression in inner most query block.
+-- TC.01.04
+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));
+
+-- Not exists with Aggregate expression in outer
+-- TC.01.05
+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; 
+
+-- Not exists with Aggregate expression in subquery block
+-- TC.01.06
+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); 
+
+-- Not exists with Aggregate expression in outer and subquery block
+-- TC.01.07
+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); 
+
+-- Nested not exists and exists with aggregate expression in inner most query block.
+-- TC.01.08
+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));
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-having.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-having.sql
new file mode 100644
index 0000000000..c30159039f
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-having.sql
@@ -0,0 +1,94 @@
+-- Tests HAVING clause in subquery.
+
+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);
+
+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);
+
+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);
+
+-- simple having in subquery. 
+-- TC.01.01
+SELECT dept_id, count(*) 
+FROM   emp 
+GROUP  BY dept_id 
+HAVING EXISTS (SELECT 1 
+               FROM   bonus 
+               WHERE  bonus_amt < min(emp.salary)); 
+
+-- nested having in subquery
+-- TC.01.02
+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)));
+
+-- aggregation in outer and inner query block with having
+-- TC.01.03
+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;
+
+-- more aggregate expressions in projection list of subquery
+-- TC.01.04
+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)));
+
+-- multiple aggregations in nested subquery
+-- TC.01.05
+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 )));
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-orderby-limit.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-orderby-limit.sql
new file mode 100644
index 0000000000..19fc188337
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-orderby-limit.sql
@@ -0,0 +1,118 @@
+-- Tests EXISTS subquery support with ORDER BY and LIMIT clauses.
+
+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);
+
+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);
+
+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);
+
+-- order by in both outer and/or inner query block
+-- TC.01.01
+SELECT * 
+FROM   emp 
+WHERE  EXISTS (SELECT dept.dept_id 
+               FROM   dept 
+               WHERE  emp.dept_id = dept.dept_id 
+               ORDER  BY state) 
+ORDER  BY hiredate; 
+
+-- TC.01.02
+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; 
+
+-- order by with not exists 
+-- TC.01.03
+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; 
+
+-- group by + order by with not exists
+-- TC.01.04
+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);
+-- TC.01.05
+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); 
+
+-- limit in the exists subquery block.
+-- TC.02.01
+SELECT * 
+FROM   emp 
+WHERE  EXISTS (SELECT dept.dept_name 
+               FROM   dept 
+               WHERE  dept.dept_id > 10 
+               LIMIT  1); 
+
+-- limit in the exists subquery block with aggregate.
+-- TC.02.02
+SELECT * 
+FROM   emp 
+WHERE  EXISTS (SELECT max(dept.dept_id) 
+               FROM   dept 
+               GROUP  BY state 
+               LIMIT  1); 
+
+-- limit in the not exists subquery block.
+-- TC.02.03
+SELECT * 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT dept.dept_name 
+                   FROM   dept 
+                   WHERE  dept.dept_id > 100 
+                   LIMIT  1); 
+
+-- limit in the not exists subquery block with aggregates.
+-- TC.02.04
+SELECT * 
+FROM   emp 
+WHERE  NOT EXISTS (SELECT max(dept.dept_id) 
+                   FROM   dept 
+                   WHERE  dept.dept_id > 100 
+                   GROUP  BY state 
+                   LIMIT  1); 
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