EVOLUTION-MANAGER

Edit File: win_lead_lag.test

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;

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;

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;

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;

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;

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;

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;

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;

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;

select pk, a, b, a+b,
       lag(a + b) over (partition by a order by pk)
from t1
order by pk asc;

select pk, a, b, a+b,
       lag(a + b) over (partition by a order by pk) + pk
from t1
order by pk asc;

--echo #
--echo # MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause
--echo #
--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
select pk,
       lag(pk, 1) over ()
from t1;

--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
select pk,
       lead(pk, 1) over ()
from t1;

drop table t1;