1create table t1 (
2  pk int primary key,
3  a int,
4  b int,
5  c char(10),
6  d decimal(10, 3),
7  e real
8);
9
10insert into t1 values
11( 1, 0, 1,    'one',    0.1,  0.001),
12( 2, 0, 2,    'two',    0.2,  0.002),
13( 3, 0, 3,    'three',  0.3,  0.003),
14( 4, 1, 2,    'three',  0.4,  0.004),
15( 5, 1, 1,    'two',    0.5,  0.005),
16( 6, 1, 1,    'one',    0.6,  0.006),
17( 7, 2, NULL, 'n_one',  0.5,  0.007),
18( 8, 2, 1,    'n_two',  NULL, 0.008),
19( 9, 2, 2,    NULL,     0.7,  0.009),
20(10, 2, 0,    'n_four', 0.8,  0.010),
21(11, 2, 10,   NULL,     0.9,  NULL);
22
23select pk,
24       lead(pk)    over (order by pk),
25       lead(pk, 1) over (order by pk),
26       lead(pk, 2) over (order by pk),
27       lead(pk, 0) over (order by pk),
28       lead(pk, -1) over (order by pk),
29       lead(pk, -2) over (order by pk)
30from t1
31order by pk asc;
32
33select pk,
34       lag(pk)    over (order by pk),
35       lag(pk, 1) over (order by pk),
36       lag(pk, 2) over (order by pk),
37       lag(pk, 0) over (order by pk),
38       lag(pk, -1) over (order by pk),
39       lag(pk, -2) over (order by pk)
40from t1
41order by pk asc;
42
43select pk, pk - 2,
44       lag(pk, pk - 2) over (order by pk),
45       lead(pk, pk - 2) over (order by pk)
46from t1
47order by pk asc;
48
49select pk, pk - 2,
50       lag(pk, pk + 2) over (order by pk),
51       lead(pk, pk + 2) over (order by pk)
52from t1
53order by pk asc;
54
55select pk, a,
56       lead(pk)    over (partition by a order by pk),
57       lead(pk, 1) over (partition by a order by pk),
58       lead(pk, 2) over (partition by a order by pk),
59       lead(pk, 0) over (partition by a order by pk),
60       lead(pk, -1) over (partition by a order by pk),
61       lead(pk, -2) over (partition by a order by pk)
62from t1
63order by pk asc;
64
65select pk, a,
66       lag(pk)    over (partition by a order by pk),
67       lag(pk, 1) over (partition by a order by pk),
68       lag(pk, 2) over (partition by a order by pk),
69       lag(pk, 0) over (partition by a order by pk),
70       lag(pk, -1) over (partition by a order by pk),
71       lag(pk, -2) over (partition by a order by pk)
72from t1
73order by pk asc;
74
75select pk, a, pk - 2,
76       lag(pk, pk - 2) over (partition by a order by pk),
77       lead(pk, pk - 2) over (partition by a order by pk),
78       lag(pk, a - 2) over (partition by a order by pk),
79       lead(pk, a - 2) over (partition by a order by pk)
80from t1
81order by pk asc;
82
83select pk, a, pk - 2,
84       lag(pk, pk + 2) over (partition by a order by pk),
85       lead(pk, pk + 2) over (partition by a order by pk),
86       lag(pk, a + 2) over (partition by a order by pk),
87       lead(pk, a + 2) over (partition by a order by pk)
88from t1
89order by pk asc;
90
91select pk, a, b, c, d, e,
92           lag(a) over (partition by a order by pk),
93           lag(b) over (partition by a order by pk),
94           lag(c) over (partition by a order by pk),
95           lag(d) over (partition by a order by pk),
96           lag(e) over (partition by a order by pk)
97from t1
98order by pk asc;
99
100select pk, a, b, a+b,
101       lag(a + b) over (partition by a order by pk)
102from t1
103order by pk asc;
104
105select pk, a, b, a+b,
106       lag(a + b) over (partition by a order by pk) + pk
107from t1
108order by pk asc;
109
110--echo #
111--echo # MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause
112--echo #
113--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
114select pk,
115       lag(pk, 1) over ()
116from t1;
117
118--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
119select pk,
120       lead(pk, 1) over ()
121from t1;
122
123drop table t1;
124