cloud-fan commented on a change in pull request #34402:
URL: https://github.com/apache/spark/pull/34402#discussion_r829113889
##########
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:
shall we simply make these plan nodes extend `SupportsSubquery`?
##########
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:
can we still keep this test by putting subquery in sort?
##########
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:
do we support subqueries in aggregate function inputs?
--
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]
