This is an automated email from the ASF dual-hosted git repository.
gurwls223 pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new 706c217 [SPARK-29108][SQL][TESTS][FOLLOWUP] Comment out no use test
case and add 'insert into' statement of window.sql (Part 2)
706c217 is described below
commit 706c21763e94957efe4a309abd6e13601b8dcaf3
Author: beliefer <[email protected]>
AuthorDate: Thu Feb 6 15:24:26 2020 +0900
[SPARK-29108][SQL][TESTS][FOLLOWUP] Comment out no use test case and add
'insert into' statement of window.sql (Part 2)
### What changes were proposed in this pull request?
When I running the `window_part2.sql` tests find it lack insert sql.
Therefore, the output is empty.
I checked the postgresql and reference
https://github.com/postgres/postgres/blob/master/src/test/regress/sql/window.sql
Although `window_part1.sql` and `window_part3.sql` exists the insert sql, I
think should also add it into `window_part2.sql`.
Because only one case reference the table `empsalary` and it throws
`AnalysisException`.
```
-- !query
select last(salary) over(order by salary range between 1000 preceding and
1000 following),
lag(salary) over(order by salary range between 1000 preceding and 1000
following),
salary from empsalary
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
Window Frame specifiedwindowframe(RangeFrame, -1000, 1000) must match the
required frame specifiedwindowframe(RowFrame, -1, -1);
```
So we should do four work:
1. comment out the only one case and create a new ticket.
2. Add `INSERT INTO empsalary`.
Note: window_part4.sql not use the table `empsalary`.
### Why are the changes needed?
Supplementary test data.
### Does this PR introduce any user-facing change?
No
### How was this patch tested?
New test case
Closes #27439 from beliefer/add-insert-to-window.
Authored-by: beliefer <[email protected]>
Signed-off-by: HyukjinKwon <[email protected]>
---
.../sql-tests/inputs/postgreSQL/window_part2.sql | 19 +++++++++++---
.../results/postgreSQL/window_part2.sql.out | 29 ++++++++++++++--------
2 files changed, 34 insertions(+), 14 deletions(-)
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/window_part2.sql
b/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/window_part2.sql
index 395149e..ba1acc9 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/window_part2.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/postgreSQL/window_part2.sql
@@ -15,6 +15,18 @@ CREATE TABLE empsalary (
enroll_date date
) USING parquet;
+INSERT INTO empsalary VALUES
+ ('develop', 10, 5200, date '2007-08-01'),
+ ('sales', 1, 5000, date '2006-10-01'),
+ ('personnel', 5, 3500, date '2007-12-10'),
+ ('sales', 4, 4800, date '2007-08-08'),
+ ('personnel', 2, 3900, date '2006-12-23'),
+ ('develop', 7, 4200, date '2008-01-01'),
+ ('develop', 9, 4500, date '2008-01-01'),
+ ('sales', 3, 4800, date '2007-08-01'),
+ ('develop', 8, 6000, date '2006-10-01'),
+ ('develop', 11, 5200, date '2007-08-15');
+
-- [SPARK-28429] SQL Datetime util function being casted to double instead of
timestamp
-- CREATE TEMP VIEW v_window AS
-- SELECT i, min(i) over (order by i range between '1 day' preceding and '10
days' following) as min_i
@@ -99,9 +111,10 @@ FROM tenk1 WHERE unique1 < 10;
-- nth_value(salary, 1) over(order by salary range between 1000 preceding and
1000 following),
-- salary from empsalary;
-select last(salary) over(order by salary range between 1000 preceding and 1000
following),
-lag(salary) over(order by salary range between 1000 preceding and 1000
following),
-salary from empsalary;
+-- [SPARK-30734] AnalysisException that window RangeFrame not match RowFrame
+-- select last(salary) over(order by salary range between 1000 preceding and
1000 following),
+-- lag(salary) over(order by salary range between 1000 preceding and 1000
following),
+-- salary from empsalary;
-- [SPARK-27951] ANSI SQL: NTH_VALUE function
-- select first_value(salary) over(order by salary range between 1000
following and 3000 following
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part2.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part2.sql.out
index 0015740..f41659a 100644
---
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part2.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/window_part2.sql.out
@@ -16,6 +16,24 @@ struct<>
-- !query
+INSERT INTO empsalary VALUES
+ ('develop', 10, 5200, date '2007-08-01'),
+ ('sales', 1, 5000, date '2006-10-01'),
+ ('personnel', 5, 3500, date '2007-12-10'),
+ ('sales', 4, 4800, date '2007-08-08'),
+ ('personnel', 2, 3900, date '2006-12-23'),
+ ('develop', 7, 4200, date '2008-01-01'),
+ ('develop', 9, 4500, date '2008-01-01'),
+ ('sales', 3, 4800, date '2007-08-01'),
+ ('develop', 8, 6000, date '2006-10-01'),
+ ('develop', 11, 5200, date '2007-08-15')
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
SELECT sum(unique1) over (order by four range between 2 preceding and 1
preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10
@@ -73,17 +91,6 @@ struct<sum(unique1) OVER (PARTITION BY four ORDER BY unique1
ASC NULLS FIRST RAN
-- !query
-select last(salary) over(order by salary range between 1000 preceding and 1000
following),
-lag(salary) over(order by salary range between 1000 preceding and 1000
following),
-salary from empsalary
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.AnalysisException
-Window Frame specifiedwindowframe(RangeFrame, -1000, 1000) must match the
required frame specifiedwindowframe(RowFrame, -1, -1);
-
-
--- !query
select ss.id, ss.y,
first(ss.y) over w,
last(ss.y) over w
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
