gruuya commented on PR #7180:
URL:
https://github.com/apache/arrow-datafusion/pull/7180#issuecomment-1666163172
> If you are saying there are more than 5 values with the same value of c5
then I think technically the query produces non specified answers and thus I
think we should change the test to be deterministic -- perhaps we can increase
the LIMIT to include all the first values of c3
Oh actually I meant that there are only 2 identical values for the sorting
column (C3) which end up in the top-5 according to the test query, but their
order is unstable when using a `LIMIT` as opposed to without it:
```sql
❯ SELECT
MAX(c12) OVER window1,
MIN(c12) OVER window2 as max1,
C3
FROM aggregate_test_100
WINDOW window1 AS (ORDER BY C12),
window2 AS (PARTITION BY C11),
window3 AS (ORDER BY C1)
ORDER BY C3;
+--------------------------------------------------------------------------------------------------------------------------------+----------------------+------+
| MAX(aggregate_test_100.c12) ORDER BY [aggregate_test_100.c12 ASC NULLS
LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | max1
| c3 |
+--------------------------------------------------------------------------------------------------------------------------------+----------------------+------+
| 0.9706712283358269
| 0.9706712283358269 |
-117 |
| 0.8506721053047003
| 0.8506721053047003 |
-117 |
| 0.152498292971736
| 0.152498292971736 |
-111 |
| 0.36936304600612724
| 0.36936304600612724 |
-107 |
| 0.565352842229935
| 0.565352842229935 |
-106 |
...
| 0.7631239070049998
| 0.7631239070049998 |
125 |
+--------------------------------------------------------------------------------------------------------------------------------+----------------------+------+
100 rows in set. Query took 0.154 seconds.
❯ SELECT
MAX(c12) OVER window1,
MIN(c12) OVER window2 as max1,
C3
FROM aggregate_test_100
WINDOW window1 AS (ORDER BY C12),
window2 AS (PARTITION BY C11),
window3 AS (ORDER BY C1)
ORDER BY C3
LIMIT 5;
+--------------------------------------------------------------------------------------------------------------------------------+---------------------+------+
| MAX(aggregate_test_100.c12) ORDER BY [aggregate_test_100.c12 ASC NULLS
LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | max1 |
c3 |
+--------------------------------------------------------------------------------------------------------------------------------+---------------------+------+
| 0.8506721053047003
| 0.8506721053047003 |
-117 |
| 0.9706712283358269
| 0.9706712283358269 |
-117 |
| 0.152498292971736
| 0.152498292971736 |
-111 |
| 0.36936304600612724
| 0.36936304600612724 |
-107 |
| 0.565352842229935
| 0.565352842229935 |
-106 |
+--------------------------------------------------------------------------------------------------------------------------------+---------------------+------+
5 rows in set. Query took 0.042 seconds.
```
(Note that I've included the C3 column for clarity.)
--
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]
