julianhyde commented on code in PR #3925:
URL: https://github.com/apache/calcite/pull/3925#discussion_r1727724433
##########
core/src/test/resources/sql/winagg.iq:
##########
@@ -78,6 +78,87 @@ from emp;
!ok
+# [CALCITE-6538] OVER (ROWS CURRENT ROW) should return a window with one row,
not all rows
+# (RANGE CURRENT ROW returns all rows, and is already correct.)
+select ename,
+ sal,
+ sum(sal) over (rows current row) as row_sum_sal,
+ sum(sal) over (range current row) as range_sum_sal
+from emp
+where job = 'MANAGER';
++-------+---------+-------------+---------------+
+| ENAME | SAL | ROW_SUM_SAL | RANGE_SUM_SAL |
++-------+---------+-------------+---------------+
+| BLAKE | 2850.00 | 2850.00 | 8275.00 |
+| CLARK | 2450.00 | 2450.00 | 8275.00 |
+| JONES | 2975.00 | 2975.00 | 8275.00 |
++-------+---------+-------------+---------------+
+(3 rows)
+
+!ok
+
+# Other windows with ROWS and no ORDER BY
+select count(*) over (rows between 3 preceding and 2 preceding) as c3p2p,
+ sum(two) over (rows between 3 preceding and 2 preceding) as s3p2p
+from (select *, 2 as two from emp)
+order by 1;
++-------+-------+
+| C3P2P | S3P2P |
++-------+-------+
+| 0 | |
+| 0 | |
+| 1 | 2 |
+| 2 | 4 |
+| 2 | 4 |
+| 2 | 4 |
+| 2 | 4 |
+| 2 | 4 |
+| 2 | 4 |
+| 2 | 4 |
+| 2 | 4 |
+| 2 | 4 |
+| 2 | 4 |
+| 2 | 4 |
++-------+-------+
+(14 rows)
+
+!ok
+
+!if (false) {
+# The result below is from Postgres.
+# Calcite currently gives the wrong results for s1p (first 2 rows),
Review Comment:
There is a problem, but there is no separate jira case. In my first commit I
wanted to avoid the issue. In my 2nd/3rd commits I addressed it and enabled the
test.
So I consider that CALCITE-5883 (and this fix) covers all of these related
problems.
--
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]
