1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
# name: test/sql/window/test_window_dbplyr.test
# description: Ensure dbplyr crash with ORDER BY under window stays fixed
# group: [window]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE dbplyr_052 (x INTEGER, g DOUBLE, w int)
statement ok
INSERT INTO dbplyr_052 VALUES (1,1, 42),(2,1, 42),(3,1, 42),(2,2, 42),(3,2, 42),(4,2, 42)
# this works fine because we order by the already-projected column in the innermost query
query IR rowsort
SELECT x, g FROM (SELECT x, g, SUM(x) OVER (PARTITION BY g ORDER BY x ROWS UNBOUNDED PRECEDING) AS zzz67 FROM (SELECT x, g FROM dbplyr_052 ORDER BY x) dbplyr_053) dbplyr_054 WHERE (zzz67 > 3.0)
----
3 1.000000
3 2.000000
4 2.000000
# this breaks because we add a fake projection that is not pruned
query IR rowsort
SELECT x, g FROM (SELECT x, g, SUM(x) OVER (PARTITION BY g ORDER BY x ROWS UNBOUNDED PRECEDING) AS zzz67 FROM (SELECT x, g FROM dbplyr_052 ORDER BY w) dbplyr_053) dbplyr_054 WHERE (zzz67 > 3.0)
----
3 1.000000
3 2.000000
4 2.000000
# this also breaks because we add a fake projection that is not pruned even if we already have that projection,
# just with a different table name
query IR rowsort
SELECT x, g FROM (SELECT x, g, SUM(x) OVER (PARTITION BY g ORDER BY x ROWS UNBOUNDED PRECEDING) AS zzz67 FROM (SELECT * FROM dbplyr_052 ORDER BY x) dbplyr_053) dbplyr_054 WHERE (zzz67 > 3.0)
----
3 1.000000
3 2.000000
4 2.000000
|
