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