You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by GitBox <gi...@apache.org> on 2022/03/18 12:48:00 UTC
[GitHub] [spark] tanelk commented on a change in pull request #34402: [SPARK-30220] Enable using Exists/In subqueries outside of the Filter node
tanelk commented on a change in pull request #34402:
URL: https://github.com/apache/spark/pull/34402#discussion_r829967745
##########
File path: sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-outside-filter.sql
##########
@@ -0,0 +1,133 @@
+-- Tests EXISTS subquery support where the subquery is used outside the WHERE clause.
+
+
+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);
+
+-- uncorrelated select exist
+-- TC.01.01
+SELECT
+ emp_name,
+ EXISTS (SELECT 1
+ FROM dept
+ WHERE dept.dept_id > 10
+ AND dept.dept_id < 30)
+FROM emp;
+
+-- correlated select exist
+-- TC.01.02
+SELECT
+ emp_name,
+ EXISTS (SELECT 1
+ FROM dept
+ WHERE emp.dept_id = dept.dept_id)
+FROM emp;
+
+-- uncorrelated exist in aggregate filter
Review comment:
Added testcases
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org