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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
# name: test/sql/window/test_lead_lag.test
# description: Test Lead/Lag function
# group: [window]
query II
select c1, lead(c1, 2) over (order by c0 rows between 2 preceding and 4 preceding) as b
from (values
(1, 2),
(2, 3),
(3, 4),
(4, 5)
) a(c0, c1);
----
2 4
3 5
4 NULL
5 NULL
# Lag > 2 with explicit constant default
statement ok
create table win(id int, v int, t int, f float, s varchar);
statement ok
insert into win values
(1, 1, 2, 0.54, 'h'),
(1, 1, 1, 0.21, 'e'),
(1, 2, 3, 0.001, 'l'),
(2, 10, 4, 0.04, 'l'),
(2, 11, -1, 10.45, 'o'),
(3, -1, 0, 13.32, ','),
(3, 5, -2, 9.87, 'wor'),
(3, null, 10, 6.56, 'ld');
query IIII
select id, v, t, lag(v, 2, NULL) over (partition by id order by t asc)
from win
order by id, t
----
1 1 1 NULL
1 1 2 NULL
1 2 3 1
2 11 -1 NULL
2 10 4 NULL
3 5 -2 NULL
3 -1 0 NULL
3 NULL 10 5
# Shifted lead optimisation with hash collisions
statement ok
CREATE TABLE issue14398 (date DATE, "group" INT, count INT, status STRING);
statement ok
INSERT INTO issue14398 VALUES
('2024-01-01', 1, 1000, 'ordered'),
('2024-02-01', 1, 1000, 'dispatched'),
('2024-03-01', 1, 1000, 'dispatched'),
('2024-01-01', 2, 2000, 'ordered'),
('2024-02-01', 2, 2000, 'ordered'),
('2024-03-01', 2, 2000, 'ordered'),
('2024-01-01', 3, 3000, 'ordered'),
('2024-02-01', 3, 3000, 'ordered'),
('2024-03-01', 3, 3000, 'late'),
('2024-01-01', 4, 4000, 'ordered'),
('2024-02-01', 4, 4000, 'ordered'),
('2024-03-01', 4, 4000, 'ordered'),
('2024-01-01', 5, 5000, 'ordered'),
('2024-02-01', 5, 5000, 'late'),
('2024-03-01', 5, 5000, 'ordered'),
('2024-01-01', 6, 1000, 'dispatched'),
('2024-02-01', 6, 1000, 'dispatched'),
('2024-03-01', 6, 1000, 'dispatched'),
('2024-01-01', 7, 1000, 'late'),
('2024-02-01', 7, 1000, 'dispatched'),
('2024-03-01', 7, 1000, 'dispatched');
query IIIIII
SELECT
"t0"."date",
"t0"."group",
"t0"."count",
"t0"."status",
LEAD("t0"."date", 2) OVER (PARTITION BY "t0"."group" ORDER BY "t0"."date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "end_date",
LEAD("t0"."status", 2) OVER (PARTITION BY "t0"."group" ORDER BY "t0"."date" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "end_status"
FROM "issue14398" AS "t0"
ORDER BY 2, 1
----
2024-01-01 1 1000 ordered 2024-03-01 dispatched
2024-02-01 1 1000 dispatched NULL NULL
2024-03-01 1 1000 dispatched NULL NULL
2024-01-01 2 2000 ordered 2024-03-01 ordered
2024-02-01 2 2000 ordered NULL NULL
2024-03-01 2 2000 ordered NULL NULL
2024-01-01 3 3000 ordered 2024-03-01 late
2024-02-01 3 3000 ordered NULL NULL
2024-03-01 3 3000 late NULL NULL
2024-01-01 4 4000 ordered 2024-03-01 ordered
2024-02-01 4 4000 ordered NULL NULL
2024-03-01 4 4000 ordered NULL NULL
2024-01-01 5 5000 ordered 2024-03-01 ordered
2024-02-01 5 5000 late NULL NULL
2024-03-01 5 5000 ordered NULL NULL
2024-01-01 6 1000 dispatched 2024-03-01 dispatched
2024-02-01 6 1000 dispatched NULL NULL
2024-03-01 6 1000 dispatched NULL NULL
2024-01-01 7 1000 late 2024-03-01 dispatched
2024-02-01 7 1000 dispatched NULL NULL
2024-03-01 7 1000 dispatched NULL NULL
# test lag and lead when offset is null
statement ok
CREATE TABLE issue17266(c1 INT, c2 SMALLINT, c3 BITSTRING);
statement ok
INSERT INTO issue17266 VALUES
(0, null, null),
(1, 32767, '101'),
(2, -32767, '101'),
(3, 0, '000'),
(4, null, null);
query IIII
SELECT c1, c3, c2, LAG(c3, c2, BITSTRING'010101010') OVER (PARTITION BY c1 ORDER BY c3) FROM issue17266 ORDER BY c1;
----
0 NULL NULL NULL
1 101 32767 010101010
2 101 -32767 010101010
3 000 0 000
4 NULL NULL NULL
query IIII
SELECT c1, c3, c2, LEAD(c3, c2, BITSTRING'010101010') OVER (PARTITION BY c1 ORDER BY c3) FROM issue17266 ORDER BY c1;
----
0 NULL NULL NULL
1 101 32767 010101010
2 101 -32767 010101010
3 000 0 000
4 NULL NULL NULL
|
