diff options
| -rw-r--r-- | mysql-test/r/win.result | 196 | ||||
| -rw-r--r-- | mysql-test/r/win_empty_over.result | 6 | ||||
| -rw-r--r-- | mysql-test/t/win.test | 77 | ||||
| -rw-r--r-- | sql/item_sum.cc | 3 | ||||
| -rw-r--r-- | sql/item_sum.h | 3 | ||||
| -rw-r--r-- | sql/item_windowfunc.cc | 1 |
6 files changed, 282 insertions, 4 deletions
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 48bdb6a6cd8..ba9ef003da1 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -2558,3 +2558,199 @@ execute stmt; row_number() over (partition by 4 order by 1+2) 1 deallocate prepare stmt; +# +# MDEV-11745: window function with min/max +# +create table t1 (i int, b int); +insert into t1 values +(1,1),(2,1),(3,1),(4,4),(5,4),(6,4),(7,8),(8,8),(9,8),(10,8); +select b, min(i) over (partition by b) as f +from t1 as tt +order by i; +b f +1 1 +1 1 +1 1 +4 4 +4 4 +4 4 +8 7 +8 7 +8 7 +8 7 +select b, min(i) over (partition by b) as f +from (select * from t1) as tt +order by i; +b f +1 1 +1 1 +1 1 +4 4 +4 4 +4 4 +8 7 +8 7 +8 7 +8 7 +select b, min(i+10) over (partition by b) as f +from t1 as tt +order by i; +b f +1 11 +1 11 +1 11 +4 14 +4 14 +4 14 +8 17 +8 17 +8 17 +8 17 +select b, min(i) over (partition by b) as f +from (select i+10 as i, b from t1) as tt +order by i; +b f +1 11 +1 11 +1 11 +4 14 +4 14 +4 14 +8 17 +8 17 +8 17 +8 17 +select b, min(i+20) over (partition by b) as f +from (select i+10 as i, b from t1) as tt +order by i; +b f +1 31 +1 31 +1 31 +4 34 +4 34 +4 34 +8 37 +8 37 +8 37 +8 37 +select b, max(i) over (partition by b) as f +from t1 as tt +order by i; +b f +1 3 +1 3 +1 3 +4 6 +4 6 +4 6 +8 10 +8 10 +8 10 +8 10 +select b, max(i) over (partition by b) as f +from (select * from t1) as tt +order by i; +b f +1 3 +1 3 +1 3 +4 6 +4 6 +4 6 +8 10 +8 10 +8 10 +8 10 +select b, max(i+10) over (partition by b) as f +from t1 as tt +order by i; +b f +1 13 +1 13 +1 13 +4 16 +4 16 +4 16 +8 20 +8 20 +8 20 +8 20 +select b, max(i) over (partition by b) as f +from (select i+10 as i, b from t1) as tt +order by i; +b f +1 13 +1 13 +1 13 +4 16 +4 16 +4 16 +8 20 +8 20 +8 20 +8 20 +select b, max(i+20) over (partition by b) as f +from (select i+10 as i, b from t1) as tt +order by i; +b f +1 33 +1 33 +1 33 +4 36 +4 36 +4 36 +8 40 +8 40 +8 40 +8 40 +select max(i), max(i), sum(i), count(i) +from t1 as tt +group by b; +max(i) max(i) sum(i) count(i) +3 3 6 3 +6 6 15 3 +10 10 34 4 +select max(i), min(sum(i)) over (partition by count(i)) f +from t1 as tt +group by b; +max(i) f +3 6 +6 6 +10 34 +select max(i), min(sum(i)) over (partition by count(i)) f +from (select * from t1) as tt +group by b; +max(i) f +3 6 +6 6 +10 34 +select max(i+10), min(sum(i)+10) over (partition by count(i)) f +from t1 as tt +group by b; +max(i+10) f +13 16 +16 16 +20 44 +select max(i), max(i), sum(i), count(i) +from (select i+10 as i, b from t1) as tt +group by b; +max(i) max(i) sum(i) count(i) +13 13 36 3 +16 16 45 3 +20 20 74 4 +select max(i), min(sum(i)) over (partition by count(i)) f +from (select i+10 as i, b from t1) as tt +group by b; +max(i) f +13 36 +16 36 +20 74 +select max(i), min(i), min(max(i)-min(i)) over (partition by count(i)) f +from (select i+10 as i, b from t1) as tt +group by b; +max(i) min(i) f +13 11 2 +16 14 2 +20 17 3 +drop table t1; diff --git a/mysql-test/r/win_empty_over.result b/mysql-test/r/win_empty_over.result index 8df2c1f4329..a5ee74b943a 100644 --- a/mysql-test/r/win_empty_over.result +++ b/mysql-test/r/win_empty_over.result @@ -138,8 +138,8 @@ create view win_view as (select a, max(a + 1) over () from t1 where a = 1); select * from win_view; a max(a + 1) over () -1 3 -1 3 -1 3 +1 2 +1 2 +1 2 drop view win_view; drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 060f579421a..c231c1eeecf 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -1559,3 +1559,80 @@ execute stmt; execute stmt; deallocate prepare stmt; +--echo # +--echo # MDEV-11745: window function with min/max +--echo # + +create table t1 (i int, b int); +insert into t1 values + (1,1),(2,1),(3,1),(4,4),(5,4),(6,4),(7,8),(8,8),(9,8),(10,8); + +select b, min(i) over (partition by b) as f + from t1 as tt +order by i; + +select b, min(i) over (partition by b) as f + from (select * from t1) as tt +order by i; + +select b, min(i+10) over (partition by b) as f + from t1 as tt +order by i; + +select b, min(i) over (partition by b) as f + from (select i+10 as i, b from t1) as tt +order by i; + +select b, min(i+20) over (partition by b) as f + from (select i+10 as i, b from t1) as tt +order by i; + +select b, max(i) over (partition by b) as f + from t1 as tt +order by i; + +select b, max(i) over (partition by b) as f + from (select * from t1) as tt +order by i; + +select b, max(i+10) over (partition by b) as f + from t1 as tt +order by i; + +select b, max(i) over (partition by b) as f + from (select i+10 as i, b from t1) as tt +order by i; + +select b, max(i+20) over (partition by b) as f + from (select i+10 as i, b from t1) as tt +order by i; + +select max(i), max(i), sum(i), count(i) + from t1 as tt +group by b; + +select max(i), min(sum(i)) over (partition by count(i)) f + from t1 as tt +group by b; + +select max(i), min(sum(i)) over (partition by count(i)) f + from (select * from t1) as tt +group by b; + +select max(i+10), min(sum(i)+10) over (partition by count(i)) f + from t1 as tt +group by b; + +select max(i), max(i), sum(i), count(i) + from (select i+10 as i, b from t1) as tt +group by b; + +select max(i), min(sum(i)) over (partition by count(i)) f + from (select i+10 as i, b from t1) as tt +group by b; + +select max(i), min(i), min(max(i)-min(i)) over (partition by count(i)) f + from (select i+10 as i, b from t1) as tt +group by b; + +drop table t1; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 2ca1be31ae1..098b1ea8750 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -1161,7 +1161,8 @@ Item_sum_hybrid::fix_fields(THD *thd, Item **ref) case TIME_RESULT: DBUG_ASSERT(0); }; - setup_hybrid(thd, args[0], NULL); + if (!is_window_func_sum_expr()) + setup_hybrid(thd, args[0], NULL); /* MIN/MAX can return NULL for empty set indepedent of the used column */ maybe_null= 1; result_field=0; diff --git a/sql/item_sum.h b/sql/item_sum.h index a838bd0bc10..57375b29114 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -576,6 +576,8 @@ public: bool check_vcol_func_processor(void *arg); virtual void setup_window_func(THD *thd, Window_spec *window_spec) {} void mark_as_window_func_sum_expr() { window_func_sum_expr_flag= true; } + bool is_window_func_sum_expr() { return window_func_sum_expr_flag; } + virtual void setup_caches(THD *thd) {}; }; @@ -1053,6 +1055,7 @@ protected: void no_rows_in_result(); void restore_to_before_no_rows_in_result(); Field *create_tmp_field(bool group, TABLE *table); + void setup_caches(THD *thd) { setup_hybrid(thd, arguments()[0], NULL); } }; diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc index a13967eaaad..fb2ad666018 100644 --- a/sql/item_windowfunc.cc +++ b/sql/item_windowfunc.cc @@ -150,6 +150,7 @@ void Item_window_func::split_sum_func(THD *thd, Ref_ptr_array ref_pointer_array, Item **p_item= &window_func()->arguments()[i]; (*p_item)->split_sum_func2(thd, ref_pointer_array, fields, p_item, flags); } + window_func()->setup_caches(thd); } |