Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 240 insertions, 0 deletions

diff --git a/mysql-test/main/win_lead_lag.result b/mysql-test/main/win_lead_lag.result
new file mode 100644
index 00000000..f3c0b8f7
--- /dev/null
+++ b/mysql-test/main/win_lead_lag.result
@@ -0,0 +1,240 @@
+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,
+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)
+from t1
+order by pk asc;
+pk	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)
+1	2	2	3	1	NULL	NULL
+2	3	3	4	2	1	NULL
+3	4	4	5	3	2	1
+4	5	5	6	4	3	2
+5	6	6	7	5	4	3
+6	7	7	8	6	5	4
+7	8	8	9	7	6	5
+8	9	9	10	8	7	6
+9	10	10	11	9	8	7
+10	11	11	NULL	10	9	8
+11	NULL	NULL	NULL	11	10	9
+select pk,
+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)
+from t1
+order by pk asc;
+pk	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)
+1	NULL	NULL	NULL	1	2	3
+2	1	1	NULL	2	3	4
+3	2	2	1	3	4	5
+4	3	3	2	4	5	6
+5	4	4	3	5	6	7
+6	5	5	4	6	7	8
+7	6	6	5	7	8	9
+8	7	7	6	8	9	10
+9	8	8	7	9	10	11
+10	9	9	8	10	11	NULL
+11	10	10	9	11	NULL	NULL
+select pk, pk - 2,
+lag(pk, pk - 2) over (order by pk),
+lead(pk, pk - 2) over (order by pk)
+from t1
+order by pk asc;
+pk	pk - 2	lag(pk, pk - 2) over (order by pk)	lead(pk, pk - 2) over (order by pk)
+1	-1	2	NULL
+2	0	2	2
+3	1	2	4
+4	2	2	6
+5	3	2	8
+6	4	2	10
+7	5	2	NULL
+8	6	2	NULL
+9	7	2	NULL
+10	8	2	NULL
+11	9	2	NULL
+select pk, pk - 2,
+lag(pk, pk + 2) over (order by pk),
+lead(pk, pk + 2) over (order by pk)
+from t1
+order by pk asc;
+pk	pk - 2	lag(pk, pk + 2) over (order by pk)	lead(pk, pk + 2) over (order by pk)
+1	-1	NULL	4
+2	0	NULL	6
+3	1	NULL	8
+4	2	NULL	10
+5	3	NULL	NULL
+6	4	NULL	NULL
+7	5	NULL	NULL
+8	6	NULL	NULL
+9	7	NULL	NULL
+10	8	NULL	NULL
+11	9	NULL	NULL
+select pk, 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)
+from t1
+order by pk asc;
+pk	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)
+1	0	2	2	3	1	NULL	NULL
+2	0	3	3	NULL	2	1	NULL
+3	0	NULL	NULL	NULL	3	2	1
+4	1	5	5	6	4	NULL	NULL
+5	1	6	6	NULL	5	4	NULL
+6	1	NULL	NULL	NULL	6	5	4
+7	2	8	8	9	7	NULL	NULL
+8	2	9	9	10	8	7	NULL
+9	2	10	10	11	9	8	7
+10	2	11	11	NULL	10	9	8
+11	2	NULL	NULL	NULL	11	10	9
+select pk, 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)
+from t1
+order by pk asc;
+pk	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)
+1	0	NULL	NULL	NULL	1	2	3
+2	0	1	1	NULL	2	3	NULL
+3	0	2	2	1	3	NULL	NULL
+4	1	NULL	NULL	NULL	4	5	6
+5	1	4	4	NULL	5	6	NULL
+6	1	5	5	4	6	NULL	NULL
+7	2	NULL	NULL	NULL	7	8	9
+8	2	7	7	NULL	8	9	10
+9	2	8	8	7	9	10	11
+10	2	9	9	8	10	11	NULL
+11	2	10	10	9	11	NULL	NULL
+select pk, 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)
+from t1
+order by pk asc;
+pk	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)
+1	0	-1	2	NULL	3	NULL
+2	0	0	2	2	NULL	NULL
+3	0	1	2	NULL	NULL	1
+4	1	2	NULL	6	5	NULL
+5	1	3	NULL	NULL	6	4
+6	1	4	NULL	NULL	NULL	5
+7	2	5	NULL	NULL	7	7
+8	2	6	NULL	NULL	8	8
+9	2	7	NULL	NULL	9	9
+10	2	8	NULL	NULL	10	10
+11	2	9	NULL	NULL	11	11
+select pk, 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)
+from t1
+order by pk asc;
+pk	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)
+1	0	-1	NULL	NULL	NULL	3
+2	0	0	NULL	NULL	NULL	NULL
+3	0	1	NULL	NULL	1	NULL
+4	1	2	NULL	NULL	NULL	NULL
+5	1	3	NULL	NULL	NULL	NULL
+6	1	4	NULL	NULL	NULL	NULL
+7	2	5	NULL	NULL	NULL	11
+8	2	6	NULL	NULL	NULL	NULL
+9	2	7	NULL	NULL	NULL	NULL
+10	2	8	NULL	NULL	NULL	NULL
+11	2	9	NULL	NULL	7	NULL
+select pk, 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)
+from t1
+order by pk asc;
+pk	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)
+1	0	1	one	0.100	0.001	NULL	NULL	NULL	NULL	NULL
+2	0	2	two	0.200	0.002	0	1	one	0.100	0.001
+3	0	3	three	0.300	0.003	0	2	two	0.200	0.002
+4	1	2	three	0.400	0.004	NULL	NULL	NULL	NULL	NULL
+5	1	1	two	0.500	0.005	1	2	three	0.400	0.004
+6	1	1	one	0.600	0.006	1	1	two	0.500	0.005
+7	2	NULL	n_one	0.500	0.007	NULL	NULL	NULL	NULL	NULL
+8	2	1	n_two	NULL	0.008	2	NULL	n_one	0.500	0.007
+9	2	2	NULL	0.700	0.009	2	1	n_two	NULL	0.008
+10	2	0	n_four	0.800	0.01	2	2	NULL	0.700	0.009
+11	2	10	NULL	0.900	NULL	2	0	n_four	0.800	0.01
+select pk, a, b, a+b,
+lag(a + b) over (partition by a order by pk)
+from t1
+order by pk asc;
+pk	a	b	a+b	lag(a + b) over (partition by a order by pk)
+1	0	1	1	NULL
+2	0	2	2	1
+3	0	3	3	2
+4	1	2	3	NULL
+5	1	1	2	3
+6	1	1	2	2
+7	2	NULL	NULL	NULL
+8	2	1	3	NULL
+9	2	2	4	3
+10	2	0	2	4
+11	2	10	12	2
+select pk, a, b, a+b,
+lag(a + b) over (partition by a order by pk) + pk
+from t1
+order by pk asc;
+pk	a	b	a+b	lag(a + b) over (partition by a order by pk) + pk
+1	0	1	1	NULL
+2	0	2	2	3
+3	0	3	3	5
+4	1	2	3	NULL
+5	1	1	2	8
+6	1	1	2	8
+7	2	NULL	NULL	NULL
+8	2	1	3	NULL
+9	2	2	4	12
+10	2	0	2	14
+11	2	10	12	13
+#
+# MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause
+#
+select pk,
+lag(pk, 1) over ()
+from t1;
+ERROR HY000: No order list in window specification for 'lag'
+select pk,
+lead(pk, 1) over ()
+from t1;
+ERROR HY000: No order list in window specification for 'lead'
+drop table t1;