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