diff options
| -rw-r--r-- | mysql-test/r/win.result | 44 | ||||
| -rw-r--r-- | mysql-test/t/win.test | 26 | ||||
| -rw-r--r-- | sql/item.cc | 4 | ||||
| -rw-r--r-- | sql/item_windowfunc.h | 1 | ||||
| -rw-r--r-- | sql/sql_select.cc | 15 |
5 files changed, 78 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; diff --git a/sql/item.cc b/sql/item.cc index d461386a696..ec4eae2a46a 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 433b3f6c4fd..3d4c310fa74 100644 --- a/sql/item_windowfunc.h +++ b/sql/item_windowfunc.h @@ -771,6 +771,7 @@ public: 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 c70e0d5b7e2..1eaa1ced0b8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26228,7 +26228,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 |