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
##########
File path:
sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnalysisErrorSuite.scala
##########
@@ -658,16 +658,6 @@ class AnalysisErrorSuite extends AnalysisTest {
assertAnalysisError(plan2, "EqualTo does not support ordering on type map"
:: Nil)
}
- test("PredicateSubQuery is used outside of a filter") {
Review comment:
Restrored it
##########
File path:
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -782,10 +782,11 @@ trait CheckAnalysis extends PredicateHelper with
LookupCatalog {
case inSubqueryOrExistsSubquery =>
plan match {
- case _: Filter | _: SupportsSubquery | _: Join => // Ok
+ case _: Filter | _: SupportsSubquery | _: Join |
+ _: Project | _: Aggregate | _: Window => // Ok
Review comment:
```
/**
* A trait to represent the commands that support subqueries.
* This is used to allow such commands in the subquery-related checks.
*/
trait SupportsSubquery extends LogicalPlan
```
Yeah, I'm bit sceptical whit this one. It states, that it works on commands.
##########
File path:
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
##########
@@ -782,10 +782,11 @@ trait CheckAnalysis extends PredicateHelper with
LookupCatalog {
case inSubqueryOrExistsSubquery =>
plan match {
- case _: Filter | _: SupportsSubquery | _: Join => // Ok
+ case _: Filter | _: SupportsSubquery | _: Join |
+ _: Project | _: Aggregate | _: Window => // Ok
Review comment:
```
/**
* A trait to represent the commands that support subqueries.
* This is used to allow such commands in the subquery-related checks.
*/
trait SupportsSubquery extends LogicalPlan
```
Yeah, I'm bit sceptical whit this one. It states, that it works on commands.
Not sure what kind of sideeffects it can have.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
