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, 292 insertions, 0 deletions

diff --git a/mysql-test/main/win_std.result b/mysql-test/main/win_std.result
new file mode 100644
index 00000000..dac8a2d5
--- /dev/null
+++ b/mysql-test/main/win_std.result
@@ -0,0 +1,292 @@
+create table t1 (
+pk int primary key,
+a int,
+b int
+);
+create table t2 (
+pk int primary key,
+a int,
+b int,
+c char(10)
+);
+insert into t2 values
+( 1, 0, 1, 'one'),
+( 2, 0, 2, 'two'),
+( 3, 0, 3, 'three'),
+( 4, 1, 1, 'one'),
+( 5, 1, 1, 'two'),
+( 6, 1, 2, 'three');
+# First try some invalid queries.
+select std(c) over (order by a)
+from t2;
+std(c) over (order by a)
+0
+0
+0
+0
+0
+0
+# Empty frame.
+select std(b) over (order by a rows between 2 following and 1 following) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 2 following and 1 following) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a rows between 1 preceding and 2 preceding) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 1 preceding and 2 preceding) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a rows between 1 following and 0 following) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 1 following and 0 following) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a rows between 1 following and 0 preceding) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 1 following and 0 preceding) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a rows between 0 following and 1 preceding) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 0 following and 1 preceding) as exp
+from t2;
+exp
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+# 1 row frame.
+select std(b) over (order by a rows between current row and current row) as exp
+from t2;
+exp
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+select std(b) over (order by a rows between 0 preceding and current row) as exp
+from t2;
+exp
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+select std(b) over (order by a rows between 0 preceding and 0 preceding) as exp
+from t2;
+exp
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+select std(b) over (order by a rows between 0 preceding and 0 following) as exp
+from t2;
+exp
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+select std(b) over (order by a rows between 0 following and 0 preceding) as exp from t2;
+exp
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+select std(b) over (order by a rows between 0 following and current row) as exp
+from t2;
+ERROR HY000: Unacceptable combination of window frame bound specifications
+select std(b) over (order by a rows between current row and 0 following) as exp
+from t2;
+exp
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+0.0000
+# Only peers frame.
+select a, b, std(b) over (order by a range between 0 preceding and 0 preceding) as exp
+from t2;
+a	b	exp
+0	1	0.8165
+0	2	0.8165
+0	3	0.8165
+1	1	0.4714
+1	1	0.4714
+1	2	0.4714
+select a, b, std(b) over (order by a range between 0 preceding and current row) as exp
+from t2;
+a	b	exp
+0	1	0.8165
+0	2	0.8165
+0	3	0.8165
+1	1	0.4714
+1	1	0.4714
+1	2	0.4714
+select a, b, std(b) over (order by a range between current row and 0 preceding) as exp
+from t2;
+ERROR HY000: Unacceptable combination of window frame bound specifications
+select a, b, std(b) over (order by a range between current row and 0 following) as exp
+from t2;
+a	b	exp
+0	1	0.8165
+0	2	0.8165
+0	3	0.8165
+1	1	0.4714
+1	1	0.4714
+1	2	0.4714
+select a, b, std(b) over (order by a range between 0 following and 0 following) as exp
+from t2;
+a	b	exp
+0	1	0.8165
+0	2	0.8165
+0	3	0.8165
+1	1	0.4714
+1	1	0.4714
+1	2	0.4714
+# 2 rows frame.
+select pk, a, b, std(b) over (order by a, b, pk rows between 1 preceding and current row) as exp
+from t2;
+pk	a	b	exp
+1	0	1	0.0000
+2	0	2	0.5000
+3	0	3	0.5000
+4	1	1	1.0000
+5	1	1	0.0000
+6	1	2	0.5000
+select pk, a, b, std(b) over (order by a, b, pk rows between 1 preceding and 0 preceding) as exp
+from t2;
+pk	a	b	exp
+1	0	1	0.0000
+2	0	2	0.5000
+3	0	3	0.5000
+4	1	1	1.0000
+5	1	1	0.0000
+6	1	2	0.5000
+select pk, a, b, std(b) over (order by a, b, pk rows between current row and 1 following) as exp
+from t2;
+pk	a	b	exp
+1	0	1	0.5000
+2	0	2	0.5000
+3	0	3	1.0000
+4	1	1	0.0000
+5	1	1	0.5000
+6	1	2	0.0000
+select pk, a, b, std(b) over (order by a, b, pk rows between 0 following and 1 following) as exp
+from t2;
+pk	a	b	exp
+1	0	1	0.5000
+2	0	2	0.5000
+3	0	3	1.0000
+4	1	1	0.0000
+5	1	1	0.5000
+6	1	2	0.0000
+# 2 peers frame.
+select pk, a, b, std(b) over (order by a range between 1 preceding and current row) as exp
+from t2;
+pk	a	b	exp
+1	0	1	0.8165
+2	0	2	0.8165
+3	0	3	0.8165
+4	1	1	0.7454
+5	1	1	0.7454
+6	1	2	0.7454
+select pk, a, b, std(b) over (order by a range between 1 preceding and 0 preceding) as exp
+from t2;
+pk	a	b	exp
+1	0	1	0.8165
+2	0	2	0.8165
+3	0	3	0.8165
+4	1	1	0.7454
+5	1	1	0.7454
+6	1	2	0.7454
+select pk, a, b, std(b) over (order by a range between current row and 1 following) as exp
+from t2;
+pk	a	b	exp
+1	0	1	0.7454
+2	0	2	0.7454
+3	0	3	0.7454
+4	1	1	0.4714
+5	1	1	0.4714
+6	1	2	0.4714
+select pk, a, b, std(b) over (order by a range between 0 following and 1 following) as exp
+from t2;
+pk	a	b	exp
+1	0	1	0.7454
+2	0	2	0.7454
+3	0	3	0.7454
+4	1	1	0.4714
+5	1	1	0.4714
+6	1	2	0.4714
+drop table t1;
+drop table t2;