diff options
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 |