[Commits] 5c87314: Add test case for STD function used as window function

Vicentiu Ciorbaru vicentiu at mariadb.org
Tue Sep 20 13:19:39 EEST 2016


revision-id: 5c87314473152f221d6851ce2f5af82362bed4d9 (mariadb-10.1.8-273-g5c87314)
parent(s): fad484975f622f2316121b8b6cc790745547b734
author: Vicențiu Ciorbaru
committer: Vicențiu Ciorbaru
timestamp: 2016-09-20 12:18:35 +0200
message:

Add test case for STD function used as window function

The test case also checks correct functionality of Frame_scan_cursor, as
currently STD does not implement the remove() call.

---
 mysql-test/r/win_std.result | 321 ++++++++++++++++++++++++++++++++++++++++++++
 mysql-test/t/win_std.test   | 134 ++++++++++++++++++
 2 files changed, 455 insertions(+)

diff --git a/mysql-test/r/win_std.result b/mysql-test/r/win_std.result
new file mode 100644
index 0000000..579f534
--- /dev/null
+++ b/mysql-test/r/win_std.result
@@ -0,0 +1,321 @@
+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
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'one       '
+Warning	1292	Truncated incorrect DOUBLE value: 'two       '
+Warning	1292	Truncated incorrect DOUBLE value: 'three     '
+Warning	1292	Truncated incorrect DOUBLE value: 'one       '
+Warning	1292	Truncated incorrect DOUBLE value: 'two       '
+Warning	1292	Truncated incorrect DOUBLE value: 'three     '
+Warning	1292	Truncated incorrect DOUBLE value: 'one       '
+Warning	1292	Truncated incorrect DOUBLE value: 'two       '
+Warning	1292	Truncated incorrect DOUBLE value: 'three     '
+Warning	1292	Truncated incorrect DOUBLE value: 'one       '
+Warning	1292	Truncated incorrect DOUBLE value: 'two       '
+Warning	1292	Truncated incorrect DOUBLE value: 'three     '
+Warning	1292	Truncated incorrect DOUBLE value: 'one       '
+Warning	1292	Truncated incorrect DOUBLE value: 'two       '
+Warning	1292	Truncated incorrect DOUBLE value: 'three     '
+Warning	1292	Truncated incorrect DOUBLE value: 'one       '
+Warning	1292	Truncated incorrect DOUBLE value: 'two       '
+Warning	1292	Truncated incorrect DOUBLE value: 'three     '
+Warning	1292	Truncated incorrect DOUBLE value: 'one       '
+Warning	1292	Truncated incorrect DOUBLE value: 'two       '
+Warning	1292	Truncated incorrect DOUBLE value: 'three     '
+Warning	1292	Truncated incorrect DOUBLE value: 'one       '
+Warning	1292	Truncated incorrect DOUBLE value: 'two       '
+Warning	1292	Truncated incorrect DOUBLE value: 'three     '
+Warning	1292	Truncated incorrect DOUBLE value: 'one       '
+Warning	1292	Truncated incorrect DOUBLE value: 'two       '
+Warning	1292	Truncated incorrect DOUBLE value: 'three     '
+# Empty frame.
+select std(b) over (order by a rows between 2 following and 1 following)
+from t2;
+std(b) over (order by a rows between 2 following and 1 following)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 2 following and 1 following)
+from t2;
+std(b) over (order by a range between 2 following and 1 following)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a rows between 1 preceding and 2 preceding)
+from t2;
+std(b) over (order by a rows between 1 preceding and 2 preceding)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 1 preceding and 2 preceding)
+from t2;
+std(b) over (order by a range between 1 preceding and 2 preceding)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a rows between 1 following and 0 following)
+from t2;
+std(b) over (order by a rows between 1 following and 0 following)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 1 following and 0 following)
+from t2;
+std(b) over (order by a range between 1 following and 0 following)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a rows between 1 following and 0 preceding)
+from t2;
+std(b) over (order by a rows between 1 following and 0 preceding)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 1 following and 0 preceding)
+from t2;
+std(b) over (order by a range between 1 following and 0 preceding)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a rows between 0 following and 1 preceding)
+from t2;
+std(b) over (order by a rows between 0 following and 1 preceding)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+select std(b) over (order by a range between 0 following and 1 preceding)
+from t2;
+std(b) over (order by a range between 0 following and 1 preceding)
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+# 1 row frame.
+select std(b) over (order by a rows between current row and current row)
+from t2;
+std(b) over (order by a rows between current row and current row)
+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)
+from t2;
+std(b) over (order by a rows between 0 preceding and current row)
+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)
+from t2;
+std(b) over (order by a rows between 0 preceding and 0 preceding)
+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)
+from t2;
+std(b) over (order by a rows between 0 preceding and 0 following)
+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)
+from t2;
+std(b) over (order by a rows between 0 following and 0 preceding)
+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)
+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)
+from t2;
+std(b) over (order by a rows between current row and 0 following)
+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)
+from t2;
+a	b	std(b) over (order by a range between 0 preceding and 0 preceding)
+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)
+from t2;
+a	b	std(b) over (order by a range between 0 preceding and current row)
+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)
+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)
+from t2;
+a	b	std(b) over (order by a range between current row and 0 following)
+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)
+from t2;
+a	b	std(b) over (order by a range between 0 following and 0 following)
+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)
+from t2;
+pk	a	b	std(b) over (order by a, b, pk rows between 1 preceding and current row)
+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)
+from t2;
+pk	a	b	std(b) over (order by a, b, pk rows between 1 preceding and 0 preceding)
+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)
+from t2;
+pk	a	b	std(b) over (order by a, b, pk rows between current row and 1 following)
+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)
+from t2;
+pk	a	b	std(b) over (order by a, b, pk rows between 0 following and 1 following)
+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)
+from t2;
+pk	a	b	std(b) over (order by a range between 1 preceding and current row)
+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)
+from t2;
+pk	a	b	std(b) over (order by a range between 1 preceding and 0 preceding)
+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)
+from t2;
+pk	a	b	std(b) over (order by a range between current row and 1 following)
+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)
+from t2;
+pk	a	b	std(b) over (order by a range between 0 following and 1 following)
+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;
diff --git a/mysql-test/t/win_std.test b/mysql-test/t/win_std.test
new file mode 100644
index 0000000..e1f32dd
--- /dev/null
+++ b/mysql-test/t/win_std.test
@@ -0,0 +1,134 @@
+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');
+
+--echo # First try some invalid queries.
+select std(c) over (order by a)
+from t2;
+
+--echo # Empty frame.
+select std(b) over (order by a rows between 2 following and 1 following)
+from t2;
+
+select std(b) over (order by a range between 2 following and 1 following)
+from t2;
+
+select std(b) over (order by a rows between 1 preceding and 2 preceding)
+from t2;
+
+select std(b) over (order by a range between 1 preceding and 2 preceding)
+from t2;
+
+select std(b) over (order by a rows between 1 following and 0 following)
+from t2;
+
+select std(b) over (order by a range between 1 following and 0 following)
+from t2;
+
+select std(b) over (order by a rows between 1 following and 0 preceding)
+from t2;
+
+select std(b) over (order by a range between 1 following and 0 preceding)
+from t2;
+
+select std(b) over (order by a rows between 0 following and 1 preceding)
+from t2;
+
+select std(b) over (order by a range between 0 following and 1 preceding)
+from t2;
+
+--echo # 1 row frame.
+select std(b) over (order by a rows between current row and current row)
+from t2;
+
+select std(b) over (order by a rows between 0 preceding and current row)
+from t2;
+
+select std(b) over (order by a rows between 0 preceding and 0 preceding)
+from t2;
+
+select std(b) over (order by a rows between 0 preceding and 0 following)
+from t2;
+
+select std(b) over (order by a rows between 0 following and 0 preceding)
+from t2;
+
+--error ER_BAD_COMBINATION_OF_WINDOW_FRAME_BOUND_SPECS
+select std(b) over (order by a rows between 0 following and current row)
+from t2;
+
+select std(b) over (order by a rows between current row and 0 following)
+from t2;
+
+--echo # Only peers frame.
+select a, b, std(b) over (order by a range between 0 preceding and 0 preceding)
+from t2;
+
+select a, b, std(b) over (order by a range between 0 preceding and current row)
+from t2;
+
+--error ER_BAD_COMBINATION_OF_WINDOW_FRAME_BOUND_SPECS
+select a, b, std(b) over (order by a range between current row and 0 preceding)
+from t2;
+
+select a, b, std(b) over (order by a range between current row and 0 following)
+from t2;
+
+select a, b, std(b) over (order by a range between 0 following and 0 following)
+from t2;
+
+--echo # 2 rows frame.
+
+--sorted_result
+select pk, a, b, std(b) over (order by a, b, pk rows between 1 preceding and current row)
+from t2;
+
+--sorted_result
+select pk, a, b, std(b) over (order by a, b, pk rows between 1 preceding and 0 preceding)
+from t2;
+
+--sorted_result
+select pk, a, b, std(b) over (order by a, b, pk rows between current row and 1 following)
+from t2;
+
+--sorted_result
+select pk, a, b, std(b) over (order by a, b, pk rows between 0 following and 1 following)
+from t2;
+
+--echo # 2 peers frame.
+
+--sorted_result
+select pk, a, b, std(b) over (order by a range between 1 preceding and current row)
+from t2;
+
+--sorted_result
+select pk, a, b, std(b) over (order by a range between 1 preceding and 0 preceding)
+from t2;
+
+--sorted_result
+select pk, a, b, std(b) over (order by a range between current row and 1 following)
+from t2;
+
+--sorted_result
+select pk, a, b, std(b) over (order by a range between 0 following and 1 following)
+from t2;
+
+drop table t1;
+drop table t2;


More information about the commits mailing list