revision-id: d071e75403fc8d26721aab520258a4d6a3647a3c (mariadb-10.2.22-47-gd071e75) parent(s): ab7e2b048d7d0835ae7473873169b7606114348e author: Varun Gupta committer: Varun Gupta timestamp: 2019-03-11 19:05:14 +0530 message: MDEV-18431: Select max + row_number giving incorrect result The issue here was when we had a subquery and a window function in an expression in the select list then subquery was getting computed after window function computation. This resulted in incorrect results because the subquery was correlated and the fields in the subquery was pointing to the base table instead of the temporary table. The approach to fix this was to have an additional field in the temporary table for the subquery and to execute the subquery before window function execution. After execution the values for the subquery were stored in the temporary table and then when we needed to calcuate the expression, all we do is read the values from the temporary table for the subquery. --- mysql-test/r/win.result | 17 +++++++++++++++++ mysql-test/t/win.test | 14 ++++++++++++++ sql/item.cc | 1 - 3 files changed, 31 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 2e80c2c..ed1d350 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3488,3 +3488,20 @@ ifnull(max(n1) over (partition by n1),'aaa') 4 drop table t1; drop view v1; +# +# MDEV-18431: Select max + row_number giving incorrect result +# +create table t1 (id int, v int); +insert into t1 values (1, 1), (1,2), (1,3), (2, 1), (2, 2); +select e.id, +(select max(t1.v) from t1 where t1.id=e.id) as a, +row_number() over (partition by e.id order by e.v) as b, +(select max(t1.v) from t1 where t1.id=e.id) + (row_number() over (partition by e.id order by e.v)) as sum_a_b +from t1 e; +id a b sum_a_b +1 3 1 4 +1 3 2 5 +1 3 3 6 +2 2 1 3 +2 2 2 4 +drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 18bdfa3..8c9bf3e 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2240,3 +2240,17 @@ explain select * from v1; select * from v1; drop table t1; drop view v1; + +--echo # +--echo # MDEV-18431: Select max + row_number giving incorrect result +--echo # + +create table t1 (id int, v int); +insert into t1 values (1, 1), (1,2), (1,3), (2, 1), (2, 2); + +select e.id, + (select max(t1.v) from t1 where t1.id=e.id) as a, + row_number() over (partition by e.id order by e.v) as b, + (select max(t1.v) from t1 where t1.id=e.id) + (row_number() over (partition by e.id order by e.v)) as sum_a_b +from t1 e; +drop table t1; diff --git a/sql/item.cc b/sql/item.cc index 46420fd..6c9496c 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1996,7 +1996,6 @@ void Item::split_sum_func2(THD *thd, Ref_ptr_array ref_pointer_array, } if (unlikely((!(used_tables() & ~PARAM_TABLE_BIT) || - type() == SUBSELECT_ITEM || (type() == REF_ITEM && ((Item_ref*)this)->ref_type() != Item_ref::VIEW_REF)))) return;