create table t1 (
pk int primary key,
a int,
b int,
c char(10),
d decimal(10, 3),
e real
);
insert into t1 values
( 1, 0, 1,    'one',    0.1,  0.001),
( 2, 0, 2,    'two',    0.2,  0.002),
( 3, 0, 3,    'three',  0.3,  0.003),
( 4, 1, 2,    'three',  0.4,  0.004),
( 5, 1, 1,    'two',    0.5,  0.005),
( 6, 1, 1,    'one',    0.6,  0.006),
( 7, 2, NULL, 'n_one',  0.5,  0.007),
( 8, 2, 1,    'n_two',  NULL, 0.008),
( 9, 2, 2,    NULL,     0.7,  0.009),
(10, 2, 0,    'n_four', 0.8,  0.010),
(11, 2, 10,   NULL,     0.9,  NULL);
select pk,
lead(pk)    over (order by pk),
lead(pk, 1) over (order by pk),
lead(pk, 2) over (order by pk),
lead(pk, 0) over (order by pk),
lead(pk, -1) over (order by pk),
lead(pk, -2) over (order by pk)
from t1
order by pk asc;
pk	lead(pk)    over (order by pk)	lead(pk, 1) over (order by pk)	lead(pk, 2) over (order by pk)	lead(pk, 0) over (order by pk)	lead(pk, -1) over (order by pk)	lead(pk, -2) over (order by pk)
1	2	2	3	1	NULL	NULL
2	3	3	4	2	1	NULL
3	4	4	5	3	2	1
4	5	5	6	4	3	2
5	6	6	7	5	4	3
6	7	7	8	6	5	4
7	8	8	9	7	6	5
8	9	9	10	8	7	6
9	10	10	11	9	8	7
10	11	11	NULL	10	9	8
11	NULL	NULL	NULL	11	10	9
select pk,
lag(pk)    over (order by pk),
lag(pk, 1) over (order by pk),
lag(pk, 2) over (order by pk),
lag(pk, 0) over (order by pk),
lag(pk, -1) over (order by pk),
lag(pk, -2) over (order by pk)
from t1
order by pk asc;
pk	lag(pk)    over (order by pk)	lag(pk, 1) over (order by pk)	lag(pk, 2) over (order by pk)	lag(pk, 0) over (order by pk)	lag(pk, -1) over (order by pk)	lag(pk, -2) over (order by pk)
1	NULL	NULL	NULL	1	2	3
2	1	1	NULL	2	3	4
3	2	2	1	3	4	5
4	3	3	2	4	5	6
5	4	4	3	5	6	7
6	5	5	4	6	7	8
7	6	6	5	7	8	9
8	7	7	6	8	9	10
9	8	8	7	9	10	11
10	9	9	8	10	11	NULL
11	10	10	9	11	NULL	NULL
select pk, pk - 2,
lag(pk, pk - 2) over (order by pk),
lead(pk, pk - 2) over (order by pk)
from t1
order by pk asc;
pk	pk - 2	lag(pk, pk - 2) over (order by pk)	lead(pk, pk - 2) over (order by pk)
1	-1	2	NULL
2	0	2	2
3	1	2	4
4	2	2	6
5	3	2	8
6	4	2	10
7	5	2	NULL
8	6	2	NULL
9	7	2	NULL
10	8	2	NULL
11	9	2	NULL
select pk, pk - 2,
lag(pk, pk + 2) over (order by pk),
lead(pk, pk + 2) over (order by pk)
from t1
order by pk asc;
pk	pk - 2	lag(pk, pk + 2) over (order by pk)	lead(pk, pk + 2) over (order by pk)
1	-1	NULL	4
2	0	NULL	6
3	1	NULL	8
4	2	NULL	10
5	3	NULL	NULL
6	4	NULL	NULL
7	5	NULL	NULL
8	6	NULL	NULL
9	7	NULL	NULL
10	8	NULL	NULL
11	9	NULL	NULL
select pk, a,
lead(pk)    over (partition by a order by pk),
lead(pk, 1) over (partition by a order by pk),
lead(pk, 2) over (partition by a order by pk),
lead(pk, 0) over (partition by a order by pk),
lead(pk, -1) over (partition by a order by pk),
lead(pk, -2) over (partition by a order by pk)
from t1
order by pk asc;
pk	a	lead(pk)    over (partition by a order by pk)	lead(pk, 1) over (partition by a order by pk)	lead(pk, 2) over (partition by a order by pk)	lead(pk, 0) over (partition by a order by pk)	lead(pk, -1) over (partition by a order by pk)	lead(pk, -2) over (partition by a order by pk)
1	0	2	2	3	1	NULL	NULL
2	0	3	3	NULL	2	1	NULL
3	0	NULL	NULL	NULL	3	2	1
4	1	5	5	6	4	NULL	NULL
5	1	6	6	NULL	5	4	NULL
6	1	NULL	NULL	NULL	6	5	4
7	2	8	8	9	7	NULL	NULL
8	2	9	9	10	8	7	NULL
9	2	10	10	11	9	8	7
10	2	11	11	NULL	10	9	8
11	2	NULL	NULL	NULL	11	10	9
select pk, a,
lag(pk)    over (partition by a order by pk),
lag(pk, 1) over (partition by a order by pk),
lag(pk, 2) over (partition by a order by pk),
lag(pk, 0) over (partition by a order by pk),
lag(pk, -1) over (partition by a order by pk),
lag(pk, -2) over (partition by a order by pk)
from t1
order by pk asc;
pk	a	lag(pk)    over (partition by a order by pk)	lag(pk, 1) over (partition by a order by pk)	lag(pk, 2) over (partition by a order by pk)	lag(pk, 0) over (partition by a order by pk)	lag(pk, -1) over (partition by a order by pk)	lag(pk, -2) over (partition by a order by pk)
1	0	NULL	NULL	NULL	1	2	3
2	0	1	1	NULL	2	3	NULL
3	0	2	2	1	3	NULL	NULL
4	1	NULL	NULL	NULL	4	5	6
5	1	4	4	NULL	5	6	NULL
6	1	5	5	4	6	NULL	NULL
7	2	NULL	NULL	NULL	7	8	9
8	2	7	7	NULL	8	9	10
9	2	8	8	7	9	10	11
10	2	9	9	8	10	11	NULL
11	2	10	10	9	11	NULL	NULL
select pk, a, pk - 2,
lag(pk, pk - 2) over (partition by a order by pk),
lead(pk, pk - 2) over (partition by a order by pk),
lag(pk, a - 2) over (partition by a order by pk),
lead(pk, a - 2) over (partition by a order by pk)
from t1
order by pk asc;
pk	a	pk - 2	lag(pk, pk - 2) over (partition by a order by pk)	lead(pk, pk - 2) over (partition by a order by pk)	lag(pk, a - 2) over (partition by a order by pk)	lead(pk, a - 2) over (partition by a order by pk)
1	0	-1	2	NULL	3	NULL
2	0	0	2	2	NULL	NULL
3	0	1	2	NULL	NULL	1
4	1	2	NULL	6	5	NULL
5	1	3	NULL	NULL	6	4
6	1	4	NULL	NULL	NULL	5
7	2	5	NULL	NULL	7	7
8	2	6	NULL	NULL	8	8
9	2	7	NULL	NULL	9	9
10	2	8	NULL	NULL	10	10
11	2	9	NULL	NULL	11	11
select pk, a, pk - 2,
lag(pk, pk + 2) over (partition by a order by pk),
lead(pk, pk + 2) over (partition by a order by pk),
lag(pk, a + 2) over (partition by a order by pk),
lead(pk, a + 2) over (partition by a order by pk)
from t1
order by pk asc;
pk	a	pk - 2	lag(pk, pk + 2) over (partition by a order by pk)	lead(pk, pk + 2) over (partition by a order by pk)	lag(pk, a + 2) over (partition by a order by pk)	lead(pk, a + 2) over (partition by a order by pk)
1	0	-1	NULL	NULL	NULL	3
2	0	0	NULL	NULL	NULL	NULL
3	0	1	NULL	NULL	1	NULL
4	1	2	NULL	NULL	NULL	NULL
5	1	3	NULL	NULL	NULL	NULL
6	1	4	NULL	NULL	NULL	NULL
7	2	5	NULL	NULL	NULL	11
8	2	6	NULL	NULL	NULL	NULL
9	2	7	NULL	NULL	NULL	NULL
10	2	8	NULL	NULL	NULL	NULL
11	2	9	NULL	NULL	7	NULL
select pk, a, b, c, d, e,
lag(a) over (partition by a order by pk),
lag(b) over (partition by a order by pk),
lag(c) over (partition by a order by pk),
lag(d) over (partition by a order by pk),
lag(e) over (partition by a order by pk)
from t1
order by pk asc;
pk	a	b	c	d	e	lag(a) over (partition by a order by pk)	lag(b) over (partition by a order by pk)	lag(c) over (partition by a order by pk)	lag(d) over (partition by a order by pk)	lag(e) over (partition by a order by pk)
1	0	1	one	0.100	0.001	NULL	NULL	NULL	NULL	NULL
2	0	2	two	0.200	0.002	0	1	one	0.100	0.001
3	0	3	three	0.300	0.003	0	2	two	0.200	0.002
4	1	2	three	0.400	0.004	NULL	NULL	NULL	NULL	NULL
5	1	1	two	0.500	0.005	1	2	three	0.400	0.004
6	1	1	one	0.600	0.006	1	1	two	0.500	0.005
7	2	NULL	n_one	0.500	0.007	NULL	NULL	NULL	NULL	NULL
8	2	1	n_two	NULL	0.008	2	NULL	n_one	0.500	0.007
9	2	2	NULL	0.700	0.009	2	1	n_two	NULL	0.008
10	2	0	n_four	0.800	0.01	2	2	NULL	0.700	0.009
11	2	10	NULL	0.900	NULL	2	0	n_four	0.800	0.01
select pk, a, b, a+b,
lag(a + b) over (partition by a order by pk)
from t1
order by pk asc;
pk	a	b	a+b	lag(a + b) over (partition by a order by pk)
1	0	1	1	NULL
2	0	2	2	1
3	0	3	3	2
4	1	2	3	NULL
5	1	1	2	3
6	1	1	2	2
7	2	NULL	NULL	NULL
8	2	1	3	NULL
9	2	2	4	3
10	2	0	2	4
11	2	10	12	2
select pk, a, b, a+b,
lag(a + b) over (partition by a order by pk) + pk
from t1
order by pk asc;
pk	a	b	a+b	lag(a + b) over (partition by a order by pk) + pk
1	0	1	1	NULL
2	0	2	2	3
3	0	3	3	5
4	1	2	3	NULL
5	1	1	2	8
6	1	1	2	8
7	2	NULL	NULL	NULL
8	2	1	3	NULL
9	2	2	4	12
10	2	0	2	14
11	2	10	12	13
#
# MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause
#
select pk,
lag(pk, 1) over ()
from t1;
ERROR HY000: No order list in window specification for 'lag'
select pk,
lead(pk, 1) over ()
from t1;
ERROR HY000: No order list in window specification for 'lead'
drop table t1;