[Commits] d3c6477: MDEV-10815: Window Function Expressions Wrong Results

Vicentiu Ciorbaru vicentiu at mariadb.org
Fri Sep 16 21:58:15 EEST 2016


revision-id: d3c647776ec96c88c283e641c86a77463a2e67eb (mariadb-10.1.8-253-gd3c6477)
parent(s): bbeca48fa331db331fdc8a8fc569ee94e5422e2b
author: Vicențiu Ciorbaru
committer: Vicențiu Ciorbaru
timestamp: 2016-09-16 20:57:35 +0200
message:

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.

---
 mysql-test/r/win.result | 42 ++++++++++++++++++++++++++++++++++--------
 mysql-test/t/win.test   | 26 +++++++++++++++++++++++---
 sql/item.cc             |  4 ++++
 sql/item_windowfunc.h   |  1 +
 sql/sql_select.cc       | 15 +++++++++++++++
 5 files changed, 77 insertions(+), 11 deletions(-)

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index d23e865..ffb068a 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);
@@ -2015,3 +2015,29 @@ AND 15 FOLLOWING)
 242	NULL
 238	NULL
 DROP table orders;
+#
+# 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 2ede130..528ea14 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;
 
@@ -1230,3 +1231,22 @@ SELECT o_custkey, avg(o_custkey) OVER (PARTITION BY abs(o_custkey)
                                        RANGE BETWEEN 15 FOLLOWING
                                                  AND 15 FOLLOWING) from orders;
 DROP table orders;
+
+--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;
diff --git a/sql/item.cc b/sql/item.cc
index d461386..ec4eae2 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1876,7 +1876,11 @@ void Item::split_sum_func2(THD *thd, Ref_ptr_array ref_pointer_array,
       Skip the else part, window functions are very special functions: 
       they need to have their own fields in the temp. table, but they
       need to be proceessed differently than regular aggregate functions
+
+      Call split_sum_func here so that each argument gets its fields to
+      point to the temporary table.
     */
+    split_sum_func(thd, ref_pointer_array, fields, split_flags);
   }
   else
   {
diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h
index 433b3f6..3d4c310 100644
--- a/sql/item_windowfunc.h
+++ b/sql/item_windowfunc.h
@@ -771,6 +771,7 @@ class Item_window_func : public Item_func_or_sum
 
   void split_sum_func(THD *thd, Ref_ptr_array ref_pointer_array,
                               List<Item> &fields, uint flags);
+
   void fix_length_and_dec()
   {
     decimals = window_func()->decimals;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 49bc619..2f6ac084 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -26210,7 +26210,22 @@ AGGR_OP::end_send()
       rc= NESTED_LOOP_KILLED;
     }
     else
+    {
+      /*
+         In case we have window functions present, an extra step is required
+         to compute all the fields from the temporary table.
+         In case we have a compound expression such as: expr + expr,
+         where one of the terms has a window function inside it, only
+         after computing window function values we actually know the true
+         final result of the compounded expression.
+
+         Go through all the func items and save their values once again in the
+         corresponding temp table fields. Do this for each row in the table.
+      */
+      if (join_tab->window_funcs_step)
+        copy_funcs(join_tab->tmp_table_param->items_to_copy, join->thd);
       rc= evaluate_join_record(join, join_tab, 0);
+    }
   }
 
   // Finish rnd scn after sending records


More information about the commits mailing list