diff options
| -rw-r--r-- | mysql-test/r/win.result | 32 | ||||
| -rw-r--r-- | mysql-test/t/win.test | 27 |
2 files changed, 59 insertions, 0 deletions
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index caa0ddbdcdb..3e085f5ead7 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -1086,3 +1086,35 @@ and 0.1 following) 1.220 2 3.330 1 drop table t1; +# +# RANGE-type frames and NULL values +# +create table t1 ( +pk int, +a int, +b int +); +insert into t1 values (1, NULL,1); +insert into t1 values (2, NULL,1); +insert into t1 values (3, NULL,1); +insert into t1 values (4, 10 ,1); +insert into t1 values (5, 11 ,1); +insert into t1 values (6, 12 ,1); +insert into t1 values (7, 13 ,1); +insert into t1 values (8, 14 ,1); +select +pk, a, +count(b) over (order by a +range between 2 preceding +and 2 following) as CNT +from t1; +pk a CNT +1 NULL 3 +2 NULL 3 +3 NULL 3 +4 10 3 +5 11 4 +6 12 5 +7 13 4 +8 14 3 +drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 875b9bc0f36..44f60f30ced 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -637,3 +637,30 @@ select from t1; drop table t1; + +--echo # +--echo # RANGE-type frames and NULL values +--echo # +create table t1 ( + pk int, + a int, + b int +); + +insert into t1 values (1, NULL,1); +insert into t1 values (2, NULL,1); +insert into t1 values (3, NULL,1); +insert into t1 values (4, 10 ,1); +insert into t1 values (5, 11 ,1); +insert into t1 values (6, 12 ,1); +insert into t1 values (7, 13 ,1); +insert into t1 values (8, 14 ,1); + + +select + pk, a, + count(b) over (order by a + range between 2 preceding + and 2 following) as CNT +from t1; +drop table t1; |