summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/win_first_last_value.result25
-rw-r--r--mysql-test/t/win_first_last_value.test17
-rw-r--r--sql/item_windowfunc.cc12
-rw-r--r--sql/item_windowfunc.h13
-rw-r--r--sql/sql_window.cc24
5 files changed, 59 insertions, 32 deletions
diff --git a/mysql-test/r/win_first_last_value.result b/mysql-test/r/win_first_last_value.result
index ba896a4a4f1..ed142f646e6 100644
--- a/mysql-test/r/win_first_last_value.result
+++ b/mysql-test/r/win_first_last_value.result
@@ -102,3 +102,28 @@ pk a b c d e fst_b lst_b fst_c lst_c fst_d lst_d fst_e lst_e
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;
+#
+# MDEV-11746: Wrong result upon using FIRST_VALUE with a window frame
+#
+create table t1 (i int);
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+select i,
+first_value(i) OVER (order by i rows between CURRENT ROW and 1 FOLLOWING) as fst_1f,
+last_value(i) OVER (order by i rows between CURRENT ROW and 1 FOLLOWING) as last_1f,
+first_value(i) OVER (order by i rows between 1 PRECEDING AND 1 FOLLOWING) as fst_1p1f,
+last_value(i) OVER (order by i rows between 1 PRECEDING AND 1 FOLLOWING) as fst_1p1f,
+first_value(i) OVER (order by i rows between 2 PRECEDING AND 1 PRECEDING) as fst_2p1p,
+last_value(i) OVER (order by i rows between 2 PRECEDING AND 1 PRECEDING) as fst_2p1p
+from t1;
+i fst_1f last_1f fst_1p1f fst_1p1f fst_2p1p fst_2p1p
+1 1 2 1 2 NULL NULL
+2 2 3 1 3 1 1
+3 3 4 2 4 1 2
+4 4 5 3 5 2 3
+5 5 6 4 6 3 4
+6 6 7 5 7 4 5
+7 7 8 6 8 5 6
+8 8 9 7 9 6 7
+9 9 10 8 10 7 8
+10 10 10 9 10 8 9
+drop table t1;
diff --git a/mysql-test/t/win_first_last_value.test b/mysql-test/t/win_first_last_value.test
index 5948cefe18a..6fc694560cf 100644
--- a/mysql-test/t/win_first_last_value.test
+++ b/mysql-test/t/win_first_last_value.test
@@ -56,3 +56,20 @@ select pk, a, b, c, d, e,
from t1;
drop table t1;
+
+--echo #
+--echo # MDEV-11746: Wrong result upon using FIRST_VALUE with a window frame
+--echo #
+create table t1 (i int);
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+
+select i,
+ first_value(i) OVER (order by i rows between CURRENT ROW and 1 FOLLOWING) as fst_1f,
+ last_value(i) OVER (order by i rows between CURRENT ROW and 1 FOLLOWING) as last_1f,
+ first_value(i) OVER (order by i rows between 1 PRECEDING AND 1 FOLLOWING) as fst_1p1f,
+ last_value(i) OVER (order by i rows between 1 PRECEDING AND 1 FOLLOWING) as fst_1p1f,
+ first_value(i) OVER (order by i rows between 2 PRECEDING AND 1 PRECEDING) as fst_2p1p,
+ last_value(i) OVER (order by i rows between 2 PRECEDING AND 1 PRECEDING) as fst_2p1p
+from t1;
+
+drop table t1;
diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc
index fb2ad666018..59a22c63ed5 100644
--- a/sql/item_windowfunc.cc
+++ b/sql/item_windowfunc.cc
@@ -221,18 +221,6 @@ void Item_sum_percent_rank::setup_window_func(THD *thd, Window_spec *window_spec
clear();
}
-bool Item_sum_first_value::add()
-{
- if (value_added)
- return false;
-
- /* TODO(cvicentiu) This is done like this due to how Item_sum_hybrid works.
- For this usecase we can actually get rid of arg_cache. arg_cache is just
- for running a comparison function. */
- value_added= true;
- Item_sum_hybrid_simple::add();
- return false;
-}
bool Item_sum_hybrid_simple::fix_fields(THD *thd, Item **ref)
{
diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h
index 2beed69c0fa..b4953c8a6ac 100644
--- a/sql/item_windowfunc.h
+++ b/sql/item_windowfunc.h
@@ -331,16 +331,8 @@ class Item_sum_first_value : public Item_sum_hybrid_simple
{
public:
Item_sum_first_value(THD* thd, Item* arg_expr) :
- Item_sum_hybrid_simple(thd, arg_expr),
- value_added(false) {}
-
- bool add();
+ Item_sum_hybrid_simple(thd, arg_expr) {}
- void clear()
- {
- value_added= false;
- Item_sum_hybrid_simple::clear();
- }
enum Sumfunctype sum_func () const
{
@@ -354,9 +346,6 @@ class Item_sum_first_value : public Item_sum_hybrid_simple
Item *get_copy(THD *thd, MEM_ROOT *mem_root)
{ return get_item_copy<Item_sum_first_value>(thd, mem_root, this); }
-
- private:
- bool value_added;
};
/*
diff --git a/sql/sql_window.cc b/sql/sql_window.cc
index e9e531714f6..668c6a41e08 100644
--- a/sql/sql_window.cc
+++ b/sql/sql_window.cc
@@ -2311,14 +2311,6 @@ void add_special_frame_cursors(THD *thd, Cursor_manager *cursor_manager,
fc->add_sum_func(item_sum);
cursor_manager->add_cursor(fc);
break;
- case Item_sum::FIRST_VALUE_FUNC:
- fc= get_frame_cursor(thd, spec, true);
- fc->set_no_action();
- cursor_manager->add_cursor(fc);
- fc= new Frame_positional_cursor(*fc);
- fc->add_sum_func(item_sum);
- cursor_manager->add_cursor(fc);
- break;
case Item_sum::LAST_VALUE_FUNC:
fc= get_frame_cursor(thd, spec, false);
fc->add_sum_func(item_sum);
@@ -2347,6 +2339,22 @@ void add_special_frame_cursors(THD *thd, Cursor_manager *cursor_manager,
cursor_manager->add_cursor(fc);
break;
}
+ case Item_sum::FIRST_VALUE_FUNC:
+ {
+ Frame_cursor *bottom_bound= get_frame_cursor(thd, spec, false);
+ Frame_cursor *top_bound= get_frame_cursor(thd, spec, true);
+ cursor_manager->add_cursor(bottom_bound);
+ cursor_manager->add_cursor(top_bound);
+ DBUG_ASSERT(item_sum->fixed);
+ Item *offset_item= new (thd->mem_root) Item_int(thd, 0);
+ offset_item->fix_fields(thd, &offset_item);
+ fc= new Frame_positional_cursor(*top_bound,
+ *top_bound, *bottom_bound,
+ *offset_item, false);
+ fc->add_sum_func(item_sum);
+ cursor_manager->add_cursor(fc);
+ break;
+ }
case Item_sum::NTH_VALUE_FUNC:
{
Frame_cursor *bottom_bound= get_frame_cursor(thd, spec, false);
View Lorry Controller Status