subquery/exists-subquery/exists-orderby-limit.sql

0001 -- Tests EXISTS subquery support with ORDER BY and LIMIT clauses.
0002
0003 -- Test sort operator with codegen on and off.
0004 --CONFIG_DIM1 spark.sql.codegen.wholeStage=true
0005 --CONFIG_DIM1 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=CODEGEN_ONLY
0006 --CONFIG_DIM1 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=NO_CODEGEN
0007
0008 CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
0009   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0010   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0011   (200, "emp 2", date "2003-01-01", 200.00D, 10),
0012   (300, "emp 3", date "2002-01-01", 300.00D, 20),
0013   (400, "emp 4", date "2005-01-01", 400.00D, 30),
0014   (500, "emp 5", date "2001-01-01", 400.00D, NULL),
0015   (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
0016   (700, "emp 7", date "2010-01-01", 400.00D, 100),
0017   (800, "emp 8", date "2016-01-01", 150.00D, 70)
0018 AS EMP(id, emp_name, hiredate, salary, dept_id);
0019
0020 CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
0021   (10, "dept 1", "CA"),
0022   (20, "dept 2", "NY"),
0023   (30, "dept 3", "TX"),
0024   (40, "dept 4 - unassigned", "OR"),
0025   (50, "dept 5 - unassigned", "NJ"),
0026   (70, "dept 7", "FL")
0027 AS DEPT(dept_id, dept_name, state);
0028
0029 CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
0030   ("emp 1", 10.00D),
0031   ("emp 1", 20.00D),
0032   ("emp 2", 300.00D),
0033   ("emp 2", 100.00D),
0034   ("emp 3", 300.00D),
0035   ("emp 4", 100.00D),
0036   ("emp 5", 1000.00D),
0037   ("emp 6 - no dept", 500.00D)
0038 AS BONUS(emp_name, bonus_amt);
0039
0040 -- order by in both outer and/or inner query block
0041 -- TC.01.01
0042 SELECT *
0043 FROM   emp
0044 WHERE  EXISTS (SELECT dept.dept_id
0045                FROM   dept
0046                WHERE  emp.dept_id = dept.dept_id
0047                ORDER  BY state)
0048 ORDER  BY hiredate;
0049
0050 -- TC.01.02
0051 SELECT id,
0052        hiredate
0053 FROM   emp
0054 WHERE  EXISTS (SELECT dept.dept_id
0055                FROM   dept
0056                WHERE  emp.dept_id = dept.dept_id
0057                ORDER  BY state)
0058 ORDER  BY hiredate DESC;
0059
0060 -- order by with not exists
0061 -- TC.01.03
0062 SELECT *
0063 FROM   emp
0064 WHERE  NOT EXISTS (SELECT dept.dept_id
0065                    FROM   dept
0066                    WHERE  emp.dept_id = dept.dept_id
0067                    ORDER  BY state)
0068 ORDER  BY hiredate;
0069
0070 -- group by + order by with not exists
0071 -- TC.01.04
0072 SELECT emp_name
0073 FROM   emp
0074 WHERE  NOT EXISTS (SELECT max(dept.dept_id) a
0075                    FROM   dept
0076                    WHERE  dept.dept_id = emp.dept_id
0077                    GROUP  BY state
0078                    ORDER  BY state);
0079 -- TC.01.05
0080 SELECT count(*)
0081 FROM   emp
0082 WHERE  NOT EXISTS (SELECT max(dept.dept_id) a
0083                    FROM   dept
0084                    WHERE  dept.dept_id = emp.dept_id
0085                    GROUP  BY dept_id
0086                    ORDER  BY dept_id);
0087
0088 -- limit in the exists subquery block.
0089 -- TC.02.01
0090 SELECT *
0091 FROM   emp
0092 WHERE  EXISTS (SELECT dept.dept_name
0093                FROM   dept
0094                WHERE  dept.dept_id > 10
0095                LIMIT  1);
0096
0097 -- limit in the exists subquery block with aggregate.
0098 -- TC.02.02
0099 SELECT *
0100 FROM   emp
0101 WHERE  EXISTS (SELECT max(dept.dept_id)
0102                FROM   dept
0103                GROUP  BY state
0104                LIMIT  1);
0105
0106 -- limit in the not exists subquery block.
0107 -- TC.02.03
0108 SELECT *
0109 FROM   emp
0110 WHERE  NOT EXISTS (SELECT dept.dept_name
0111                    FROM   dept
0112                    WHERE  dept.dept_id > 100
0113                    LIMIT  1);
0114
0115 -- limit in the not exists subquery block with aggregates.
0116 -- TC.02.04
0117 SELECT *
0118 FROM   emp
0119 WHERE  NOT EXISTS (SELECT max(dept.dept_id)
0120                    FROM   dept
0121                    WHERE  dept.dept_id > 100
0122                    GROUP  BY state
0123                    LIMIT  1);