1create table t1 ( 2pk int primary key, 3a int, 4b int, 5c char(10), 6d decimal(10, 3), 7e real 8); 9insert into t1 values 10( 1, 0, 1, 'one', 0.1, 0.001), 11( 2, 0, 2, 'two', 0.2, 0.002), 12( 3, 0, 3, 'three', 0.3, 0.003), 13( 4, 1, 2, 'three', 0.4, 0.004), 14( 5, 1, 1, 'two', 0.5, 0.005), 15( 6, 1, 1, 'one', 0.6, 0.006), 16( 7, 2, NULL, 'n_one', 0.5, 0.007), 17( 8, 2, 1, 'n_two', NULL, 0.008), 18( 9, 2, 2, NULL, 0.7, 0.009), 19(10, 2, 0, 'n_four', 0.8, 0.010), 20(11, 2, 10, NULL, 0.9, NULL); 21select pk, 22lead(pk) over (order by pk), 23lead(pk, 1) over (order by pk), 24lead(pk, 2) over (order by pk), 25lead(pk, 0) over (order by pk), 26lead(pk, -1) over (order by pk), 27lead(pk, -2) over (order by pk) 28from t1 29order by pk asc; 30pk 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) 311 2 2 3 1 NULL NULL 322 3 3 4 2 1 NULL 333 4 4 5 3 2 1 344 5 5 6 4 3 2 355 6 6 7 5 4 3 366 7 7 8 6 5 4 377 8 8 9 7 6 5 388 9 9 10 8 7 6 399 10 10 11 9 8 7 4010 11 11 NULL 10 9 8 4111 NULL NULL NULL 11 10 9 42select pk, 43lag(pk) over (order by pk), 44lag(pk, 1) over (order by pk), 45lag(pk, 2) over (order by pk), 46lag(pk, 0) over (order by pk), 47lag(pk, -1) over (order by pk), 48lag(pk, -2) over (order by pk) 49from t1 50order by pk asc; 51pk 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) 521 NULL NULL NULL 1 2 3 532 1 1 NULL 2 3 4 543 2 2 1 3 4 5 554 3 3 2 4 5 6 565 4 4 3 5 6 7 576 5 5 4 6 7 8 587 6 6 5 7 8 9 598 7 7 6 8 9 10 609 8 8 7 9 10 11 6110 9 9 8 10 11 NULL 6211 10 10 9 11 NULL NULL 63select pk, pk - 2, 64lag(pk, pk - 2) over (order by pk), 65lead(pk, pk - 2) over (order by pk) 66from t1 67order by pk asc; 68pk pk - 2 lag(pk, pk - 2) over (order by pk) lead(pk, pk - 2) over (order by pk) 691 -1 2 NULL 702 0 2 2 713 1 2 4 724 2 2 6 735 3 2 8 746 4 2 10 757 5 2 NULL 768 6 2 NULL 779 7 2 NULL 7810 8 2 NULL 7911 9 2 NULL 80select pk, pk - 2, 81lag(pk, pk + 2) over (order by pk), 82lead(pk, pk + 2) over (order by pk) 83from t1 84order by pk asc; 85pk pk - 2 lag(pk, pk + 2) over (order by pk) lead(pk, pk + 2) over (order by pk) 861 -1 NULL 4 872 0 NULL 6 883 1 NULL 8 894 2 NULL 10 905 3 NULL NULL 916 4 NULL NULL 927 5 NULL NULL 938 6 NULL NULL 949 7 NULL NULL 9510 8 NULL NULL 9611 9 NULL NULL 97select pk, a, 98lead(pk) over (partition by a order by pk), 99lead(pk, 1) over (partition by a order by pk), 100lead(pk, 2) over (partition by a order by pk), 101lead(pk, 0) over (partition by a order by pk), 102lead(pk, -1) over (partition by a order by pk), 103lead(pk, -2) over (partition by a order by pk) 104from t1 105order by pk asc; 106pk 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) 1071 0 2 2 3 1 NULL NULL 1082 0 3 3 NULL 2 1 NULL 1093 0 NULL NULL NULL 3 2 1 1104 1 5 5 6 4 NULL NULL 1115 1 6 6 NULL 5 4 NULL 1126 1 NULL NULL NULL 6 5 4 1137 2 8 8 9 7 NULL NULL 1148 2 9 9 10 8 7 NULL 1159 2 10 10 11 9 8 7 11610 2 11 11 NULL 10 9 8 11711 2 NULL NULL NULL 11 10 9 118select pk, a, 119lag(pk) over (partition by a order by pk), 120lag(pk, 1) over (partition by a order by pk), 121lag(pk, 2) over (partition by a order by pk), 122lag(pk, 0) over (partition by a order by pk), 123lag(pk, -1) over (partition by a order by pk), 124lag(pk, -2) over (partition by a order by pk) 125from t1 126order by pk asc; 127pk 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) 1281 0 NULL NULL NULL 1 2 3 1292 0 1 1 NULL 2 3 NULL 1303 0 2 2 1 3 NULL NULL 1314 1 NULL NULL NULL 4 5 6 1325 1 4 4 NULL 5 6 NULL 1336 1 5 5 4 6 NULL NULL 1347 2 NULL NULL NULL 7 8 9 1358 2 7 7 NULL 8 9 10 1369 2 8 8 7 9 10 11 13710 2 9 9 8 10 11 NULL 13811 2 10 10 9 11 NULL NULL 139select pk, a, pk - 2, 140lag(pk, pk - 2) over (partition by a order by pk), 141lead(pk, pk - 2) over (partition by a order by pk), 142lag(pk, a - 2) over (partition by a order by pk), 143lead(pk, a - 2) over (partition by a order by pk) 144from t1 145order by pk asc; 146pk 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) 1471 0 -1 2 NULL 3 NULL 1482 0 0 2 2 NULL NULL 1493 0 1 2 NULL NULL 1 1504 1 2 NULL 6 5 NULL 1515 1 3 NULL NULL 6 4 1526 1 4 NULL NULL NULL 5 1537 2 5 NULL NULL 7 7 1548 2 6 NULL NULL 8 8 1559 2 7 NULL NULL 9 9 15610 2 8 NULL NULL 10 10 15711 2 9 NULL NULL 11 11 158select pk, a, pk - 2, 159lag(pk, pk + 2) over (partition by a order by pk), 160lead(pk, pk + 2) over (partition by a order by pk), 161lag(pk, a + 2) over (partition by a order by pk), 162lead(pk, a + 2) over (partition by a order by pk) 163from t1 164order by pk asc; 165pk 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) 1661 0 -1 NULL NULL NULL 3 1672 0 0 NULL NULL NULL NULL 1683 0 1 NULL NULL 1 NULL 1694 1 2 NULL NULL NULL NULL 1705 1 3 NULL NULL NULL NULL 1716 1 4 NULL NULL NULL NULL 1727 2 5 NULL NULL NULL 11 1738 2 6 NULL NULL NULL NULL 1749 2 7 NULL NULL NULL NULL 17510 2 8 NULL NULL NULL NULL 17611 2 9 NULL NULL 7 NULL 177select pk, a, b, c, d, e, 178lag(a) over (partition by a order by pk), 179lag(b) over (partition by a order by pk), 180lag(c) over (partition by a order by pk), 181lag(d) over (partition by a order by pk), 182lag(e) over (partition by a order by pk) 183from t1 184order by pk asc; 185pk 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) 1861 0 1 one 0.100 0.001 NULL NULL NULL NULL NULL 1872 0 2 two 0.200 0.002 0 1 one 0.100 0.001 1883 0 3 three 0.300 0.003 0 2 two 0.200 0.002 1894 1 2 three 0.400 0.004 NULL NULL NULL NULL NULL 1905 1 1 two 0.500 0.005 1 2 three 0.400 0.004 1916 1 1 one 0.600 0.006 1 1 two 0.500 0.005 1927 2 NULL n_one 0.500 0.007 NULL NULL NULL NULL NULL 1938 2 1 n_two NULL 0.008 2 NULL n_one 0.500 0.007 1949 2 2 NULL 0.700 0.009 2 1 n_two NULL 0.008 19510 2 0 n_four 0.800 0.01 2 2 NULL 0.700 0.009 19611 2 10 NULL 0.900 NULL 2 0 n_four 0.800 0.01 197select pk, a, b, a+b, 198lag(a + b) over (partition by a order by pk) 199from t1 200order by pk asc; 201pk a b a+b lag(a + b) over (partition by a order by pk) 2021 0 1 1 NULL 2032 0 2 2 1 2043 0 3 3 2 2054 1 2 3 NULL 2065 1 1 2 3 2076 1 1 2 2 2087 2 NULL NULL NULL 2098 2 1 3 NULL 2109 2 2 4 3 21110 2 0 2 4 21211 2 10 12 2 213select pk, a, b, a+b, 214lag(a + b) over (partition by a order by pk) + pk 215from t1 216order by pk asc; 217pk a b a+b lag(a + b) over (partition by a order by pk) + pk 2181 0 1 1 NULL 2192 0 2 2 3 2203 0 3 3 5 2214 1 2 3 NULL 2225 1 1 2 8 2236 1 1 2 8 2247 2 NULL NULL NULL 2258 2 1 3 NULL 2269 2 2 4 12 22710 2 0 2 14 22811 2 10 12 13 229# 230# MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause 231# 232select pk, 233lag(pk, 1) over () 234from t1; 235ERROR HY000: No order list in window specification for 'lag' 236select pk, 237lead(pk, 1) over () 238from t1; 239ERROR HY000: No order list in window specification for 'lead' 240drop table t1; 241