EVOLUTION-MANAGER

Edit File: win_lead_lag.result

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;