MDEV-10815: Window Function Expressions Wrong Results

Fix window function expressions such as win_func() <operator> expr.
The problem was found in 2 places.
First, when we have complex expressions containing window functions, we
can only compute their final value _after_ we have computed the window
function's values. These values must be stored within the temporary
table that we are using, before sending them off.
This is done by performing an extra copy_funcs call before the final
end_send() call.

Second, such expressions need to have their inner arguments,
changed such that the references within those arguments point to fields within
the temporary table.
Ex: sum(t.a) over (order by t.b) + sum(t.a) over (order by t.b)
Before this fix, t.a pointed to the original table's a field. In order
to compute the sum function's value correctly, it needs to point to the
copy of this field inside the temp table.
This is done by calling split_sum_func for each argument in the
expression in turn.

The win.test results have also been updated as they contained wrong
values for such a use case.

2 files changed, 58 insertions, 12 deletions

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 233308cd031..2b6157bc7bc 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -1815,16 +1815,16 @@ insert into t1 values
 (1,3),
 (2,2),
 (3,1);
-select 
-rank() over (order by a) -
-rank() over (order by b)
+select
+a, b,
+rank() over (order by a), rank() over (order by b),
+rank() over (order by a) - rank() over (order by b) as diff
 from
 t1;
-rank() over (order by a) -
-rank() over (order by b)
-0
-0
-0
+a	b	rank() over (order by a)	rank() over (order by b)	diff
+1	3	1	3	-2
+2	2	2	2	0
+3	1	3	1	2
 drop table t1;
 create table t1 (i int);
 insert into t1 values (1),(2);
@@ -2055,7 +2055,7 @@ pk	r_desc	r_asc
 11	1	11
 drop table t1;
 #
-# MDEV-10874: two window functions with ccompatible sorting 
+# MDEV-10874: two window functions with compatible sorting
 #
 create table t1 (
 pk int primary key,
@@ -2185,3 +2185,29 @@ EXPLAIN
   }
 }
 drop table t1;
+#
+# MDEV-10815: Window Function Expressions Wrong Results
+#
+create table t(a decimal(35,10), b int);
+insert into t(a,b) values(1,1);
+insert into t(a,b) values(2,1);
+insert into t(a,b) values(0,1);
+insert into t(a,b) values(1,  2);
+insert into t(a,b) values(1.5,2);
+insert into t(a,b) values(3,  2);
+insert into t(a,b) values(4.5,2);
+select a, b,
+sum(t.a) over (partition by t.b order by a) as simple_sum,
+sum(t.a) over (partition by t.b order by a) + 1 as sum_and_const,
+sum(t.b) over (partition by t.b order by a) + sum(t.a) over (partition by t.b order by a) as sum_and_sum
+from t
+order by t.b, t.a;
+a	b	simple_sum	sum_and_const	sum_and_sum
+0.0000000000	1	0.0000000000	1.0000000000	1.0000000000
+1.0000000000	1	1.0000000000	2.0000000000	3.0000000000
+2.0000000000	1	3.0000000000	4.0000000000	6.0000000000
+1.0000000000	2	1.0000000000	2.0000000000	3.0000000000
+1.5000000000	2	2.5000000000	3.5000000000	6.5000000000
+3.0000000000	2	5.5000000000	6.5000000000	11.5000000000
+4.5000000000	2	10.0000000000	11.0000000000	18.0000000000
+drop table t;
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 60bd42e026a..7490948ddd2 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -1099,9 +1099,10 @@ insert into t1 values
   (2,2),
   (3,1);
 
-select 
-  rank() over (order by a) -
-  rank() over (order by b)
+select
+  a, b,
+  rank() over (order by a), rank() over (order by b),
+  rank() over (order by a) - rank() over (order by b) as diff
 from
   t1;
 
@@ -1337,3 +1338,22 @@ select
 from t1;
 
 drop table t1;
+
+--echo #
+--echo # MDEV-10815: Window Function Expressions Wrong Results
+--echo #
+create table t(a decimal(35,10), b int);
+insert into t(a,b) values(1,1);
+insert into t(a,b) values(2,1);
+insert into t(a,b) values(0,1);
+insert into t(a,b) values(1,  2);
+insert into t(a,b) values(1.5,2);
+insert into t(a,b) values(3,  2);
+insert into t(a,b) values(4.5,2);
+select a, b,
+       sum(t.a) over (partition by t.b order by a) as simple_sum,
+       sum(t.a) over (partition by t.b order by a) + 1 as sum_and_const,
+       sum(t.b) over (partition by t.b order by a) + sum(t.a) over (partition by t.b order by a) as sum_and_sum
+from t
+order by t.b, t.a;
+drop table t;