diff options
-rw-r--r-- | mysql-test/r/win_nth_value.result | 120 | ||||
-rw-r--r-- | mysql-test/t/win_nth_value.test | 67 | ||||
-rw-r--r-- | sql/item_windowfunc.h | 30 | ||||
-rw-r--r-- | sql/sql_window.cc | 8 |
4 files changed, 196 insertions, 29 deletions
diff --git a/mysql-test/r/win_nth_value.result b/mysql-test/r/win_nth_value.result new file mode 100644 index 00000000000..abda1a2377f --- /dev/null +++ b/mysql-test/r/win_nth_value.result @@ -0,0 +1,120 @@ +create table t1 ( +pk int primary key, +a int, +b int, +c char(10), +d decimal(10, 3), +e real +); +insert into t1 values +( 1, 0, 1, 'one', 0.1, 0.001), +( 2, 0, 2, 'two', 0.2, 0.002), +( 3, 0, 3, 'three', 0.3, 0.003), +( 4, 1, 2, 'three', 0.4, 0.004), +( 5, 1, 1, 'two', 0.5, 0.005), +( 6, 1, 1, 'one', 0.6, 0.006), +( 7, 2, NULL, 'n_one', 0.5, 0.007), +( 8, 2, 1, 'n_two', NULL, 0.008), +( 9, 2, 2, NULL, 0.7, 0.009), +(10, 2, 0, 'n_four', 0.8, 0.010), +(11, 2, 10, NULL, 0.9, NULL); +select pk, +nth_value(pk, 1) over (order by pk), +nth_value(pk, 2) over (order by pk), +nth_value(pk, 0) over (order by pk), +nth_value(pk, -1) over (order by pk), +nth_value(pk, -2) over (order by pk) +from t1 +order by pk asc; +pk nth_value(pk, 1) over (order by pk) nth_value(pk, 2) over (order by pk) nth_value(pk, 0) over (order by pk) nth_value(pk, -1) over (order by pk) nth_value(pk, -2) over (order by pk) +1 1 NULL NULL NULL NULL +2 1 2 NULL NULL NULL +3 1 2 NULL NULL NULL +4 1 2 NULL NULL NULL +5 1 2 NULL NULL NULL +6 1 2 NULL NULL NULL +7 1 2 NULL NULL NULL +8 1 2 NULL NULL NULL +9 1 2 NULL NULL NULL +10 1 2 NULL NULL NULL +11 1 2 NULL NULL NULL +select pk, +nth_value(pk, pk) over (order by pk), +nth_value(pk / 0.1, pk) over (order by pk) +from t1 +order by pk asc; +pk nth_value(pk, pk) over (order by pk) nth_value(pk / 0.1, pk) over (order by pk) +1 1 10.0000 +2 2 20.0000 +3 3 30.0000 +4 4 40.0000 +5 5 50.0000 +6 6 60.0000 +7 7 70.0000 +8 8 80.0000 +9 9 90.0000 +10 10 100.0000 +11 11 110.0000 +select pk, +a, +nth_value(pk, pk) over (partition by a order by pk), +nth_value(pk, a + 1) over (partition by a order by pk) +from t1 +order by pk asc; +pk a nth_value(pk, pk) over (partition by a order by pk) nth_value(pk, a + 1) over (partition by a order by pk) +1 0 1 1 +2 0 2 1 +3 0 3 1 +4 1 NULL NULL +5 1 NULL 5 +6 1 NULL 5 +7 2 NULL NULL +8 2 NULL NULL +9 2 NULL 9 +10 2 NULL 9 +11 2 NULL 9 +select pk, +a, +nth_value(pk, 1) over (partition by a order by pk ROWS between 1 preceding and 1 following) +from t1; +pk a nth_value(pk, 1) over (partition by a order by pk ROWS between 1 preceding and 1 following) +1 0 1 +2 0 1 +3 0 2 +4 1 4 +5 1 4 +6 1 5 +7 2 7 +8 2 7 +9 2 8 +10 2 9 +11 2 10 +select pk, +a, +nth_value(a, 1) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 2) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 3) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 4) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 5) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 6) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 7) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 8) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 9) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 10) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 11) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 12) over (order by a RANGE BETWEEN 1 preceding and 1 following) +from t1 +order by pk asc; +pk a nth_value(a, 1) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 2) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 3) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 4) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 5) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 6) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 7) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 8) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 9) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 10) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 11) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 12) over (order by a RANGE BETWEEN 1 preceding and 1 following) +1 0 0 0 0 1 1 1 NULL NULL NULL NULL NULL NULL +2 0 0 0 0 1 1 1 NULL NULL NULL NULL NULL NULL +3 0 0 0 0 1 1 1 NULL NULL NULL NULL NULL NULL +4 1 0 0 0 1 1 1 2 2 2 2 2 NULL +5 1 0 0 0 1 1 1 2 2 2 2 2 NULL +6 1 0 0 0 1 1 1 2 2 2 2 2 NULL +7 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +8 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +9 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +10 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +11 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +drop table t1; diff --git a/mysql-test/t/win_nth_value.test b/mysql-test/t/win_nth_value.test new file mode 100644 index 00000000000..b9764d1e9d7 --- /dev/null +++ b/mysql-test/t/win_nth_value.test @@ -0,0 +1,67 @@ +create table t1 ( + pk int primary key, + a int, + b int, + c char(10), + d decimal(10, 3), + e real +); + +insert into t1 values +( 1, 0, 1, 'one', 0.1, 0.001), +( 2, 0, 2, 'two', 0.2, 0.002), +( 3, 0, 3, 'three', 0.3, 0.003), +( 4, 1, 2, 'three', 0.4, 0.004), +( 5, 1, 1, 'two', 0.5, 0.005), +( 6, 1, 1, 'one', 0.6, 0.006), +( 7, 2, NULL, 'n_one', 0.5, 0.007), +( 8, 2, 1, 'n_two', NULL, 0.008), +( 9, 2, 2, NULL, 0.7, 0.009), +(10, 2, 0, 'n_four', 0.8, 0.010), +(11, 2, 10, NULL, 0.9, NULL); + +select pk, + nth_value(pk, 1) over (order by pk), + nth_value(pk, 2) over (order by pk), + nth_value(pk, 0) over (order by pk), + nth_value(pk, -1) over (order by pk), + nth_value(pk, -2) over (order by pk) +from t1 +order by pk asc; + +select pk, + nth_value(pk, pk) over (order by pk), + nth_value(pk / 0.1, pk) over (order by pk) +from t1 +order by pk asc; + +select pk, + a, + nth_value(pk, pk) over (partition by a order by pk), + nth_value(pk, a + 1) over (partition by a order by pk) +from t1 +order by pk asc; + +select pk, + a, + nth_value(pk, 1) over (partition by a order by pk ROWS between 1 preceding and 1 following) +from t1; + +select pk, + a, + nth_value(a, 1) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 2) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 3) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 4) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 5) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 6) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 7) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 8) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 9) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 10) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 11) over (order by a RANGE BETWEEN 1 preceding and 1 following), + nth_value(a, 12) over (order by a RANGE BETWEEN 1 preceding and 1 following) +from t1 +order by pk asc; + +drop table t1; diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h index b3d0b7a15f3..5b368e2cdbc 100644 --- a/sql/item_windowfunc.h +++ b/sql/item_windowfunc.h @@ -385,36 +385,11 @@ class Item_sum_last_value : public Item_sum_hybrid_simple { return get_item_copy<Item_sum_last_value>(thd, mem_root, this); } }; -class Item_sum_nth_value : public Item_sum_last_value +class Item_sum_nth_value : public Item_sum_hybrid_simple { public: Item_sum_nth_value(THD *thd, Item *arg_expr, Item* offset_expr) : - Item_sum_last_value(thd, arg_expr) { - /* TODO(cvicentiu) This is messy. Item_args starts with 2 args by chance. - Clean this up by pulling out the common code from Item_sum_hybrid! */ - arg_count= 2; - args[1]= offset_expr; - } - - bool fix_fields(THD *thd, Item **ref) - { - Item *offset= args[1]; - if (offset->fix_fields(thd, args)) - return true; - /* Fix fields for the second argument as well. */ - orig_args[1]= offset; - /* Item_sum_last_value fixes fields for first argument only. */ - if (Item_sum_last_value::fix_fields(thd, ref)) - return true; - - return false; - } - - bool add() - { - Item_sum_last_value::add(); - return false; - } + Item_sum_hybrid_simple(thd, arg_expr, offset_expr) {} enum Sumfunctype sum_func() const { @@ -788,6 +763,7 @@ public: switch (window_func()->sum_func()) { case Item_sum::FIRST_VALUE_FUNC: case Item_sum::LAST_VALUE_FUNC: + case Item_sum::NTH_VALUE_FUNC: case Item_sum::LAG_FUNC: case Item_sum::LEAD_FUNC: return true; diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 1a111e52c75..a2fada64a83 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -2205,10 +2205,14 @@ void add_special_frame_cursors(THD *thd, Cursor_manager *cursor_manager, cursor_manager->add_cursor(bottom_bound); cursor_manager->add_cursor(top_bound); DBUG_ASSERT(item_sum->fixed); + Item *int_item= new (thd->mem_root) Item_int(thd, 1); + Item *offset_func= new (thd->mem_root) + Item_func_minus(thd, item_sum->get_arg(1), + int_item); + offset_func->fix_fields(thd, &offset_func); fc= new Frame_positional_cursor(*top_bound, *top_bound, *bottom_bound, - *item_sum->get_arg(1), - false); + *offset_func, false); fc->add_sum_func(item_sum); cursor_manager->add_cursor(fc); break; |