You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by hv...@apache.org on 2017/02/15 16:34:10 UTC
spark git commit: [SPARK-18872][SQL][TESTS] New test cases for EXISTS
subquery (Joins + CTE)
Repository: spark
Updated Branches:
refs/heads/master 5ad10c531 -> a8a139820
[SPARK-18872][SQL][TESTS] New test cases for EXISTS subquery (Joins + CTE)
## What changes were proposed in this pull request?
This PR adds the third and final set of tests for EXISTS subquery.
File name | Brief description
------------------------| -----------------
exists-cte.sql |Tests Exist subqueries referencing CTE
exists-joins-and-set-ops.sql|Tests Exists subquery used in Joins (Both when joins occurs in outer and suquery blocks)
DB2 results are attached here as reference :
[exists-cte-db2.txt](https://github.com/apache/spark/files/752091/exists-cte-db2.txt)
[exists-joins-and-set-ops-db2.txt](https://github.com/apache/spark/files/753283/exists-joins-and-set-ops-db2.txt) (updated)
## 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: Dilip Biswal <db...@us.ibm.com>
Closes #16802 from dilipbiswal/exists-pr3.
Project: http://git-wip-us.apache.org/repos/asf/spark/repo
Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/a8a13982
Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/a8a13982
Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/a8a13982
Branch: refs/heads/master
Commit: a8a139820c4a77a0b017b621bec6273cc09c8476
Parents: 5ad10c5
Author: Dilip Biswal <db...@us.ibm.com>
Authored: Wed Feb 15 17:34:05 2017 +0100
Committer: Herman van Hovell <hv...@databricks.com>
Committed: Wed Feb 15 17:34:05 2017 +0100
----------------------------------------------------------------------
.../subquery/exists-subquery/exists-cte.sql | 142 ++++++++
.../exists-joins-and-set-ops.sql | 228 ++++++++++++
.../subquery/exists-subquery/exists-cte.sql.out | 200 ++++++++++
.../exists-joins-and-set-ops.sql.out | 363 +++++++++++++++++++
4 files changed, 933 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/spark/blob/a8a13982/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-cte.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-cte.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-cte.sql
new file mode 100644
index 0000000..c678483
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-cte.sql
@@ -0,0 +1,142 @@
+-- Tests EXISTS subquery used along with
+-- Common Table Expressions(CTE)
+
+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);
+
+-- CTE used inside subquery with correlated condition
+-- TC.01.01
+WITH bonus_cte
+ AS (SELECT *
+ FROM bonus
+ WHERE EXISTS (SELECT dept.dept_id,
+ emp.emp_name,
+ Max(salary),
+ Count(*)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY dept.dept_id,
+ emp.emp_name
+ ORDER BY emp.emp_name))
+SELECT *
+FROM bonus a
+WHERE a.bonus_amt > 30
+ AND EXISTS (SELECT 1
+ FROM bonus_cte b
+ WHERE a.emp_name = b.emp_name);
+
+-- Inner join between two CTEs with correlated condition
+-- TC.01.02
+WITH emp_cte
+ AS (SELECT *
+ FROM emp
+ WHERE id >= 100
+ AND id <= 300),
+ dept_cte
+ AS (SELECT *
+ FROM dept
+ WHERE dept_id = 10)
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp_cte a
+ JOIN dept_cte b
+ ON a.dept_id = b.dept_id
+ WHERE bonus.emp_name = a.emp_name);
+
+-- Left outer join between two CTEs with correlated condition
+-- TC.01.03
+WITH emp_cte
+ AS (SELECT *
+ FROM emp
+ WHERE id >= 100
+ AND id <= 300),
+ dept_cte
+ AS (SELECT *
+ FROM dept
+ WHERE dept_id = 10)
+SELECT DISTINCT b.emp_name,
+ b.bonus_amt
+FROM bonus b,
+ emp_cte e,
+ dept d
+WHERE e.dept_id = d.dept_id
+ AND e.emp_name = b.emp_name
+ AND EXISTS (SELECT *
+ FROM emp_cte a
+ LEFT JOIN dept_cte b
+ ON a.dept_id = b.dept_id
+ WHERE e.emp_name = a.emp_name);
+
+-- Joins inside cte and aggregation on cte referenced subquery with correlated condition
+-- TC.01.04
+WITH empdept
+ AS (SELECT id,
+ salary,
+ emp_name,
+ dept.dept_id
+ FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+ WHERE emp.id IN ( 100, 200 ))
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE EXISTS (SELECT dept_id,
+ max(salary)
+ FROM empdept
+ GROUP BY dept_id
+ HAVING count(*) > 1)
+GROUP BY emp_name;
+
+-- Using not exists
+-- TC.01.05
+WITH empdept
+ AS (SELECT id,
+ salary,
+ emp_name,
+ dept.dept_id
+ FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+ WHERE emp.id IN ( 100, 200 ))
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE NOT EXISTS (SELECT dept_id,
+ Max(salary)
+ FROM empdept
+ GROUP BY dept_id
+ HAVING count(*) < 1)
+GROUP BY emp_name;
http://git-wip-us.apache.org/repos/asf/spark/blob/a8a13982/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-joins-and-set-ops.sql
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-joins-and-set-ops.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-joins-and-set-ops.sql
new file mode 100644
index 0000000..cc4ed64
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-joins-and-set-ops.sql
@@ -0,0 +1,228 @@
+-- Tests EXISTS subquery support. Tests Exists subquery
+-- used in Joins (Both when joins occurs in outer and suquery blocks)
+
+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);
+
+-- Join in outer query block
+-- TC.01.01
+SELECT *
+FROM emp,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- Join in outer query block with ON condition
+-- TC.01.02
+SELECT *
+FROM emp
+ JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- Left join in outer query block with ON condition
+-- TC.01.03
+SELECT *
+FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- Join in outer query block + NOT EXISTS
+-- TC.01.04
+SELECT *
+FROM emp,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND NOT EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+
+-- inner join in subquery.
+-- TC.01.05
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- right join in subquery
+-- TC.01.06
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ RIGHT JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name);
+
+
+-- Aggregation and join in subquery
+-- TC.01.07
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT dept.dept_id,
+ emp.emp_name,
+ Max(salary),
+ Count(*)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY dept.dept_id,
+ emp.emp_name
+ ORDER BY emp.emp_name);
+
+-- Aggregations in outer and subquery + join in subquery
+-- TC.01.08
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name;
+
+-- TC.01.09
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE NOT EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name;
+
+-- Set operations along with EXISTS subquery
+-- union
+-- TC.02.01
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ UNION
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- intersect
+-- TC.02.02
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- intersect + not exists
+-- TC.02.03
+SELECT *
+FROM emp
+WHERE NOT EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- Union all in outer query and except,intersect in subqueries.
+-- TC.02.04
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ EXCEPT
+ SELECT *
+ FROM dept
+ WHERE dept_id > 50)
+UNION ALL
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- Union in outer query and except,intersect in subqueries.
+-- TC.02.05
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ EXCEPT
+ SELECT *
+ FROM dept
+ WHERE dept_id > 50)
+UNION
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
http://git-wip-us.apache.org/repos/asf/spark/blob/a8a13982/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-cte.sql.out
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-cte.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-cte.sql.out
new file mode 100644
index 0000000..c6c1c04
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-cte.sql.out
@@ -0,0 +1,200 @@
+-- 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
+WITH bonus_cte
+ AS (SELECT *
+ FROM bonus
+ WHERE EXISTS (SELECT dept.dept_id,
+ emp.emp_name,
+ Max(salary),
+ Count(*)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY dept.dept_id,
+ emp.emp_name
+ ORDER BY emp.emp_name))
+SELECT *
+FROM bonus a
+WHERE a.bonus_amt > 30
+ AND EXISTS (SELECT 1
+ FROM bonus_cte b
+ WHERE a.emp_name = b.emp_name)
+-- !query 3 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 3 output
+emp 2 100.0
+emp 2 300.0
+emp 3 300.0
+emp 4 100.0
+
+
+-- !query 4
+WITH emp_cte
+ AS (SELECT *
+ FROM emp
+ WHERE id >= 100
+ AND id <= 300),
+ dept_cte
+ AS (SELECT *
+ FROM dept
+ WHERE dept_id = 10)
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp_cte a
+ JOIN dept_cte b
+ ON a.dept_id = b.dept_id
+ WHERE bonus.emp_name = a.emp_name)
+-- !query 4 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 4 output
+emp 1 10.0
+emp 1 20.0
+emp 2 100.0
+emp 2 300.0
+
+
+-- !query 5
+WITH emp_cte
+ AS (SELECT *
+ FROM emp
+ WHERE id >= 100
+ AND id <= 300),
+ dept_cte
+ AS (SELECT *
+ FROM dept
+ WHERE dept_id = 10)
+SELECT DISTINCT b.emp_name,
+ b.bonus_amt
+FROM bonus b,
+ emp_cte e,
+ dept d
+WHERE e.dept_id = d.dept_id
+ AND e.emp_name = b.emp_name
+ AND EXISTS (SELECT *
+ FROM emp_cte a
+ LEFT JOIN dept_cte b
+ ON a.dept_id = b.dept_id
+ WHERE e.emp_name = a.emp_name)
+-- !query 5 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 5 output
+emp 1 10.0
+emp 1 20.0
+emp 2 100.0
+emp 2 300.0
+emp 3 300.0
+
+
+-- !query 6
+WITH empdept
+ AS (SELECT id,
+ salary,
+ emp_name,
+ dept.dept_id
+ FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+ WHERE emp.id IN ( 100, 200 ))
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE EXISTS (SELECT dept_id,
+ max(salary)
+ FROM empdept
+ GROUP BY dept_id
+ HAVING count(*) > 1)
+GROUP BY emp_name
+-- !query 6 schema
+struct<emp_name:string,sum(bonus_amt):double>
+-- !query 6 output
+emp 1 30.0
+emp 2 400.0
+emp 3 300.0
+emp 4 100.0
+emp 5 1000.0
+emp 6 - no dept 500.0
+
+
+-- !query 7
+WITH empdept
+ AS (SELECT id,
+ salary,
+ emp_name,
+ dept.dept_id
+ FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+ WHERE emp.id IN ( 100, 200 ))
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE NOT EXISTS (SELECT dept_id,
+ Max(salary)
+ FROM empdept
+ GROUP BY dept_id
+ HAVING count(*) < 1)
+GROUP BY emp_name
+-- !query 7 schema
+struct<emp_name:string,sum(bonus_amt):double>
+-- !query 7 output
+emp 1 30.0
+emp 2 400.0
+emp 3 300.0
+emp 4 100.0
+emp 5 1000.0
+emp 6 - no dept 500.0
http://git-wip-us.apache.org/repos/asf/spark/blob/a8a13982/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-joins-and-set-ops.sql.out
----------------------------------------------------------------------
diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-joins-and-set-ops.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-joins-and-set-ops.sql.out
new file mode 100644
index 0000000..c488cba
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-joins-and-set-ops.sql.out
@@ -0,0 +1,363 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 17
+
+
+-- !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,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 3 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int,dept_id:int,dept_name:string,state:string>
+-- !query 3 output
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+200 emp 2 2003-01-01 200.0 10 10 dept 1 CA
+300 emp 3 2002-01-01 300.0 20 20 dept 2 NY
+400 emp 4 2005-01-01 400.0 30 30 dept 3 TX
+
+
+-- !query 4
+SELECT *
+FROM emp
+ JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 4 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int,dept_id:int,dept_name:string,state:string>
+-- !query 4 output
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+200 emp 2 2003-01-01 200.0 10 10 dept 1 CA
+300 emp 3 2002-01-01 300.0 20 20 dept 2 NY
+400 emp 4 2005-01-01 400.0 30 30 dept 3 TX
+
+
+-- !query 5
+SELECT *
+FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 5 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int,dept_id:int,dept_name:string,state:string>
+-- !query 5 output
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+200 emp 2 2003-01-01 200.0 10 10 dept 1 CA
+300 emp 3 2002-01-01 300.0 20 20 dept 2 NY
+400 emp 4 2005-01-01 400.0 30 30 dept 3 TX
+500 emp 5 2001-01-01 400.0 NULL NULL NULL NULL
+600 emp 6 - no dept 2001-01-01 400.0 100 NULL NULL NULL
+
+
+-- !query 6
+SELECT *
+FROM emp,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND NOT EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 6 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int,dept_id:int,dept_name:string,state:string>
+-- !query 6 output
+800 emp 8 2016-01-01 150.0 70 70 dept 7 FL
+
+
+-- !query 7
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 7 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 7 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 8
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ RIGHT JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 8 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 8 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 9
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT dept.dept_id,
+ emp.emp_name,
+ Max(salary),
+ Count(*)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY dept.dept_id,
+ emp.emp_name
+ ORDER BY emp.emp_name)
+-- !query 9 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 9 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 10
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name
+-- !query 10 schema
+struct<emp_name:string,sum(bonus_amt):double>
+-- !query 10 output
+emp 1 30.0
+
+
+-- !query 11
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE NOT EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name
+-- !query 11 schema
+struct<emp_name:string,sum(bonus_amt):double>
+-- !query 11 output
+emp 2 400.0
+emp 3 300.0
+emp 4 100.0
+emp 5 1000.0
+emp 6 - no dept 500.0
+
+
+-- !query 12
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ UNION
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50)
+-- !query 12 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 12 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 13
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50)
+-- !query 13 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 13 output
+
+
+
+-- !query 14
+SELECT *
+FROM emp
+WHERE NOT EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50)
+-- !query 14 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 14 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 15
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ EXCEPT
+ SELECT *
+ FROM dept
+ WHERE dept_id > 50)
+UNION ALL
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50)
+-- !query 15 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 15 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 16
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ EXCEPT
+ SELECT *
+ FROM dept
+ WHERE dept_id > 50)
+UNION
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50)
+-- !query 16 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 16 output
+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
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org