1 /* 2 Copyright (c) 2016, 2020, MariaDB 3 4 This program is free software; you can redistribute it and/or modify 5 it under the terms of the GNU General Public License as published by 6 the Free Software Foundation; version 2 of the License. 7 8 This program is distributed in the hope that it will be useful, 9 but WITHOUT ANY WARRANTY; without even the implied warranty of 10 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 11 GNU General Public License for more details. 12 13 You should have received a copy of the GNU General Public License 14 along with this program; if not, write to the Free Software 15 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ 16 17 #ifndef ITEM_WINDOWFUNC_INCLUDED 18 #define ITEM_WINDOWFUNC_INCLUDED 19 20 #include "item.h" 21 22 class Window_spec; 23 24 25 int test_if_group_changed(List<Cached_item> &list); 26 27 28 /* A wrapper around test_if_group_changed */ 29 class Group_bound_tracker 30 { 31 public: 32 Group_bound_tracker(THD * thd,SQL_I_List<ORDER> * list)33 Group_bound_tracker(THD *thd, SQL_I_List<ORDER> *list) 34 { 35 for (ORDER *curr = list->first; curr; curr=curr->next) 36 { 37 Cached_item *tmp= new_Cached_item(thd, curr->item[0], TRUE); 38 group_fields.push_back(tmp); 39 } 40 } 41 init()42 void init() 43 { 44 first_check= true; 45 } 46 47 /* 48 Check if the current row is in a different group than the previous row 49 this function was called for. 50 XXX: Side-effect: The new row's group becomes the current row's group. 51 52 Returns true if there is a change between the current_group and the cached 53 value, or if it is the first check after a call to init. 54 */ check_if_next_group()55 bool check_if_next_group() 56 { 57 if (test_if_group_changed(group_fields) > -1 || first_check) 58 { 59 first_check= false; 60 return true; 61 } 62 return false; 63 } 64 65 /* 66 Check if the current row is in a different group than the previous row 67 check_if_next_group was called for. 68 69 Compares the groups without the additional side effect of updating the 70 current cached values. 71 */ compare_with_cache()72 int compare_with_cache() 73 { 74 List_iterator<Cached_item> li(group_fields); 75 Cached_item *ptr; 76 int res; 77 while ((ptr= li++)) 78 { 79 if ((res= ptr->cmp_read_only())) 80 return res; 81 } 82 return 0; 83 } ~Group_bound_tracker()84 ~Group_bound_tracker() 85 { 86 group_fields.delete_elements(); 87 } 88 89 private: 90 List<Cached_item> group_fields; 91 /* 92 During the first check_if_next_group, the list of cached_items is not 93 initialized. The compare function will return that the items match if 94 the field's value is the same as the Cached_item's default value (0). 95 This flag makes sure that we always return true during the first check. 96 97 XXX This is better to be implemented within test_if_group_changed, but 98 since it is used in other parts of the codebase, we keep it here for now. 99 */ 100 bool first_check; 101 }; 102 103 /* 104 ROW_NUMBER() OVER (...) 105 106 @detail 107 - This is a Window function (not just an aggregate) 108 - It can be computed by doing one pass over select output, provided 109 the output is sorted according to the window definition. 110 */ 111 112 class Item_sum_row_number: public Item_sum_int 113 { 114 longlong count; 115 116 public: 117 Item_sum_row_number(THD * thd)118 Item_sum_row_number(THD *thd) 119 : Item_sum_int(thd), count(0) {} 120 clear()121 void clear() 122 { 123 count= 0; 124 } 125 add()126 bool add() 127 { 128 count++; 129 return false; 130 } 131 update_field()132 void update_field() {} 133 sum_func()134 enum Sumfunctype sum_func() const 135 { 136 return ROW_NUMBER_FUNC; 137 } 138 val_int()139 longlong val_int() 140 { 141 return count; 142 } func_name()143 const char*func_name() const 144 { 145 return "row_number"; 146 } 147 get_copy(THD * thd)148 Item *get_copy(THD *thd) 149 { return get_item_copy<Item_sum_row_number>(thd, this); } 150 }; 151 152 153 /* 154 RANK() OVER (...) Windowing function 155 156 @detail 157 - This is a Window function (not just an aggregate) 158 - It can be computed by doing one pass over select output, provided 159 the output is sorted according to the window definition. 160 161 The function is defined as: 162 163 "The rank of row R is defined as 1 (one) plus the number of rows that 164 precede R and are not peers of R" 165 166 "This implies that if two or more rows are not distinct with respect to 167 the window ordering, then there will be one or more" 168 */ 169 170 class Item_sum_rank: public Item_sum_int 171 { 172 protected: 173 longlong row_number; // just ROW_NUMBER() 174 longlong cur_rank; // current value 175 176 Group_bound_tracker *peer_tracker; 177 public: 178 Item_sum_rank(THD * thd)179 Item_sum_rank(THD *thd) : Item_sum_int(thd), peer_tracker(NULL) {} 180 clear()181 void clear() 182 { 183 /* This is called on partition start */ 184 cur_rank= 1; 185 row_number= 0; 186 } 187 188 bool add(); 189 val_int()190 longlong val_int() 191 { 192 return cur_rank; 193 } 194 update_field()195 void update_field() {} 196 /* 197 void reset_field(); 198 TODO: ^^ what does this do ? It is not called ever? 199 */ 200 sum_func()201 enum Sumfunctype sum_func () const 202 { 203 return RANK_FUNC; 204 } 205 func_name()206 const char*func_name() const 207 { 208 return "rank"; 209 } 210 211 void setup_window_func(THD *thd, Window_spec *window_spec); 212 cleanup()213 void cleanup() 214 { 215 if (peer_tracker) 216 { 217 delete peer_tracker; 218 peer_tracker= NULL; 219 } 220 Item_sum_int::cleanup(); 221 } get_copy(THD * thd)222 Item *get_copy(THD *thd) 223 { return get_item_copy<Item_sum_rank>(thd, this); } 224 }; 225 226 227 /* 228 DENSE_RANK() OVER (...) Windowing function 229 230 @detail 231 - This is a Window function (not just an aggregate) 232 - It can be computed by doing one pass over select output, provided 233 the output is sorted according to the window definition. 234 235 The function is defined as: 236 237 "If DENSE_RANK is specified, then the rank of row R is defined as the 238 number of rows preceding and including R that are distinct with respect 239 to the window ordering" 240 241 "This implies that there are no gaps in the sequential rank numbering of 242 rows in each window partition." 243 */ 244 245 246 class Item_sum_dense_rank: public Item_sum_int 247 { 248 longlong dense_rank; 249 bool first_add; 250 Group_bound_tracker *peer_tracker; 251 public: 252 /* 253 XXX(cvicentiu) This class could potentially be implemented in the rank 254 class, with a switch for the DENSE case. 255 */ clear()256 void clear() 257 { 258 dense_rank= 0; 259 first_add= true; 260 } 261 bool add(); update_field()262 void update_field() {} val_int()263 longlong val_int() 264 { 265 return dense_rank; 266 } 267 Item_sum_dense_rank(THD * thd)268 Item_sum_dense_rank(THD *thd) 269 : Item_sum_int(thd), dense_rank(0), first_add(true), peer_tracker(NULL) {} sum_func()270 enum Sumfunctype sum_func () const 271 { 272 return DENSE_RANK_FUNC; 273 } 274 func_name()275 const char*func_name() const 276 { 277 return "dense_rank"; 278 } 279 280 void setup_window_func(THD *thd, Window_spec *window_spec); 281 cleanup()282 void cleanup() 283 { 284 if (peer_tracker) 285 { 286 delete peer_tracker; 287 peer_tracker= NULL; 288 } 289 Item_sum_int::cleanup(); 290 } get_copy(THD * thd)291 Item *get_copy(THD *thd) 292 { return get_item_copy<Item_sum_dense_rank>(thd, this); } 293 }; 294 295 class Item_sum_hybrid_simple : public Item_sum_hybrid 296 { 297 public: Item_sum_hybrid_simple(THD * thd,Item * arg)298 Item_sum_hybrid_simple(THD *thd, Item *arg): 299 Item_sum_hybrid(thd, arg), 300 value(NULL) 301 { } 302 Item_sum_hybrid_simple(THD * thd,Item * arg1,Item * arg2)303 Item_sum_hybrid_simple(THD *thd, Item *arg1, Item *arg2): 304 Item_sum_hybrid(thd, arg1, arg2), 305 value(NULL) 306 { } 307 308 bool add(); 309 bool fix_fields(THD *, Item **); 310 bool fix_length_and_dec(); 311 void setup_hybrid(THD *thd, Item *item); 312 double val_real(); 313 longlong val_int(); 314 my_decimal *val_decimal(my_decimal *); 315 void reset_field(); 316 String *val_str(String *); 317 bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); 318 void update_field(); 319 Field *create_tmp_field(bool group, TABLE *table); clear()320 void clear() 321 { 322 value->clear(); 323 null_value= 1; 324 } 325 326 private: 327 Item_cache *value; 328 }; 329 330 /* 331 This item will remember the first value added to it. It will not update 332 the value unless it is cleared. 333 */ 334 class Item_sum_first_value : public Item_sum_hybrid_simple 335 { 336 public: Item_sum_first_value(THD * thd,Item * arg_expr)337 Item_sum_first_value(THD* thd, Item* arg_expr) : 338 Item_sum_hybrid_simple(thd, arg_expr) {} 339 340 sum_func()341 enum Sumfunctype sum_func () const 342 { 343 return FIRST_VALUE_FUNC; 344 } 345 func_name()346 const char*func_name() const 347 { 348 return "first_value"; 349 } 350 get_copy(THD * thd)351 Item *get_copy(THD *thd) 352 { return get_item_copy<Item_sum_first_value>(thd, this); } 353 }; 354 355 /* 356 This item will remember the last value added to it. 357 358 This item does not support removal, and can be cleared only by calling 359 clear(). 360 */ 361 class Item_sum_last_value : public Item_sum_hybrid_simple 362 { 363 public: Item_sum_last_value(THD * thd,Item * arg_expr)364 Item_sum_last_value(THD* thd, Item* arg_expr) : 365 Item_sum_hybrid_simple(thd, arg_expr) {} 366 sum_func()367 enum Sumfunctype sum_func() const 368 { 369 return LAST_VALUE_FUNC; 370 } 371 func_name()372 const char*func_name() const 373 { 374 return "last_value"; 375 } 376 get_copy(THD * thd)377 Item *get_copy(THD *thd) 378 { return get_item_copy<Item_sum_last_value>(thd, this); } 379 }; 380 381 class Item_sum_nth_value : public Item_sum_hybrid_simple 382 { 383 public: Item_sum_nth_value(THD * thd,Item * arg_expr,Item * offset_expr)384 Item_sum_nth_value(THD *thd, Item *arg_expr, Item* offset_expr) : 385 Item_sum_hybrid_simple(thd, arg_expr, offset_expr) {} 386 sum_func()387 enum Sumfunctype sum_func() const 388 { 389 return NTH_VALUE_FUNC; 390 } 391 func_name()392 const char*func_name() const 393 { 394 return "nth_value"; 395 } 396 get_copy(THD * thd)397 Item *get_copy(THD *thd) 398 { return get_item_copy<Item_sum_nth_value>(thd, this); } 399 }; 400 401 class Item_sum_lead : public Item_sum_hybrid_simple 402 { 403 public: Item_sum_lead(THD * thd,Item * arg_expr,Item * offset_expr)404 Item_sum_lead(THD *thd, Item *arg_expr, Item* offset_expr) : 405 Item_sum_hybrid_simple(thd, arg_expr, offset_expr) {} 406 sum_func()407 enum Sumfunctype sum_func() const 408 { 409 return LEAD_FUNC; 410 } 411 func_name()412 const char*func_name() const 413 { 414 return "lead"; 415 } 416 get_copy(THD * thd)417 Item *get_copy(THD *thd) 418 { return get_item_copy<Item_sum_lead>(thd, this); } 419 }; 420 421 class Item_sum_lag : public Item_sum_hybrid_simple 422 { 423 public: Item_sum_lag(THD * thd,Item * arg_expr,Item * offset_expr)424 Item_sum_lag(THD *thd, Item *arg_expr, Item* offset_expr) : 425 Item_sum_hybrid_simple(thd, arg_expr, offset_expr) {} 426 sum_func()427 enum Sumfunctype sum_func() const 428 { 429 return LAG_FUNC; 430 } 431 func_name()432 const char*func_name() const 433 { 434 return "lag"; 435 } 436 get_copy(THD * thd)437 Item *get_copy(THD *thd) 438 { return get_item_copy<Item_sum_lag>(thd, this); } 439 }; 440 441 442 class Partition_row_count 443 { 444 public: Partition_row_count()445 Partition_row_count() :partition_row_count_(0) { } set_partition_row_count(ulonglong count)446 void set_partition_row_count(ulonglong count) 447 { 448 partition_row_count_ = count; 449 } calc_val_real(bool * null_value,ulonglong current_row_count)450 double calc_val_real(bool *null_value, 451 ulonglong current_row_count) 452 { 453 if ((*null_value= (partition_row_count_ == 0))) 454 return 0; 455 return static_cast<double>(current_row_count) / partition_row_count_; 456 } 457 protected: get_row_count()458 longlong get_row_count() { return partition_row_count_; } 459 ulonglong partition_row_count_; 460 }; 461 462 463 class Current_row_count 464 { 465 public: Current_row_count()466 Current_row_count() :current_row_count_(0) { } 467 protected: get_row_number()468 ulonglong get_row_number() { return current_row_count_ ; } 469 ulonglong current_row_count_; 470 }; 471 472 473 /* 474 @detail 475 "The relative rank of a row R is defined as (RK-1)/(NR-1), where RK is 476 defined to be the RANK of R and NR is defined to be the number of rows in 477 the window partition of R." 478 479 Computation of this function requires two passes: 480 - First pass to find #rows in the partition 481 This is held within the row_count context. 482 - Second pass to compute rank of current row and the value of the function 483 */ 484 class Item_sum_percent_rank: public Item_sum_double, 485 public Partition_row_count 486 { 487 public: Item_sum_percent_rank(THD * thd)488 Item_sum_percent_rank(THD *thd) 489 : Item_sum_double(thd), cur_rank(1), peer_tracker(NULL) {} 490 val_int()491 longlong val_int() 492 { 493 /* 494 Percent rank is a real value so calling the integer value should never 495 happen. It makes no sense as it gets truncated to either 0 or 1. 496 */ 497 DBUG_ASSERT(0); 498 return 0; 499 } 500 val_real()501 double val_real() 502 { 503 /* 504 We can not get the real value without knowing the number of rows 505 in the partition. Don't divide by 0. 506 */ 507 ulonglong partition_rows = get_row_count(); 508 null_value= partition_rows > 0 ? false : true; 509 510 return partition_rows > 1 ? 511 static_cast<double>(cur_rank - 1) / (partition_rows - 1) : 0; 512 } 513 sum_func()514 enum Sumfunctype sum_func () const 515 { 516 return PERCENT_RANK_FUNC; 517 } 518 func_name()519 const char*func_name() const 520 { 521 return "percent_rank"; 522 } 523 update_field()524 void update_field() {} 525 clear()526 void clear() 527 { 528 cur_rank= 1; 529 row_number= 0; 530 } 531 bool add(); type_handler()532 const Type_handler *type_handler() const { return &type_handler_double; } 533 fix_length_and_dec()534 bool fix_length_and_dec() 535 { 536 decimals = 10; // TODO-cvicentiu find out how many decimals the standard 537 // requires. 538 return FALSE; 539 } 540 541 void setup_window_func(THD *thd, Window_spec *window_spec); 542 set_partition_row_count(ulonglong count)543 void set_partition_row_count(ulonglong count) 544 { 545 Partition_row_count::set_partition_row_count(count); 546 } 547 get_copy(THD * thd)548 Item *get_copy(THD *thd) 549 { return get_item_copy<Item_sum_percent_rank>(thd, this); } 550 551 private: 552 longlong cur_rank; // Current rank of the current row. 553 longlong row_number; // Value if this were ROW_NUMBER() function. 554 555 Group_bound_tracker *peer_tracker; 556 cleanup()557 void cleanup() 558 { 559 if (peer_tracker) 560 { 561 delete peer_tracker; 562 peer_tracker= NULL; 563 } 564 Item_sum_num::cleanup(); 565 } 566 }; 567 568 569 570 571 /* 572 @detail 573 "The relative rank of a row R is defined as NP/NR, where 574 - NP is defined to be the number of rows preceding or peer with R in the 575 window ordering of the window partition of R 576 - NR is defined to be the number of rows in the window partition of R. 577 578 Just like with Item_sum_percent_rank, computation of this function requires 579 two passes. 580 */ 581 582 class Item_sum_cume_dist: public Item_sum_double, 583 public Partition_row_count, 584 public Current_row_count 585 { 586 public: Item_sum_cume_dist(THD * thd)587 Item_sum_cume_dist(THD *thd) :Item_sum_double(thd) { } Item_sum_cume_dist(THD * thd,Item * arg)588 Item_sum_cume_dist(THD *thd, Item *arg) :Item_sum_double(thd, arg) { } 589 val_real()590 double val_real() 591 { 592 return calc_val_real(&null_value, current_row_count_); 593 } 594 add()595 bool add() 596 { 597 current_row_count_++; 598 return false; 599 } 600 sum_func()601 enum Sumfunctype sum_func() const 602 { 603 return CUME_DIST_FUNC; 604 } 605 clear()606 void clear() 607 { 608 current_row_count_= 0; 609 partition_row_count_= 0; 610 } 611 func_name()612 const char*func_name() const 613 { 614 return "cume_dist"; 615 } 616 update_field()617 void update_field() {} type_handler()618 const Type_handler *type_handler() const { return &type_handler_double; } 619 fix_length_and_dec()620 bool fix_length_and_dec() 621 { 622 decimals = 10; // TODO-cvicentiu find out how many decimals the standard 623 // requires. 624 return FALSE; 625 } 626 set_partition_row_count(ulonglong count)627 void set_partition_row_count(ulonglong count) 628 { 629 Partition_row_count::set_partition_row_count(count); 630 } 631 get_copy(THD * thd)632 Item *get_copy(THD *thd) 633 { return get_item_copy<Item_sum_cume_dist>(thd, this); } 634 635 }; 636 637 class Item_sum_ntile : public Item_sum_int, 638 public Partition_row_count, 639 public Current_row_count 640 { 641 public: Item_sum_ntile(THD * thd,Item * num_quantiles_expr)642 Item_sum_ntile(THD* thd, Item* num_quantiles_expr) : 643 Item_sum_int(thd, num_quantiles_expr), n_old_val_(0) 644 { } 645 val_int()646 longlong val_int() 647 { 648 if (get_row_count() == 0) 649 { 650 null_value= true; 651 return 0; 652 } 653 654 longlong num_quantiles= get_num_quantiles(); 655 656 if (num_quantiles <= 0 || 657 (static_cast<ulonglong>(num_quantiles) != n_old_val_ && n_old_val_ > 0)) 658 { 659 my_error(ER_INVALID_NTILE_ARGUMENT, MYF(0)); 660 return true; 661 } 662 n_old_val_= static_cast<ulonglong>(num_quantiles); 663 null_value= false; 664 ulonglong quantile_size = get_row_count() / num_quantiles; 665 ulonglong extra_rows = get_row_count() - quantile_size * num_quantiles; 666 667 if (current_row_count_ <= extra_rows * (quantile_size + 1)) 668 return (current_row_count_ - 1) / (quantile_size + 1) + 1; 669 670 return (current_row_count_ - 1 - extra_rows) / quantile_size + 1; 671 } 672 add()673 bool add() 674 { 675 current_row_count_++; 676 return false; 677 } 678 sum_func()679 enum Sumfunctype sum_func() const 680 { 681 return NTILE_FUNC; 682 } 683 clear()684 void clear() 685 { 686 current_row_count_= 0; 687 partition_row_count_= 0; 688 n_old_val_= 0; 689 } 690 func_name()691 const char*func_name() const 692 { 693 return "ntile"; 694 } 695 update_field()696 void update_field() {} 697 set_partition_row_count(ulonglong count)698 void set_partition_row_count(ulonglong count) 699 { 700 Partition_row_count::set_partition_row_count(count); 701 } 702 get_copy(THD * thd)703 Item *get_copy(THD *thd) 704 { return get_item_copy<Item_sum_ntile>(thd, this); } 705 706 private: get_num_quantiles()707 longlong get_num_quantiles() { return args[0]->val_int(); } 708 ulonglong n_old_val_; 709 }; 710 711 class Item_sum_percentile_disc : public Item_sum_num, 712 public Type_handler_hybrid_field_type, 713 public Partition_row_count, 714 public Current_row_count 715 { 716 public: Item_sum_percentile_disc(THD * thd,Item * arg)717 Item_sum_percentile_disc(THD *thd, Item* arg) : Item_sum_num(thd, arg), 718 Type_handler_hybrid_field_type(&type_handler_longlong), 719 value(NULL), val_calculated(FALSE), first_call(TRUE), 720 prev_value(0), order_item(NULL){} 721 val_real()722 double val_real() 723 { 724 if (get_row_count() == 0 || get_arg(0)->is_null()) 725 { 726 null_value= true; 727 return 0; 728 } 729 null_value= false; 730 return value->val_real(); 731 } 732 val_int()733 longlong val_int() 734 { 735 if (get_row_count() == 0 || get_arg(0)->is_null()) 736 { 737 null_value= true; 738 return 0; 739 } 740 null_value= false; 741 return value->val_int(); 742 } 743 val_decimal(my_decimal * dec)744 my_decimal* val_decimal(my_decimal* dec) 745 { 746 if (get_row_count() == 0 || get_arg(0)->is_null()) 747 { 748 null_value= true; 749 return 0; 750 } 751 null_value= false; 752 return value->val_decimal(dec); 753 } 754 val_str(String * str)755 String* val_str(String *str) 756 { 757 if (get_row_count() == 0 || get_arg(0)->is_null()) 758 { 759 null_value= true; 760 return 0; 761 } 762 null_value= false; 763 return value->val_str(str); 764 } 765 get_date(MYSQL_TIME * ltime,ulonglong fuzzydate)766 bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate) 767 { 768 if (get_row_count() == 0 || get_arg(0)->is_null()) 769 { 770 null_value= true; 771 return 0; 772 } 773 null_value= false; 774 return value->get_date(ltime, fuzzydate); 775 } 776 add()777 bool add() 778 { 779 Item *arg= get_arg(0); 780 if (arg->is_null()) 781 return false; 782 783 if (first_call) 784 { 785 prev_value= arg->val_real(); 786 if (prev_value > 1 || prev_value < 0) 787 { 788 my_error(ER_ARGUMENT_OUT_OF_RANGE, MYF(0), func_name()); 789 return true; 790 } 791 first_call= false; 792 } 793 794 double arg_val= arg->val_real(); 795 796 if (prev_value != arg_val) 797 { 798 my_error(ER_ARGUMENT_NOT_CONSTANT, MYF(0), func_name()); 799 return true; 800 } 801 802 if (val_calculated) 803 return false; 804 805 value->store(order_item); 806 value->cache_value(); 807 if (value->null_value) 808 return false; 809 810 current_row_count_++; 811 double val= calc_val_real(&null_value, current_row_count_); 812 813 if (val >= prev_value && !val_calculated) 814 val_calculated= true; 815 return false; 816 } 817 sum_func()818 enum Sumfunctype sum_func() const 819 { 820 return PERCENTILE_DISC_FUNC; 821 } 822 clear()823 void clear() 824 { 825 val_calculated= false; 826 first_call= true; 827 value->clear(); 828 partition_row_count_= 0; 829 current_row_count_= 0; 830 } 831 func_name()832 const char*func_name() const 833 { 834 return "percentile_disc"; 835 } 836 update_field()837 void update_field() {} type_handler()838 const Type_handler *type_handler() const 839 {return Type_handler_hybrid_field_type::type_handler();} 840 fix_length_and_dec()841 bool fix_length_and_dec() 842 { 843 decimals = 10; // TODO-cvicentiu find out how many decimals the standard 844 // requires. 845 return FALSE; 846 } 847 set_partition_row_count(ulonglong count)848 void set_partition_row_count(ulonglong count) 849 { 850 Partition_row_count::set_partition_row_count(count); 851 } 852 get_copy(THD * thd)853 Item *get_copy(THD *thd) 854 { return get_item_copy<Item_sum_percentile_disc>(thd, this); } 855 void setup_window_func(THD *thd, Window_spec *window_spec); 856 void setup_hybrid(THD *thd, Item *item); 857 bool fix_fields(THD *thd, Item **ref); 858 859 private: 860 Item_cache *value; 861 bool val_calculated; 862 bool first_call; 863 double prev_value; 864 Item *order_item; 865 }; 866 867 class Item_sum_percentile_cont : public Item_sum_double, 868 public Partition_row_count, 869 public Current_row_count 870 { 871 public: Item_sum_percentile_cont(THD * thd,Item * arg)872 Item_sum_percentile_cont(THD *thd, Item* arg) : Item_sum_double(thd, arg), 873 floor_value(NULL), ceil_value(NULL), first_call(TRUE),prev_value(0), 874 ceil_val_calculated(FALSE), floor_val_calculated(FALSE), order_item(NULL){} 875 val_real()876 double val_real() 877 { 878 if (get_row_count() == 0 || get_arg(0)->is_null()) 879 { 880 null_value= true; 881 return 0; 882 } 883 null_value= false; 884 double val= 1 + prev_value * (get_row_count()-1); 885 886 /* 887 Applying the formula to get the value 888 If (CRN = FRN = RN) then the result is (value of expression from row at RN) 889 Otherwise the result is 890 (CRN - RN) * (value of expression for row at FRN) + 891 (RN - FRN) * (value of expression for row at CRN) 892 */ 893 894 if(ceil(val) == floor(val)) 895 return floor_value->val_real(); 896 897 double ret_val= ((val - floor(val)) * ceil_value->val_real()) + 898 ((ceil(val) - val) * floor_value->val_real()); 899 900 return ret_val; 901 } 902 add()903 bool add() 904 { 905 Item *arg= get_arg(0); 906 if (arg->is_null()) 907 return false; 908 909 if (first_call) 910 { 911 first_call= false; 912 prev_value= arg->val_real(); 913 if (prev_value > 1 || prev_value < 0) 914 { 915 my_error(ER_ARGUMENT_OUT_OF_RANGE, MYF(0), func_name()); 916 return true; 917 } 918 } 919 920 double arg_val= arg->val_real(); 921 if (prev_value != arg_val) 922 { 923 my_error(ER_ARGUMENT_NOT_CONSTANT, MYF(0), func_name()); 924 return true; 925 } 926 927 if (!floor_val_calculated) 928 { 929 floor_value->store(order_item); 930 floor_value->cache_value(); 931 if (floor_value->null_value) 932 return false; 933 } 934 if (floor_val_calculated && !ceil_val_calculated) 935 { 936 ceil_value->store(order_item); 937 ceil_value->cache_value(); 938 if (ceil_value->null_value) 939 return false; 940 } 941 942 current_row_count_++; 943 double val= 1 + prev_value * (get_row_count()-1); 944 945 if (!floor_val_calculated && get_row_number() == floor(val)) 946 floor_val_calculated= true; 947 948 if (!ceil_val_calculated && get_row_number() == ceil(val)) 949 ceil_val_calculated= true; 950 return false; 951 } 952 sum_func()953 enum Sumfunctype sum_func() const 954 { 955 return PERCENTILE_CONT_FUNC; 956 } 957 clear()958 void clear() 959 { 960 first_call= true; 961 floor_value->clear(); 962 ceil_value->clear(); 963 floor_val_calculated= false; 964 ceil_val_calculated= false; 965 partition_row_count_= 0; 966 current_row_count_= 0; 967 } 968 func_name()969 const char*func_name() const 970 { 971 return "percentile_cont"; 972 } update_field()973 void update_field() {} 974 fix_length_and_dec()975 bool fix_length_and_dec() 976 { 977 decimals = 10; // TODO-cvicentiu find out how many decimals the standard 978 // requires. 979 return FALSE; 980 } 981 set_partition_row_count(ulonglong count)982 void set_partition_row_count(ulonglong count) 983 { 984 Partition_row_count::set_partition_row_count(count); 985 } 986 get_copy(THD * thd)987 Item *get_copy(THD *thd) 988 { return get_item_copy<Item_sum_percentile_cont>(thd, this); } 989 void setup_window_func(THD *thd, Window_spec *window_spec); 990 void setup_hybrid(THD *thd, Item *item); 991 bool fix_fields(THD *thd, Item **ref); 992 993 private: 994 Item_cache *floor_value; 995 Item_cache *ceil_value; 996 bool first_call; 997 double prev_value; 998 bool ceil_val_calculated; 999 bool floor_val_calculated; 1000 Item *order_item; 1001 }; 1002 1003 1004 1005 1006 class Item_window_func : public Item_func_or_sum 1007 { 1008 /* Window function parameters as we've got them from the parser */ 1009 public: 1010 LEX_CSTRING *window_name; 1011 public: 1012 Window_spec *window_spec; 1013 1014 public: Item_window_func(THD * thd,Item_sum * win_func,LEX_CSTRING * win_name)1015 Item_window_func(THD *thd, Item_sum *win_func, LEX_CSTRING *win_name) 1016 : Item_func_or_sum(thd, (Item *) win_func), 1017 window_name(win_name), window_spec(NULL), 1018 force_return_blank(true), 1019 read_value_from_result_field(false) {} 1020 Item_window_func(THD * thd,Item_sum * win_func,Window_spec * win_spec)1021 Item_window_func(THD *thd, Item_sum *win_func, Window_spec *win_spec) 1022 : Item_func_or_sum(thd, (Item *) win_func), 1023 window_name(NULL), window_spec(win_spec), 1024 force_return_blank(true), 1025 read_value_from_result_field(false) {} 1026 window_func()1027 Item_sum *window_func() const { return (Item_sum *) args[0]; } 1028 1029 void update_used_tables(); 1030 1031 /* 1032 This is used by filesort to mark the columns it needs to read (because they 1033 participate in the sort criteria and/or row retrieval. Window functions can 1034 only be used in sort criteria). 1035 1036 Sorting by window function value is only done after the window functions 1037 have been computed. In that case, window function will need to read its 1038 temp.table field. In order to allow that, mark that field in the read_set. 1039 */ register_field_in_read_map(void * arg)1040 bool register_field_in_read_map(void *arg) 1041 { 1042 TABLE *table= (TABLE*) arg; 1043 if (result_field && (result_field->table == table || !table)) 1044 { 1045 bitmap_set_bit(result_field->table->read_set, result_field->field_index); 1046 } 1047 return 0; 1048 } 1049 is_frame_prohibited()1050 bool is_frame_prohibited() const 1051 { 1052 switch (window_func()->sum_func()) { 1053 case Item_sum::ROW_NUMBER_FUNC: 1054 case Item_sum::RANK_FUNC: 1055 case Item_sum::DENSE_RANK_FUNC: 1056 case Item_sum::PERCENT_RANK_FUNC: 1057 case Item_sum::CUME_DIST_FUNC: 1058 case Item_sum::NTILE_FUNC: 1059 case Item_sum::PERCENTILE_CONT_FUNC: 1060 case Item_sum::PERCENTILE_DISC_FUNC: 1061 return true; 1062 default: 1063 return false; 1064 } 1065 } 1066 requires_special_cursors()1067 bool requires_special_cursors() const 1068 { 1069 switch (window_func()->sum_func()) { 1070 case Item_sum::FIRST_VALUE_FUNC: 1071 case Item_sum::LAST_VALUE_FUNC: 1072 case Item_sum::NTH_VALUE_FUNC: 1073 case Item_sum::LAG_FUNC: 1074 case Item_sum::LEAD_FUNC: 1075 return true; 1076 default: 1077 return false; 1078 } 1079 } 1080 requires_partition_size()1081 bool requires_partition_size() const 1082 { 1083 switch (window_func()->sum_func()) { 1084 case Item_sum::PERCENT_RANK_FUNC: 1085 case Item_sum::CUME_DIST_FUNC: 1086 case Item_sum::NTILE_FUNC: 1087 case Item_sum::PERCENTILE_CONT_FUNC: 1088 case Item_sum::PERCENTILE_DISC_FUNC: 1089 return true; 1090 default: 1091 return false; 1092 } 1093 } 1094 requires_peer_size()1095 bool requires_peer_size() const 1096 { 1097 switch (window_func()->sum_func()) { 1098 case Item_sum::CUME_DIST_FUNC: 1099 return true; 1100 default: 1101 return false; 1102 } 1103 } 1104 is_order_list_mandatory()1105 bool is_order_list_mandatory() const 1106 { 1107 switch (window_func()->sum_func()) { 1108 case Item_sum::RANK_FUNC: 1109 case Item_sum::DENSE_RANK_FUNC: 1110 case Item_sum::PERCENT_RANK_FUNC: 1111 case Item_sum::CUME_DIST_FUNC: 1112 case Item_sum::LAG_FUNC: 1113 case Item_sum::LEAD_FUNC: 1114 case Item_sum::PERCENTILE_CONT_FUNC: 1115 case Item_sum::PERCENTILE_DISC_FUNC: 1116 return true; 1117 default: 1118 return false; 1119 } 1120 } 1121 only_single_element_order_list()1122 bool only_single_element_order_list() const 1123 { 1124 switch (window_func()->sum_func()){ 1125 case Item_sum::PERCENTILE_CONT_FUNC: 1126 case Item_sum::PERCENTILE_DISC_FUNC: 1127 return true; 1128 default: 1129 return false; 1130 } 1131 } 1132 1133 bool check_result_type_of_order_item(); 1134 1135 1136 1137 /* 1138 Computation functions. 1139 TODO: consoder merging these with class Group_bound_tracker. 1140 */ 1141 void setup_partition_border_check(THD *thd); 1142 type_handler()1143 const Type_handler *type_handler() const 1144 { 1145 return ((Item_sum *) args[0])->type_handler(); 1146 } type()1147 enum Item::Type type() const { return Item::WINDOW_FUNC_ITEM; } 1148 1149 private: 1150 /* 1151 Window functions are very special functions, so val_() methods have 1152 special meaning for them: 1153 1154 - Phase#1, "Initial" we run the join and put its result into temporary 1155 table. For window functions, we write the default value (NULL?) as 1156 a placeholder. 1157 1158 - Phase#2: "Computation": executor does the scan in {PARTITION, ORDER BY} 1159 order of this window function. It calls appropriate methods to inform 1160 the window function about rows entering/leaving the window. 1161 It calls window_func()->val_int() so that current window function value 1162 can be saved and stored in the temp.table. 1163 1164 - Phase#3: "Retrieval" the temporary table is read and passed to query 1165 output. However, Item_window_func still remains in the select list, 1166 so item_windowfunc->val_int() will be called. 1167 During Phase#3, read_value_from_result_field= true. 1168 */ 1169 bool force_return_blank; 1170 bool read_value_from_result_field; 1171 void print_for_percentile_functions(String *str, enum_query_type query_type); 1172 1173 public: set_phase_to_initial()1174 void set_phase_to_initial() 1175 { 1176 force_return_blank= true; 1177 read_value_from_result_field= false; 1178 } set_phase_to_computation()1179 void set_phase_to_computation() 1180 { 1181 force_return_blank= false; 1182 read_value_from_result_field= false; 1183 } set_phase_to_retrieval()1184 void set_phase_to_retrieval() 1185 { 1186 force_return_blank= false; 1187 read_value_from_result_field= true; 1188 } 1189 is_null()1190 bool is_null() 1191 { 1192 if (force_return_blank) 1193 return true; 1194 1195 if (read_value_from_result_field) 1196 return result_field->is_null(); 1197 1198 return window_func()->is_null(); 1199 } 1200 val_real()1201 double val_real() 1202 { 1203 double res; 1204 if (force_return_blank) 1205 { 1206 res= 0.0; 1207 null_value= true; 1208 } 1209 else if (read_value_from_result_field) 1210 { 1211 res= result_field->val_real(); 1212 null_value= result_field->is_null(); 1213 } 1214 else 1215 { 1216 res= window_func()->val_real(); 1217 null_value= window_func()->null_value; 1218 } 1219 return res; 1220 } 1221 val_int()1222 longlong val_int() 1223 { 1224 longlong res; 1225 if (force_return_blank) 1226 { 1227 res= 0; 1228 null_value= true; 1229 } 1230 else if (read_value_from_result_field) 1231 { 1232 res= result_field->val_int(); 1233 null_value= result_field->is_null(); 1234 } 1235 else 1236 { 1237 res= window_func()->val_int(); 1238 null_value= window_func()->null_value; 1239 } 1240 return res; 1241 } 1242 val_str(String * str)1243 String* val_str(String* str) 1244 { 1245 String *res; 1246 if (force_return_blank) 1247 { 1248 null_value= true; 1249 res= NULL; 1250 } 1251 else if (read_value_from_result_field) 1252 { 1253 if ((null_value= result_field->is_null())) 1254 res= NULL; 1255 else 1256 res= result_field->val_str(str); 1257 } 1258 else 1259 { 1260 res= window_func()->val_str(str); 1261 null_value= window_func()->null_value; 1262 } 1263 return res; 1264 } 1265 val_decimal(my_decimal * dec)1266 my_decimal* val_decimal(my_decimal* dec) 1267 { 1268 my_decimal *res; 1269 if (force_return_blank) 1270 { 1271 null_value= true; 1272 res= NULL; 1273 } 1274 else if (read_value_from_result_field) 1275 { 1276 if ((null_value= result_field->is_null())) 1277 res= NULL; 1278 else 1279 res= result_field->val_decimal(dec); 1280 } 1281 else 1282 { 1283 res= window_func()->val_decimal(dec); 1284 null_value= window_func()->null_value; 1285 } 1286 return res; 1287 } 1288 get_date(MYSQL_TIME * ltime,ulonglong fuzzydate)1289 bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate) 1290 { 1291 bool res; 1292 if (force_return_blank) 1293 { 1294 null_value= true; 1295 res= true; 1296 } 1297 else if (read_value_from_result_field) 1298 { 1299 if ((null_value= result_field->is_null())) 1300 res= true; 1301 else 1302 res= result_field->get_date(ltime, fuzzydate); 1303 } 1304 else 1305 { 1306 res= window_func()->get_date(ltime, fuzzydate); 1307 null_value= window_func()->null_value; 1308 } 1309 return res; 1310 } 1311 1312 void split_sum_func(THD *thd, Ref_ptr_array ref_pointer_array, 1313 List<Item> &fields, uint flags); 1314 fix_length_and_dec()1315 bool fix_length_and_dec() 1316 { 1317 Type_std_attributes::set(window_func()); 1318 return FALSE; 1319 } 1320 func_name()1321 const char* func_name() const { return "WF"; } 1322 1323 bool fix_fields(THD *thd, Item **ref); 1324 1325 bool resolve_window_name(THD *thd); 1326 1327 void print(String *str, enum_query_type query_type); 1328 get_copy(THD * thd)1329 Item *get_copy(THD *thd) { return 0; } 1330 1331 }; 1332 1333 #endif /* ITEM_WINDOWFUNC_INCLUDED */ 1334