Hi Varun, Here's the final review for Percentile Functions. Just 2 minor coding style comments. Rebase on top of current 10.3 and push to a bb-* branch to double check nothing breaks. Make sure that no merge commit happens. Regards, Vicențiu
diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h index 77cbd556e60..589f7cf5d96 100644 --- a/sql/item_windowfunc.h +++ b/sql/item_windowfunc.h @@ -678,6 +687,277 @@ class Item_sum_ntile : public Item_sum_window_with_row_count ulong current_row_count_; };
+class Item_sum_percentile_disc : public Item_sum_cume_dist, + public Type_handler_hybrid_field_type +{ +public: + Item_sum_percentile_disc(THD *thd, Item* arg) : Item_sum_cume_dist(thd, arg), + Type_handler_hybrid_field_type(&type_handler_longlong), + value(NULL), val_calculated(FALSE), first_call(TRUE), + prev_value(0), order_item(NULL){} + + double val_real() + { + if (get_row_count() == 0 || get_arg(0)->is_null()) + { + null_value= true; + return 0; + } + null_value= false; + return value->val_real(); + } + + longlong val_int() + { + if (get_row_count() == 0 || get_arg(0)->is_null()) + { + null_value= true; + return 0; + } + null_value= false; + return value->val_int(); + } + + my_decimal* val_decimal(my_decimal* dec) + { + if (get_row_count() == 0 || get_arg(0)->is_null()) + { + null_value= true; + return 0; + } + null_value= false; + return value->val_decimal(dec); + } + + String* val_str(String *str) + { + if (get_row_count() == 0 || get_arg(0)->is_null()) + { + null_value= true; + return 0; + } + null_value= false; + return value->val_str(str); + } + + bool add() + { + 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) + { + my_error(ER_ARGUMENT_OUT_OF_RANGE, MYF(0), func_name()); + return true; + } + first_call= false; + } + + double arg_val= arg->val_real(); + + if (prev_value != arg_val) + { + my_error(ER_ARGUMENT_NOT_CONSTANT, MYF(0), func_name()); + return true; + } + + if (val_calculated) + return false; + + value->store(order_item); + value->cache_value(); + if (value->null_value) + return false; + + Item_sum_cume_dist::add(); + double val= Item_sum_cume_dist::val_real(); + + if (val >= prev_value && !val_calculated) + val_calculated= true; + return false; + } + + enum Sumfunctype sum_func() const + { + return PERCENTILE_DISC_FUNC; + } + + void clear() + { + val_calculated= false; + first_call= true; + value->clear(); + Item_sum_cume_dist::clear(); + } + + const char*func_name() const + { + return "percentile_disc"; + } + + void update_field() {} + void set_type_handler(Window_spec *window_spec); + const Type_handler *type_handler() const + {return Type_handler_hybrid_field_type::type_handler();} + + void fix_length_and_dec() + { + decimals = 10; // TODO-cvicentiu find out how many decimals the standard + // requires. + } + + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy
(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; + bool val_calculated; + bool first_call; + double prev_value; + Item *order_item; +}; + +class Item_sum_percentile_cont : public Item_sum_cume_dist, + public Type_handler_hybrid_field_type +{ +public: + Item_sum_percentile_cont(THD *thd, Item* arg) : Item_sum_cume_dist(thd, arg), + Type_handler_hybrid_field_type(&type_handler_double), + floor_value(NULL), ceil_value(NULL), first_call(TRUE),prev_value(0), + ceil_val_calculated(FALSE), floor_val_calculated(FALSE), order_item(NULL){} + + double val_real() + { + if (get_row_count() == 0 || get_arg(0)->is_null()) + { + null_value= true; + return 0; + } + null_value= false; + double val= 1 + prev_value * (get_row_count()-1); + + /* + Applying the formula to get the value + If (CRN = FRN = RN) then the result is (value of expression from row at RN) + Otherwise the result is + (CRN - RN) * (value of expression for row at FRN) + + (RN - FRN) * (value of expression for row at CRN) + */ + + if(ceil(val) == floor(val)) + return floor_value->val_real(); + + double ret_val= ((val - floor(val)) * ceil_value->val_real()) + + ((ceil(val) - val) * floor_value->val_real()); + + return ret_val; + } + + bool add() + { + Item *arg= get_arg(0); + if (arg->is_null()) + return false; + + if (first_call) + { + first_call= false; + prev_value= arg->val_real(); + if (prev_value > 1 || prev_value < 0) + { + my_error(ER_ARGUMENT_OUT_OF_RANGE, MYF(0), func_name()); + return true; + } + } + + double arg_val= arg->val_real(); + if (prev_value != arg_val) + { + my_error(ER_ARGUMENT_NOT_CONSTANT, MYF(0), func_name()); + return true; + } + + if (!floor_val_calculated) + { + floor_value->store(order_item); + floor_value->cache_value(); + if (floor_value->null_value) + return false; Add one extra space before return + } + if (floor_val_calculated && !ceil_val_calculated) + { + ceil_value->store(order_item); + ceil_value->cache_value(); + if (ceil_value->null_value) + return false; Add one extra space before return + } + + Item_sum_cume_dist::add(); + double val= 1 + prev_value * (get_row_count()-1); + + if (!floor_val_calculated && get_row_number() == floor(val)) + floor_val_calculated= true; too much indentation here. Delete 2 spaces + + if (!ceil_val_calculated && get_row_number() == ceil(val)) + ceil_val_calculated= true; too much indentation here. Delete 2 spaces + return false; + } + + enum Sumfunctype sum_func() const + { + return PERCENTILE_CONT_FUNC; + } + + void clear() + { + first_call= true; + floor_value->clear(); + ceil_value->clear(); + floor_val_calculated= false; + ceil_val_calculated= false; + Item_sum_cume_dist::clear(); + } + + const char*func_name() const + { + return "percentile_cont"; + } + void update_field() {} + void set_type_handler(Window_spec *window_spec); + const Type_handler *type_handler() const + {return Type_handler_hybrid_field_type::type_handler();} + + void fix_length_and_dec() + { + decimals = 10; // TODO-cvicentiu find out how many decimals the standard + // requires. + } + + Item *get_copy(THD *thd, MEM_ROOT *mem_root) + { return get_item_copy (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; + Item_cache *ceil_value; + bool first_call; + double prev_value; + bool ceil_val_calculated; + bool floor_val_calculated; + Item *order_item; +}; + + + class Item_window_func : public Item_func_or_sum { diff --git a/sql/lex.h b/sql/lex.h index 17dd45f9c0b..fcb44a0f836 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -735,6 +736,7 @@ static SYMBOL sql_functions[] = { { "LAG", SYM(LAG_SYM)}, { "LEAD", SYM(LEAD_SYM)}, { "MAX", SYM(MAX_SYM)}, + { "MEDIAN", SYM(MEDIAN_SYM)}, Fix indentation here { "MID", SYM(SUBSTRING)}, /* unireg function */ { "MIN", SYM(MIN_SYM)}, { "NOW", SYM(NOW_SYM)}, \ No newline at end of file