diff options
Diffstat (limited to 'mysql-test/main/win_sum.result')
| -rw-r--r-- | mysql-test/main/win_sum.result | 107 |
1 files changed, 107 insertions, 0 deletions
diff --git a/mysql-test/main/win_sum.result b/mysql-test/main/win_sum.result new file mode 100644 index 00000000..71d87bd6 --- /dev/null +++ b/mysql-test/main/win_sum.result @@ -0,0 +1,107 @@ +create table t1 ( +pk int primary key, +a int, +b int, +c real +); +insert into t1 values +(101 , 0, 10, 1.1), +(102 , 0, 10, 2.1), +(103 , 1, 10, 3.1), +(104 , 1, 10, 4.1), +(108 , 2, 10, 5.1), +(105 , 2, 20, 6.1), +(106 , 2, 20, 7.1), +(107 , 2, 20, 8.15), +(109 , 4, 20, 9.15), +(110 , 4, 20, 10.15), +(111 , 5, NULL, 11.15), +(112 , 5, 1, 12.25), +(113 , 5, NULL, 13.35), +(114 , 5, NULL, 14.50), +(115 , 5, NULL, 15.65), +(116 , 6, 1, NULL), +(117 , 6, 1, 10), +(118 , 6, 1, 1.1), +(119 , 6, 1, NULL), +(120 , 6, 1, NULL), +(121 , 6, 1, NULL), +(122 , 6, 1, 2.2), +(123 , 6, 1, 20.1), +(124 , 6, 1, -10.4), +(125 , 6, 1, NULL), +(126 , 6, 1, NULL), +(127 , 6, 1, NULL); +select pk, a, b, sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +from t1; +pk a b sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +101 0 10 20 +102 0 10 20 +103 1 10 20 +104 1 10 20 +105 2 20 40 +106 2 20 60 +107 2 20 50 +108 2 10 30 +109 4 20 40 +110 4 20 40 +111 5 NULL 1 +112 5 1 1 +113 5 NULL 1 +114 5 NULL NULL +115 5 NULL NULL +116 6 1 2 +117 6 1 3 +118 6 1 3 +119 6 1 3 +120 6 1 3 +121 6 1 3 +122 6 1 3 +123 6 1 3 +124 6 1 3 +125 6 1 3 +126 6 1 3 +127 6 1 2 +select pk, a, c, sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +from t1; +pk a c sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +101 0 1.1 3.2 +102 0 2.1 3.2 +103 1 3.1 7.199999999999999 +104 1 4.1 7.199999999999999 +105 2 6.1 13.2 +106 2 7.1 21.35 +107 2 8.15 20.35 +108 2 5.1 13.250000000000002 +109 4 9.15 19.3 +110 4 10.15 19.3 +111 5 11.15 23.4 +112 5 12.25 36.75 +113 5 13.35 40.1 +114 5 14.5 43.5 +115 5 15.65 30.15 +116 6 NULL 10 +117 6 10 11.1 +118 6 1.1 11.1 +119 6 NULL 1.0999999999999996 +120 6 NULL NULL +121 6 NULL 2.1999999999999997 +122 6 2.2 22.3 +123 6 20.1 11.9 +124 6 -10.4 9.7 +125 6 NULL -10.400000000000002 +126 6 NULL NULL +127 6 NULL NULL +drop table t1; +# +# End of 10.2 tests +# +# +# MDEV-28094 Window function in expression in ORDER BY +# +SELECT EXISTS (SELECT 1 ORDER BY 1+sum(2) OVER ()); +EXISTS (SELECT 1 ORDER BY 1+sum(2) OVER ()) +1 +# +# End of 10.4 tests +# |