diff options
Diffstat (limited to 'mysql-test/r/win.result')
| -rw-r--r-- | mysql-test/r/win.result | 107 |
1 files changed, 101 insertions, 6 deletions
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index ad99453ea1c..27824a3074e 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -1189,6 +1189,44 @@ pk c cnt 10 2 0 select pk, c, +sum(c) over (partition by c +order by pk +rows between 1 preceding +and 2 preceding) +as sum +from t1; +pk c sum +1 1 NULL +2 1 NULL +3 1 NULL +4 1 NULL +5 2 NULL +6 2 NULL +7 2 NULL +8 2 NULL +9 2 NULL +10 2 NULL +select +pk, c, +sum(c) over (partition by c +order by pk +rows between 2 following +and 1 following) +as sum +from t1; +pk c sum +1 1 NULL +2 1 NULL +3 1 NULL +4 1 NULL +5 2 NULL +6 2 NULL +7 2 NULL +8 2 NULL +9 2 NULL +10 2 NULL +select +pk, c, count(*) over (partition by c order by pk range between 1 preceding @@ -1788,15 +1826,12 @@ rank() over (order by b) 0 0 drop table t1; -# -# MDEV-9894: Assertion `0' failed in Window_func_runner::setup -# return ER_NOT_SUPPORTED_YET for aggregates that are not yet supported -# as window functions. -# create table t1 (i int); insert into t1 values (1),(2); SELECT MAX(i) OVER (PARTITION BY (i)) FROM t1; -ERROR 42000: This version of MariaDB doesn't yet support 'This aggregate as window function' +MAX(i) OVER (PARTITION BY (i)) +1 +2 drop table t1; # # Check the 0 in ROWS 0 PRECEDING @@ -1959,3 +1994,63 @@ select rank() over (order by i) from v1; rank() over (order by i) 1 drop view v1; +# +# MDEV-10097: Assertion `count > 0' failed in Item_sum_sum::add_helper(bool) +# +CREATE TABLE `orders` ( +`o_orderkey` int(11) NOT NULL, +`o_custkey` int(11) DEFAULT NULL, +PRIMARY KEY (`o_orderkey`) +) DEFAULT CHARSET=latin1; +INSERT INTO `orders` VALUES (59908,242); +INSERT INTO `orders` VALUES (59940,238); +SELECT o_custkey, avg(o_custkey) OVER (PARTITION BY abs(o_custkey) +ORDER BY o_custkey +RANGE BETWEEN 15 FOLLOWING +AND 15 FOLLOWING) from orders; +o_custkey avg(o_custkey) OVER (PARTITION BY abs(o_custkey) +ORDER BY o_custkey +RANGE BETWEEN 15 FOLLOWING +AND 15 FOLLOWING) +242 NULL +238 NULL +DROP table orders; +# +# MDEV-10842: window functions with the same order column +# but different directions +# +create table t1 ( +pk int primary key, +a int, +b int, +c char(10) +); +insert into t1 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'), +( 7, 2, NULL, 'n_one'), +( 8, 2, 1, 'n_two'), +( 9, 2, 2, 'n_three'), +(10, 2, 0, 'n_four'), +(11, 2, 10, NULL); +select pk, +row_number() over (order by pk desc) as r_desc, +row_number() over (order by pk asc) as r_asc +from t1; +pk r_desc r_asc +1 11 1 +2 10 2 +3 9 3 +4 8 4 +5 7 5 +6 6 6 +7 5 7 +8 4 8 +9 3 9 +10 2 10 +11 1 11 +drop table t1; |