You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by dilipbiswal <gi...@git.apache.org> on 2017/02/04 07:48:23 UTC
[GitHub] spark pull request #16802: [SPARK-18872][SQL][TESTS] New test cases for EXIS...
GitHub user dilipbiswal opened a pull request:
https://github.com/apache/spark/pull/16802
[SPARK-18872][SQL][TESTS] New test cases for EXISTS subquery (Joins + CTE)
## What changes were proposed in this pull request?
This PR adds the third and final set of tests for EXISTS subquery.
File name | Brief description
------------------------| -----------------
exists-cte.sql |Tests Exist subqueries referencing CTE
exists-joins-and-set-ops.sql|Tests Exists subquery used in Joins (Both when joins occurs in outer and suquery blocks)
DB2 results are attached here as reference :
[exists-cte-db2.txt](https://github.com/apache/spark/files/752091/exists-cte-db2.txt)
[exists-joins-and-set-ops-db2.txt](https://github.com/apache/spark/files/752092/exists-joins-and-set-ops-db2.txt)
## How was this patch tested?
The test result is compared with the result run from another SQL engine (in this case is IBM DB2). If the result are equivalent, we assume the result is correct.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/dilipbiswal/spark exists-pr3
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/16802.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #16802
----
commit 67946812fef927bc14b8520d425dea095c39e65d
Author: Dilip Biswal <db...@us.ibm.com>
Date: 2017-01-30T22:47:38Z
[SPARK-18872][SQL][TESTS] New test cases for EXISTS subquery (Joins + CTE)
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16802
**[Test build #72367 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72367/testReport)** for PR 16802 at commit [`6794681`](https://github.com/apache/spark/commit/67946812fef927bc14b8520d425dea095c39e65d).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16802
Merged build finished. Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16802
Merged build finished. Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16802
**[Test build #72367 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72367/testReport)** for PR 16802 at commit [`6794681`](https://github.com/apache/spark/commit/67946812fef927bc14b8520d425dea095c39e65d).
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16802: [SPARK-18872][SQL][TESTS] New test cases for EXIS...
Posted by dilipbiswal <gi...@git.apache.org>.
Github user dilipbiswal commented on a diff in the pull request:
https://github.com/apache/spark/pull/16802#discussion_r99499014
--- Diff: sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-joins-and-set-ops.sql ---
@@ -0,0 +1,206 @@
+-- Tests EXISTS subquery support. Tests Exists subquery
+-- used in Joins (Both when joins occurs in outer and suquery blocks)
+
+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);
+
+-- Join in outer query block
+-- TC.01.01
+SELECT *
+FROM emp,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- Join in outer query block with ON condition
+-- TC.01.02
+SELECT *
+FROM emp
+ JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- Left join in outer query block with ON condition
+-- TC.01.03
+SELECT *
+FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- Join in outer query block + NOT EXISTS
+-- TC.01.04
+SELECT *
+FROM emp,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND NOT EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+
+-- inner join in subquery.
+-- TC.01.05
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- right join in subquery
+-- TC.01.06
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ RIGHT JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name);
+
+
+-- Aggregation and join in subquery
+-- TC.01.07
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT dept.dept_id,
+ emp.emp_name,
+ Max(salary),
+ Count(*)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY dept.dept_id,
+ emp.emp_name
+ ORDER BY emp.emp_name);
+
+-- Aggregations in outer and subquery + join in subquery
+-- TC.01.08
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name;
+
+-- TC.01.09
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE NOT EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name;
+
+-- Set operations along with EXISTS subquery
+-- union
+-- TC.02.01
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ UNION
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- intersect
+-- TC.02.02
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- intersect + not exists
+-- TC.02.03
+SELECT *
+FROM emp
+WHERE NOT EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- Union in outer query and except,intersect in subqueries.
+-- TC.02.04
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ EXCEPT
+ SELECT *
+ FROM dept
+ WHERE dept_id > 50)
+UNION ALL
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
--- End diff --
@dongjoon-hyun Thanks !! I have added the UNION test case. I have also uploaded the updated db2 result file.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by dilipbiswal <gi...@git.apache.org>.
Github user dilipbiswal commented on the issue:
https://github.com/apache/spark/pull/16802
@hvanhovell Thank you very much Herman !!
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16802
**[Test build #72415 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72415/testReport)** for PR 16802 at commit [`3ebd654`](https://github.com/apache/spark/commit/3ebd654bca1cad01fa96b924ececd12fd33ef516).
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16802
Test FAILed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/72363/
Test FAILed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16802
**[Test build #72363 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72363/testReport)** for PR 16802 at commit [`6794681`](https://github.com/apache/spark/commit/67946812fef927bc14b8520d425dea095c39e65d).
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by dilipbiswal <gi...@git.apache.org>.
Github user dilipbiswal commented on the issue:
https://github.com/apache/spark/pull/16802
cc @hvanhovell @gatorsmile
Thanks @nsyca @dongjoon-hyun for reviewing.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/16802
LGTM - merging to master.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16802
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/72415/
Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16802: [SPARK-18872][SQL][TESTS] New test cases for EXIS...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/spark/pull/16802
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16802: [SPARK-18872][SQL][TESTS] New test cases for EXIS...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/16802#discussion_r99466460
--- Diff: sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-joins-and-set-ops.sql.out ---
@@ -0,0 +1,330 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 16
+
+
+-- !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,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 3 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int,dept_id:int,dept_name:string,state:string>
+-- !query 3 output
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+200 emp 2 2003-01-01 200.0 10 10 dept 1 CA
+300 emp 3 2002-01-01 300.0 20 20 dept 2 NY
+400 emp 4 2005-01-01 400.0 30 30 dept 3 TX
+
+
+-- !query 4
+SELECT *
+FROM emp
+ JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 4 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int,dept_id:int,dept_name:string,state:string>
+-- !query 4 output
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+200 emp 2 2003-01-01 200.0 10 10 dept 1 CA
+300 emp 3 2002-01-01 300.0 20 20 dept 2 NY
+400 emp 4 2005-01-01 400.0 30 30 dept 3 TX
+
+
+-- !query 5
+SELECT *
+FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 5 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int,dept_id:int,dept_name:string,state:string>
+-- !query 5 output
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+100 emp 1 2005-01-01 100.0 10 10 dept 1 CA
+200 emp 2 2003-01-01 200.0 10 10 dept 1 CA
+300 emp 3 2002-01-01 300.0 20 20 dept 2 NY
+400 emp 4 2005-01-01 400.0 30 30 dept 3 TX
+500 emp 5 2001-01-01 400.0 NULL NULL NULL NULL
+600 emp 6 - no dept 2001-01-01 400.0 100 NULL NULL NULL
+
+
+-- !query 6
+SELECT *
+FROM emp,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND NOT EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 6 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int,dept_id:int,dept_name:string,state:string>
+-- !query 6 output
+800 emp 8 2016-01-01 150.0 70 70 dept 7 FL
+
+
+-- !query 7
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 7 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 7 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 8
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ RIGHT JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name)
+-- !query 8 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 8 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 9
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT dept.dept_id,
+ emp.emp_name,
+ Max(salary),
+ Count(*)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY dept.dept_id,
+ emp.emp_name
+ ORDER BY emp.emp_name)
+-- !query 9 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 9 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 10
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name
+-- !query 10 schema
+struct<emp_name:string,sum(bonus_amt):double>
+-- !query 10 output
+emp 1 30.0
+
+
+-- !query 11
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE NOT EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name
+-- !query 11 schema
+struct<emp_name:string,sum(bonus_amt):double>
+-- !query 11 output
+emp 2 400.0
+emp 3 300.0
+emp 4 100.0
+emp 5 1000.0
+emp 6 - no dept 500.0
+
+
+-- !query 12
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ UNION
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50)
+-- !query 12 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 12 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 13
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50)
+-- !query 13 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 13 output
+
+
+
+-- !query 14
+SELECT *
+FROM emp
+WHERE NOT EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50)
+-- !query 14 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 14 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 15
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ EXCEPT
+ SELECT *
+ FROM dept
+ WHERE dept_id > 50)
+UNION ALL
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50)
+-- !query 15 schema
+struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+-- !query 15 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
--- End diff --
I have verified the answer sets in this file match with the ones in DB2.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16802
Merged build finished. Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by hvanhovell <gi...@git.apache.org>.
Github user hvanhovell commented on the issue:
https://github.com/apache/spark/pull/16802
Retest this please
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the issue:
https://github.com/apache/spark/pull/16802
**[Test build #72415 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/72415/testReport)** for PR 16802 at commit [`3ebd654`](https://github.com/apache/spark/commit/3ebd654bca1cad01fa96b924ececd12fd33ef516).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16802: [SPARK-18872][SQL][TESTS] New test cases for EXISTS subq...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16802
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/72367/
Test PASSed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16802: [SPARK-18872][SQL][TESTS] New test cases for EXIS...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on a diff in the pull request:
https://github.com/apache/spark/pull/16802#discussion_r99479814
--- Diff: sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-joins-and-set-ops.sql ---
@@ -0,0 +1,206 @@
+-- Tests EXISTS subquery support. Tests Exists subquery
+-- used in Joins (Both when joins occurs in outer and suquery blocks)
+
+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);
+
+-- Join in outer query block
+-- TC.01.01
+SELECT *
+FROM emp,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- Join in outer query block with ON condition
+-- TC.01.02
+SELECT *
+FROM emp
+ JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- Left join in outer query block with ON condition
+-- TC.01.03
+SELECT *
+FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+WHERE EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- Join in outer query block + NOT EXISTS
+-- TC.01.04
+SELECT *
+FROM emp,
+ dept
+WHERE emp.dept_id = dept.dept_id
+ AND NOT EXISTS (SELECT *
+ FROM bonus
+ WHERE bonus.emp_name = emp.emp_name);
+
+
+-- inner join in subquery.
+-- TC.01.05
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name);
+
+-- right join in subquery
+-- TC.01.06
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp
+ RIGHT JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name);
+
+
+-- Aggregation and join in subquery
+-- TC.01.07
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT dept.dept_id,
+ emp.emp_name,
+ Max(salary),
+ Count(*)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY dept.dept_id,
+ emp.emp_name
+ ORDER BY emp.emp_name);
+
+-- Aggregations in outer and subquery + join in subquery
+-- TC.01.08
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name;
+
+-- TC.01.09
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE NOT EXISTS (SELECT emp_name,
+ Max(salary)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY emp_name
+ HAVING Count(*) > 1
+ ORDER BY emp_name)
+GROUP BY emp_name;
+
+-- Set operations along with EXISTS subquery
+-- union
+-- TC.02.01
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ UNION
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- intersect
+-- TC.02.02
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- intersect + not exists
+-- TC.02.03
+SELECT *
+FROM emp
+WHERE NOT EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
+-- Union in outer query and except,intersect in subqueries.
+-- TC.02.04
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ EXCEPT
+ SELECT *
+ FROM dept
+ WHERE dept_id > 50)
+UNION ALL
+SELECT *
+FROM emp
+WHERE EXISTS (SELECT *
+ FROM dept
+ WHERE dept_id < 30
+ INTERSECT
+ SELECT *
+ FROM dept
+ WHERE dept_id >= 30
+ AND dept_id <= 50);
+
--- End diff --
Hi, @dilipbiswal .
Can we have another test case, `TC.02.05`, here for `UNION` instead of `UNION ALL`?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16802: [SPARK-18872][SQL][TESTS] New test cases for EXIS...
Posted by nsyca <gi...@git.apache.org>.
Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/16802#discussion_r99466405
--- Diff: sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-cte.sql.out ---
@@ -0,0 +1,200 @@
+-- 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
+WITH bonus_cte
+ AS (SELECT *
+ FROM bonus
+ WHERE EXISTS (SELECT dept.dept_id,
+ emp.emp_name,
+ Max(salary),
+ Count(*)
+ FROM emp
+ JOIN dept
+ ON dept.dept_id = emp.dept_id
+ WHERE bonus.emp_name = emp.emp_name
+ GROUP BY dept.dept_id,
+ emp.emp_name
+ ORDER BY emp.emp_name))
+SELECT *
+FROM bonus a
+WHERE a.bonus_amt > 30
+ AND EXISTS (SELECT 1
+ FROM bonus_cte b
+ WHERE a.emp_name = b.emp_name)
+-- !query 3 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 3 output
+emp 2 100.0
+emp 2 300.0
+emp 3 300.0
+emp 4 100.0
+
+
+-- !query 4
+WITH emp_cte
+ AS (SELECT *
+ FROM emp
+ WHERE id >= 100
+ AND id <= 300),
+ dept_cte
+ AS (SELECT *
+ FROM dept
+ WHERE dept_id = 10)
+SELECT *
+FROM bonus
+WHERE EXISTS (SELECT *
+ FROM emp_cte a
+ JOIN dept_cte b
+ ON a.dept_id = b.dept_id
+ WHERE bonus.emp_name = a.emp_name)
+-- !query 4 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 4 output
+emp 1 10.0
+emp 1 20.0
+emp 2 100.0
+emp 2 300.0
+
+
+-- !query 5
+WITH emp_cte
+ AS (SELECT *
+ FROM emp
+ WHERE id >= 100
+ AND id <= 300),
+ dept_cte
+ AS (SELECT *
+ FROM dept
+ WHERE dept_id = 10)
+SELECT DISTINCT b.emp_name,
+ b.bonus_amt
+FROM bonus b,
+ emp_cte e,
+ dept d
+WHERE e.dept_id = d.dept_id
+ AND e.emp_name = b.emp_name
+ AND EXISTS (SELECT *
+ FROM emp_cte a
+ LEFT JOIN dept_cte b
+ ON a.dept_id = b.dept_id
+ WHERE e.emp_name = a.emp_name)
+-- !query 5 schema
+struct<emp_name:string,bonus_amt:double>
+-- !query 5 output
+emp 1 10.0
+emp 1 20.0
+emp 2 100.0
+emp 2 300.0
+emp 3 300.0
+
+
+-- !query 6
+WITH empdept
+ AS (SELECT id,
+ salary,
+ emp_name,
+ dept.dept_id
+ FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+ WHERE emp.id IN ( 100, 200 ))
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE EXISTS (SELECT dept_id,
+ max(salary)
+ FROM empdept
+ GROUP BY dept_id
+ HAVING count(*) > 1)
+GROUP BY emp_name
+-- !query 6 schema
+struct<emp_name:string,sum(bonus_amt):double>
+-- !query 6 output
+emp 1 30.0
+emp 2 400.0
+emp 3 300.0
+emp 4 100.0
+emp 5 1000.0
+emp 6 - no dept 500.0
+
+
+-- !query 7
+WITH empdept
+ AS (SELECT id,
+ salary,
+ emp_name,
+ dept.dept_id
+ FROM emp
+ LEFT JOIN dept
+ ON emp.dept_id = dept.dept_id
+ WHERE emp.id IN ( 100, 200 ))
+SELECT emp_name,
+ Sum(bonus_amt)
+FROM bonus
+WHERE NOT EXISTS (SELECT dept_id,
+ Max(salary)
+ FROM empdept
+ GROUP BY dept_id
+ HAVING count(*) < 1)
+GROUP BY emp_name
+-- !query 7 schema
+struct<emp_name:string,sum(bonus_amt):double>
+-- !query 7 output
+emp 1 30.0
+emp 2 400.0
+emp 3 300.0
+emp 4 100.0
+emp 5 1000.0
+emp 6 - no dept 500.0
--- End diff --
I have verified the answer sets in this file match with the ones in DB2.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org