diff options
Diffstat (limited to 'mysql-test/main/win_std.result')
| -rw-r--r-- | mysql-test/main/win_std.result | 293 |
1 files changed, 293 insertions, 0 deletions
diff --git a/mysql-test/main/win_std.result b/mysql-test/main/win_std.result new file mode 100644 index 00000000000..36d99eec425 --- /dev/null +++ b/mysql-test/main/win_std.result @@ -0,0 +1,293 @@ +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) +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; |