[Commits] fd55e35: Allow first/last value functions to have frame definitions

Vicentiu Ciorbaru vicentiu at mariadb.org
Wed Sep 21 17:46:45 EEST 2016


revision-id: fd55e359d7db26ee4776d7fd74232617c4751044 (mariadb-10.1.8-282-gfd55e35)
parent(s): 29e419ee1252dfe28a19df7e6e2b2a96e289c51d
author: Vicențiu Ciorbaru
committer: Vicențiu Ciorbaru
timestamp: 2016-09-21 16:45:36 +0200
message:

Allow first/last value functions to have frame definitions

Add a test case to check this behavior.

---
 mysql-test/r/win_first_last_value.result | 75 --------------------------------
 sql/item_windowfunc.h                    | 11 ++++-
 sql/sql_window.cc                        |  3 +-
 3 files changed, 12 insertions(+), 77 deletions(-)

diff --git a/mysql-test/r/win_first_last_value.result b/mysql-test/r/win_first_last_value.result
deleted file mode 100644
index 3bcab5e..0000000
--- a/mysql-test/r/win_first_last_value.result
+++ /dev/null
@@ -1,75 +0,0 @@
-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, first_value(pk) over (order by pk),
-last_value(pk) over (order by pk)
-from t1
-order by pk desc;
-pk	first_value(pk) over (order by pk)	last_value(pk) over (order by pk)
-11	1	11
-10	1	10
-9	1	9
-8	1	8
-7	1	7
-6	1	6
-5	1	5
-4	1	4
-3	1	3
-2	1	2
-1	1	1
-select pk,
-first_value(pk) over (order by pk desc),
-last_value(pk) over (order by pk desc)
-from t1;
-pk	first_value(pk) over (order by pk desc)	last_value(pk) over (order by pk desc)
-1	11	1
-2	11	2
-3	11	3
-4	11	4
-5	11	5
-6	11	6
-7	11	7
-8	11	8
-9	11	9
-10	11	10
-11	11	11
-select pk, a, b, c, d, e,
-first_value(b) over (partition by a order by pk) as fst_b,
-last_value(b) over (partition by a order by pk) as lst_b,
-first_value(c) over (partition by a order by pk) as fst_c,
-last_value(c) over (partition by a order by pk) as lst_c,
-first_value(d) over (partition by a order by pk) as fst_d,
-last_value(d) over (partition by a order by pk) as lst_d,
-first_value(e) over (partition by a order by pk) as fst_e,
-last_value(e) over (partition by a order by pk) as lst_e
-from t1;
-pk	a	b	c	d	e	fst_b	lst_b	fst_c	lst_c	fst_d	lst_d	fst_e	lst_e
-1	0	1	one	0.100	0.001	1	1	one	one	0.100	0.100	0.001	0.001
-2	0	2	two	0.200	0.002	1	2	one	two	0.100	0.200	0.001	0.002
-3	0	3	three	0.300	0.003	1	3	one	three	0.100	0.300	0.001	0.003
-4	1	2	three	0.400	0.004	2	2	three	three	0.400	0.400	0.004	0.004
-5	1	1	two	0.500	0.005	2	1	three	two	0.400	0.500	0.004	0.005
-6	1	1	one	0.600	0.006	2	1	three	one	0.400	0.600	0.004	0.006
-7	2	NULL	n_one	0.500	0.007	NULL	NULL	n_one	n_one	0.500	0.500	0.007	0.007
-8	2	1	n_two	NULL	0.008	NULL	1	n_one	n_two	0.500	NULL	0.007	0.008
-9	2	2	NULL	0.700	0.009	NULL	2	n_one	NULL	0.500	0.700	0.007	0.009
-10	2	0	n_four	0.800	0.01	NULL	0	n_one	n_four	0.500	0.800	0.007	0.01
-11	2	10	NULL	0.900	NULL	NULL	10	n_one	NULL	0.500	0.900	0.007	NULL
-drop table t1;
diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h
index ba59550..cf484e3 100644
--- a/sql/item_windowfunc.h
+++ b/sql/item_windowfunc.h
@@ -657,10 +657,19 @@ class Item_window_func : public Item_func_or_sum
     case Item_sum::PERCENT_RANK_FUNC:
     case Item_sum::CUME_DIST_FUNC:
     case Item_sum::NTILE_FUNC:
+      return true;
+    default: 
+      return false;
+    }
+  }
+
+  bool requires_special_cursors() const
+  {
+    switch (window_func()->sum_func()) {
     case Item_sum::FIRST_VALUE_FUNC:
     case Item_sum::LAST_VALUE_FUNC:
       return true;
-    default: 
+    default:
       return false;
     }
   }
diff --git a/sql/sql_window.cc b/sql/sql_window.cc
index b8f42a8..c639ef2 100644
--- a/sql/sql_window.cc
+++ b/sql/sql_window.cc
@@ -2147,7 +2147,8 @@ void get_window_functions_required_cursors(
       functions can keep the default frame generating code, overwrite it or
       add to it.
     */
-    if (item_win_func->is_frame_prohibited())
+    if (item_win_func->is_frame_prohibited() ||
+        item_win_func->requires_special_cursors())
     {
       add_special_frame_cursors(thd, cursor_manager, item_win_func);
       cursor_managers->push_back(cursor_manager);


More information about the commits mailing list