[Commits] fb14380: Add results for first_value and last_value test case

Vicentiu Ciorbaru vicentiu at mariadb.org
Tue Sep 20 23:18:17 EEST 2016


revision-id: fb1438025c051a6f07488bf9887ece3dc8dc0127 (mariadb-10.1.8-275-gfb14380)
parent(s): 333523663602658da8c7a98dc7a43527e215d509
author: Vicențiu Ciorbaru
committer: Vicențiu Ciorbaru
timestamp: 2016-09-20 22:18:06 +0200
message:

Add results for first_value and last_value test case

---
 mysql-test/r/win_first_last_value.result | 75 ++++++++++++++++++++++++++++++++
 1 file changed, 75 insertions(+)

diff --git a/mysql-test/r/win_first_last_value.result b/mysql-test/r/win_first_last_value.result
new file mode 100644
index 0000000..3bcab5e
--- /dev/null
+++ b/mysql-test/r/win_first_last_value.result
@@ -0,0 +1,75 @@
+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, first_value(pk) over (order by pk),
+last_value(pk) over (order by pk)
+from t1
+order by pk desc;
+pk	first_value(pk) over (order by pk)	last_value(pk) over (order by pk)
+11	1	11
+10	1	10
+9	1	9
+8	1	8
+7	1	7
+6	1	6
+5	1	5
+4	1	4
+3	1	3
+2	1	2
+1	1	1
+select pk,
+first_value(pk) over (order by pk desc),
+last_value(pk) over (order by pk desc)
+from t1;
+pk	first_value(pk) over (order by pk desc)	last_value(pk) over (order by pk desc)
+1	11	1
+2	11	2
+3	11	3
+4	11	4
+5	11	5
+6	11	6
+7	11	7
+8	11	8
+9	11	9
+10	11	10
+11	11	11
+select pk, a, b, c, d, e,
+first_value(b) over (partition by a order by pk) as fst_b,
+last_value(b) over (partition by a order by pk) as lst_b,
+first_value(c) over (partition by a order by pk) as fst_c,
+last_value(c) over (partition by a order by pk) as lst_c,
+first_value(d) over (partition by a order by pk) as fst_d,
+last_value(d) over (partition by a order by pk) as lst_d,
+first_value(e) over (partition by a order by pk) as fst_e,
+last_value(e) over (partition by a order by pk) as lst_e
+from t1;
+pk	a	b	c	d	e	fst_b	lst_b	fst_c	lst_c	fst_d	lst_d	fst_e	lst_e
+1	0	1	one	0.100	0.001	1	1	one	one	0.100	0.100	0.001	0.001
+2	0	2	two	0.200	0.002	1	2	one	two	0.100	0.200	0.001	0.002
+3	0	3	three	0.300	0.003	1	3	one	three	0.100	0.300	0.001	0.003
+4	1	2	three	0.400	0.004	2	2	three	three	0.400	0.400	0.004	0.004
+5	1	1	two	0.500	0.005	2	1	three	two	0.400	0.500	0.004	0.005
+6	1	1	one	0.600	0.006	2	1	three	one	0.400	0.600	0.004	0.006
+7	2	NULL	n_one	0.500	0.007	NULL	NULL	n_one	n_one	0.500	0.500	0.007	0.007
+8	2	1	n_two	NULL	0.008	NULL	1	n_one	n_two	0.500	NULL	0.007	0.008
+9	2	2	NULL	0.700	0.009	NULL	2	n_one	NULL	0.500	0.700	0.007	0.009
+10	2	0	n_four	0.800	0.01	NULL	0	n_one	n_four	0.500	0.800	0.007	0.01
+11	2	10	NULL	0.900	NULL	NULL	10	n_one	NULL	0.500	0.900	0.007	NULL
+drop table t1;


More information about the commits mailing list