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, first_value(pk) over (order by pk), 22last_value(pk) over (order by pk), 23first_value(pk) over (order by pk desc), 24last_value(pk) over (order by pk desc) 25from t1 26order by pk desc; 27pk first_value(pk) over (order by pk) last_value(pk) over (order by pk) first_value(pk) over (order by pk desc) last_value(pk) over (order by pk desc) 2811 1 11 11 11 2910 1 10 11 10 309 1 9 11 9 318 1 8 11 8 327 1 7 11 7 336 1 6 11 6 345 1 5 11 5 354 1 4 11 4 363 1 3 11 3 372 1 2 11 2 381 1 1 11 1 39select pk, 40first_value(pk) over (order by pk 41RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 42last_value(pk) over (order by pk 43RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 44first_value(pk) over (order by pk desc 45RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 46last_value(pk) over (order by pk desc 47RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 48from t1 49order by pk; 50pk first_value(pk) over (order by pk 51RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_value(pk) over (order by pk 52RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_value(pk) over (order by pk desc 53RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_value(pk) over (order by pk desc 54RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 551 1 11 11 1 562 1 11 11 1 573 1 11 11 1 584 1 11 11 1 595 1 11 11 1 606 1 11 11 1 617 1 11 11 1 628 1 11 11 1 639 1 11 11 1 6410 1 11 11 1 6511 1 11 11 1 66select pk, 67first_value(pk) over (order by pk desc), 68last_value(pk) over (order by pk desc) 69from t1; 70pk first_value(pk) over (order by pk desc) last_value(pk) over (order by pk desc) 7111 11 11 7210 11 10 739 11 9 748 11 8 757 11 7 766 11 6 775 11 5 784 11 4 793 11 3 802 11 2 811 11 1 82select pk, a, b, c, d, e, 83first_value(b) over (partition by a order by pk) as fst_b, 84last_value(b) over (partition by a order by pk) as lst_b, 85first_value(c) over (partition by a order by pk) as fst_c, 86last_value(c) over (partition by a order by pk) as lst_c, 87first_value(d) over (partition by a order by pk) as fst_d, 88last_value(d) over (partition by a order by pk) as lst_d, 89first_value(e) over (partition by a order by pk) as fst_e, 90last_value(e) over (partition by a order by pk) as lst_e 91from t1; 92pk a b c d e fst_b lst_b fst_c lst_c fst_d lst_d fst_e lst_e 931 0 1 one 0.100 0.001 1 1 one one 0.100 0.100 0.001 0.001 942 0 2 two 0.200 0.002 1 2 one two 0.100 0.200 0.001 0.002 953 0 3 three 0.300 0.003 1 3 one three 0.100 0.300 0.001 0.003 964 1 2 three 0.400 0.004 2 2 three three 0.400 0.400 0.004 0.004 975 1 1 two 0.500 0.005 2 1 three two 0.400 0.500 0.004 0.005 986 1 1 one 0.600 0.006 2 1 three one 0.400 0.600 0.004 0.006 997 2 NULL n_one 0.500 0.007 NULL NULL n_one n_one 0.500 0.500 0.007 0.007 1008 2 1 n_two NULL 0.008 NULL 1 n_one n_two 0.500 NULL 0.007 0.008 1019 2 2 NULL 0.700 0.009 NULL 2 n_one NULL 0.500 0.700 0.007 0.009 10210 2 0 n_four 0.800 0.01 NULL 0 n_one n_four 0.500 0.800 0.007 0.01 10311 2 10 NULL 0.900 NULL NULL 10 n_one NULL 0.500 0.900 0.007 NULL 104drop table t1; 105# 106# MDEV-11746: Wrong result upon using FIRST_VALUE with a window frame 107# 108create table t1 (i int); 109insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 110select i, 111first_value(i) OVER (order by i rows between CURRENT ROW and 1 FOLLOWING) as fst_1f, 112last_value(i) OVER (order by i rows between CURRENT ROW and 1 FOLLOWING) as last_1f, 113first_value(i) OVER (order by i rows between 1 PRECEDING AND 1 FOLLOWING) as fst_1p1f, 114last_value(i) OVER (order by i rows between 1 PRECEDING AND 1 FOLLOWING) as fst_1p1f, 115first_value(i) OVER (order by i rows between 2 PRECEDING AND 1 PRECEDING) as fst_2p1p, 116last_value(i) OVER (order by i rows between 2 PRECEDING AND 1 PRECEDING) as fst_2p1p, 117first_value(i) OVER (order by i rows between 1 FOLLOWING AND 2 FOLLOWING) as fst_1f2f, 118last_value(i) OVER (order by i rows between 1 FOLLOWING AND 2 FOLLOWING) as fst_1f2f 119from t1; 120i fst_1f last_1f fst_1p1f fst_1p1f fst_2p1p fst_2p1p fst_1f2f fst_1f2f 1211 1 2 1 2 NULL NULL 2 3 1222 2 3 1 3 1 1 3 4 1233 3 4 2 4 1 2 4 5 1244 4 5 3 5 2 3 5 6 1255 5 6 4 6 3 4 6 7 1266 6 7 5 7 4 5 7 8 1277 7 8 6 8 5 6 8 9 1288 8 9 7 9 6 7 9 10 1299 9 10 8 10 7 8 10 10 13010 10 10 9 10 8 9 NULL NULL 131drop table t1; 132# 133# MDEV-12861 FIRST_VALUE() does not preserve the exact data type 134# 135CREATE TABLE t1 (a INT, b INT, c FLOAT); 136INSERT INTO t1 VALUES (1,1,1),(1,2,2),(2,1,1),(2,2,2); 137CREATE TABLE t2 AS SELECT a, FIRST_VALUE(b) OVER(), FIRST_VALUE(c) OVER() FROM t1 GROUP BY a; 138SHOW CREATE TABLE t2; 139Table Create Table 140t2 CREATE TABLE `t2` ( 141 `a` int(11) DEFAULT NULL, 142 `FIRST_VALUE(b) OVER()` int(11) DEFAULT NULL, 143 `FIRST_VALUE(c) OVER()` float DEFAULT NULL 144) ENGINE=MyISAM DEFAULT CHARSET=latin1 145DROP TABLE t2,t1; 146