diff options
-rw-r--r-- | mysql-test/r/win_percentile_cont.result | 111 | ||||
-rw-r--r-- | mysql-test/t/win_percentile_cont.test | 55 | ||||
-rw-r--r-- | sql/item.h | 3 | ||||
-rw-r--r-- | sql/item_windowfunc.h | 18 | ||||
-rw-r--r-- | sql/sql_window.cc | 37 |
5 files changed, 28 insertions, 196 deletions
diff --git a/mysql-test/r/win_percentile_cont.result b/mysql-test/r/win_percentile_cont.result deleted file mode 100644 index 61f70892887..00000000000 --- a/mysql-test/r/win_percentile_cont.result +++ /dev/null @@ -1,111 +0,0 @@ -CREATE TABLE student (name CHAR(10), test double, score DECIMAL(19,4)); -INSERT INTO student VALUES -('Chun', 0, 3), ('Chun', 0, 7), -('Kaolin', 0.5, 3), ('Kaolin', 0.6, 7), -('Kaolin', 0.5, 4), -('Tatiana', 0.8, 4), ('Tata', 0.8, 4); -select name, percentile_disc(0.5) within group(order by score) over () from student; -name percentile_disc(0.5) within group(order by score) over () -Chun 4.0000000000 -Chun 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Tatiana 4.0000000000 -Tata 4.0000000000 -select name, percentile_cont(0.5) within group(order by score) over () from student; -name percentile_cont(0.5) within group(order by score) over () -Chun 4.0000000000 -Chun 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Tatiana 4.0000000000 -Tata 4.0000000000 -select name, percentile_cont(null) within group(order by score) over (partition by name) from student; -name percentile_cont(null) within group(order by score) over (partition by name) -Chun NULL -Chun NULL -Kaolin NULL -Kaolin NULL -Kaolin NULL -Tatiana NULL -Tata NULL -select name, percentile_disc(null) within group(order by score) over (partition by name) from student; -name percentile_disc(null) within group(order by score) over (partition by name) -Chun NULL -Chun NULL -Kaolin NULL -Kaolin NULL -Kaolin NULL -Tatiana NULL -Tata NULL -select name, percentile_cont(0.5) within group(order by score) over (partition by name) as c from student; -name c -Chun 5.0000000000 -Chun 5.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Tatiana 4.0000000000 -Tata 4.0000000000 -select name, percentile_disc(0.5) within group(order by score) over (partition by name) as c from student; -name c -Chun 3.0000000000 -Chun 3.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Tatiana 4.0000000000 -Tata 4.0000000000 -select * from ( select name , percentile_cont(0.5) within group ( order by score) over (partition by name ) from student ) as t; -name percentile_cont(0.5) within group ( order by score) over (partition by name ) -Chun 5.0000000000 -Chun 5.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Tatiana 4.0000000000 -Tata 4.0000000000 -select * from ( select name , percentile_disc(0.5) within group ( order by score) over (partition by name ) from student ) as t; -name percentile_disc(0.5) within group ( order by score) over (partition by name ) -Chun 3.0000000000 -Chun 3.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Kaolin 4.0000000000 -Tatiana 4.0000000000 -Tata 4.0000000000 -select name from student a where (select percentile_disc(0.5) within group (order by score) over (partition by name) from student b limit 1) >= 0.5; -name -Chun -Chun -Kaolin -Kaolin -Kaolin -Tatiana -Tata -select score, percentile_cont(0.5) within group(order by name) over (partition by score) from student; -ERROR HY000: Numeric datatype is required for Percentile_CONT function -select score, percentile_disc(0.5) within group(order by name) over (partition by score) from student; -score percentile_disc(0.5) within group(order by name) over (partition by score) -3.0000 Chun -7.0000 Chun -3.0000 Chun -7.0000 Chun -4.0000 Tata -4.0000 Tata -4.0000 Tata -select percentile_disc(0.5) within group(order by score,test) over (partition by name) from student; -ERROR HY000: Incorrect number of elements in the order list for 'percentile_disc' -select percentile_cont(0.5) within group(order by score,test) over (partition by name) from student; -ERROR HY000: Incorrect number of elements in the order list for 'percentile_cont' -select percentile_disc(1.5) within group(order by score) over (partition by name) from student; -ERROR HY000: Argument to the percentile functions does not belong to the range [0,1] -select percentile_cont(1.5) within group(order by score) over (partition by name) from student; -ERROR HY000: Argument to the percentile functions does not belong to the range [0,1] -select name,percentile_cont(test) within group(order by score) over (partition by name) from student; -ERROR HY000: Argument to the percentile functions is not a constant -select name, percentile_disc(test) within group(order by score) over (partition by name) from student; -ERROR HY000: Argument to the percentile functions is not a constant -drop table student; diff --git a/mysql-test/t/win_percentile_cont.test b/mysql-test/t/win_percentile_cont.test deleted file mode 100644 index 75fde963b2a..00000000000 --- a/mysql-test/t/win_percentile_cont.test +++ /dev/null @@ -1,55 +0,0 @@ -CREATE TABLE student (name CHAR(10), test double, score DECIMAL(19,4)); -INSERT INTO student VALUES -('Chun', 0, 3), ('Chun', 0, 7), -('Kaolin', 0.5, 3), ('Kaolin', 0.6, 7), -('Kaolin', 0.5, 4), -('Tatiana', 0.8, 4), ('Tata', 0.8, 4); - -#no partition clause -select name, percentile_disc(0.5) within group(order by score) over () from student; -select name, percentile_cont(0.5) within group(order by score) over () from student; - -# argument set to null -select name, percentile_cont(null) within group(order by score) over (partition by name) from student; -select name, percentile_disc(null) within group(order by score) over (partition by name) from student; - -# complete query with partition column -select name, percentile_cont(0.5) within group(order by score) over (partition by name) as c from student; -select name, percentile_disc(0.5) within group(order by score) over (partition by name) as c from student; - -#subqueries having percentile functions - -select * from ( select name , percentile_cont(0.5) within group ( order by score) over (partition by name ) from student ) as t; -select * from ( select name , percentile_disc(0.5) within group ( order by score) over (partition by name ) from student ) as t; -select name from student a where (select percentile_disc(0.5) within group (order by score) over (partition by name) from student b limit 1) >= 0.5; - -# WITH STORED PROCEDURES - - -#DISALLOWED FIELDS IN ORDER BY CLAUSE ---error ER_WRONG_TYPE_FOR_PERCENTILE_CONT -select score, percentile_cont(0.5) within group(order by name) over (partition by score) from student; -select score, percentile_disc(0.5) within group(order by name) over (partition by score) from student; - -# error with 2 order by elements - ---error ER_NOT_SINGLE_ELEMENT_ORDER_LIST -select percentile_disc(0.5) within group(order by score,test) over (partition by name) from student; ---error ER_NOT_SINGLE_ELEMENT_ORDER_LIST -select percentile_cont(0.5) within group(order by score,test) over (partition by name) from student; - -#parameter value should be in the range of 0 to 1( NEED TO THINK A WAY FOR THIS) ---error ER_ARGUMENT_OUT_OF_RANGE -select percentile_disc(1.5) within group(order by score) over (partition by name) from student; ---error ER_ARGUMENT_OUT_OF_RANGE -select percentile_cont(1.5) within group(order by score) over (partition by name) from student; - ---error ER_ARGUMENT_NOT_CONSTANT -select name,percentile_cont(test) within group(order by score) over (partition by name) from student; ---error ER_ARGUMENT_NOT_CONSTANT -select name, percentile_disc(test) within group(order by score) over (partition by name) from student; - -#CHECK TYPE OF THE ARGUMENT, SHOULD BE ONLY NUMERICAL -#select name, percentile_cont(name) within group(order by score) over (partition by name) from student; - -drop table student; diff --git a/sql/item.h b/sql/item.h index f4f2055b48c..1f743913d78 100644 --- a/sql/item.h +++ b/sql/item.h @@ -5341,7 +5341,6 @@ public: Cached_item_real(Item *item_par) :Cached_item_item(item_par),value(0.0) {} bool cmp(void); int cmp_read_only(); - double get_value(){ return value;} }; class Cached_item_int :public Cached_item_item @@ -5351,7 +5350,6 @@ public: Cached_item_int(Item *item_par) :Cached_item_item(item_par),value(0) {} bool cmp(void); int cmp_read_only(); - longlong get_value(){ return value;} }; @@ -5362,7 +5360,6 @@ public: Cached_item_decimal(Item *item_par); bool cmp(void); int cmp_read_only(); - my_decimal *get_value(){ return &value;}; }; class Cached_item_field :public Cached_item diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h index c1a8c594e20..8b3bd5a26e2 100644 --- a/sql/item_windowfunc.h +++ b/sql/item_windowfunc.h @@ -757,14 +757,14 @@ public: bool add() { - Item *arg = get_arg(0); + Item *arg= get_arg(0); if (arg->is_null()) return false; if (first_call) { prev_value= arg->val_real(); - if (prev_value >1 || prev_value < 0) + if (prev_value > 1 || prev_value < 0) { my_error(ER_ARGUMENT_OUT_OF_RANGE, MYF(0)); return true; @@ -774,7 +774,7 @@ public: double arg_val= arg->val_real(); - if (prev_value != arg_val) + if (prev_value != arg_val) { my_error(ER_ARGUMENT_NOT_CONSTANT, MYF(0)); return true; @@ -821,7 +821,7 @@ public: void fix_length_and_dec() { - decimals = 10; // TODO-cvicentiu find out how many decimals the standard + decimals = 5; // TODO-cvicentiu find out how many decimals the standard // requires. } @@ -829,6 +829,7 @@ public: { return get_item_copy<Item_sum_percentile_disc>(thd, mem_root, this); } void setup_window_func(THD *thd, Window_spec *window_spec); void setup_hybrid(THD *thd, Item *item); + bool fix_fields(THD *thd, Item **ref); private: Item_cache *value; @@ -876,7 +877,7 @@ public: bool add() { - Item *arg = get_arg(0); + Item *arg= get_arg(0); if (arg->is_null()) return false; @@ -884,7 +885,7 @@ public: { first_call= false; prev_value= arg->val_real(); - if (prev_value >1 || prev_value < 0) + if (prev_value > 1 || prev_value < 0) { my_error(ER_ARGUMENT_OUT_OF_RANGE, MYF(0)); return true; @@ -892,7 +893,7 @@ public: } double arg_val= arg->val_real(); - if (prev_value != arg_val) + if (prev_value != arg_val) { my_error(ER_ARGUMENT_NOT_CONSTANT, MYF(0)); return true; @@ -950,7 +951,7 @@ public: void fix_length_and_dec() { - decimals = 10; // TODO-cvicentiu find out how many decimals the standard + decimals = 5; // TODO-cvicentiu find out how many decimals the standard // requires. } @@ -958,6 +959,7 @@ public: { return get_item_copy<Item_sum_percentile_cont>(thd, mem_root, this); } void setup_window_func(THD *thd, Window_spec *window_spec); void setup_hybrid(THD *thd, Item *item); + bool fix_fields(THD *thd, Item **ref); private: Item_cache *floor_value; diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 9a274179b21..059dd8073f5 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -1743,7 +1743,17 @@ public: /* Walk to the end of the partition, find how many rows there are. */ while (!cursor.next()) num_rows_in_partition++; + set_win_funcs_row_count(num_rows_in_partition); + } + + ha_rows get_curr_rownum() const + { + return cursor.get_rownum(); + } +protected: + void set_win_funcs_row_count(ha_rows num_rows_in_partition) + { List_iterator_fast<Item_sum> it(sum_functions); Item_sum* item; while ((item= it++)) @@ -1753,20 +1763,16 @@ public: item_with_row_count->set_row_count(num_rows_in_partition); } } - - ha_rows get_curr_rownum() const - { - return cursor.get_rownum(); - } }; -class Frame_unbounded_following_set_count_special: public Frame_unbounded_following_set_count +class Frame_unbounded_following_set_count_no_nulls: + public Frame_unbounded_following_set_count { public: - Frame_unbounded_following_set_count_special(THD *thd, + Frame_unbounded_following_set_count_no_nulls(THD *thd, SQL_I_List<ORDER> *partition_list, - SQL_I_List<ORDER> *order_list, Item* arg) : + SQL_I_List<ORDER> *order_list) : Frame_unbounded_following_set_count(thd,partition_list, order_list) { order_item= order_list->first->item[0]; @@ -1782,16 +1788,9 @@ public: { if (!order_item->is_null()) num_rows_in_partition++; - }while (!cursor.next()); + } while (!cursor.next()); - List_iterator_fast<Item_sum> it(sum_functions); - Item_sum* item; - while ((item= it++)) - { - Item_sum_window_with_row_count* item_with_row_count = - static_cast<Item_sum_window_with_row_count *>(item); - item_with_row_count->set_row_count(num_rows_in_partition); - } + set_win_funcs_row_count(num_rows_in_partition); } ha_rows get_curr_rownum() const @@ -2614,9 +2613,9 @@ void get_window_functions_required_cursors( { if (item_win_func->only_single_element_order_list()) { - fc= new Frame_unbounded_following_set_count_special(thd, + fc= new Frame_unbounded_following_set_count_no_nulls(thd, item_win_func->window_spec->partition_list, - item_win_func->window_spec->order_list, item_win_func->window_func()->get_arg(0)); + item_win_func->window_spec->order_list); } else { |