Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
Description
When running the following query:
select pk, a, d, sum(d) over (partition by a order by pk desc ROWS between 1 preceding and current row) as sum_d from t1; |
We get results ordered by (a, pk), not ordered by (a, pk desc). The window function values are computed according to the descending ordering, but the results end up printed as (a, pk). This can be confusing to the end user. How do we deal with this use case and others like it?
Attachments
Activity
Hi Sergey!
Can you review this patch which addresses the issue:
--- a/mysys/mf_iocache.c
|
+++ b/mysys/mf_iocache.c
|
@@ -353,6 +353,15 @@ int init_slave_io_cache(IO_CACHE *master, IO_CACHE *slave)
|
|
void end_slave_io_cache(IO_CACHE *cache)
|
{
|
+ /* Remove the cache from the next_file_user circular linked list. */
|
+ if (cache->next_file_user != cache)
|
+ {
|
+ IO_CACHE *p= cache->next_file_user;
|
+ while (p->next_file_user != cache)
|
+ p= p->next_file_user;
|
+ p->next_file_user= cache->next_file_user;
|
+
|
+ }
|
my_free(cache->buffer);
|
}
|
|
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
|
index 0ce7ff958db..d5b4d0b6ed3 100644
|
--- a/sql/sql_select.cc
|
+++ b/sql/sql_select.cc
|
@@ -26434,9 +26434,10 @@ AGGR_OP::end_send()
|
|
// Update ref array
|
join_tab->join->set_items_ref_array(*join_tab->ref_array);
|
+ bool keep_last_filesort_result = join_tab->filesort ? false : true;
|
if (join_tab->window_funcs_step)
|
{
|
- if (join_tab->window_funcs_step->exec(join))
|
+ if (join_tab->window_funcs_step->exec(join, keep_last_filesort_result))
|
return NESTED_LOOP_ERROR;
|
}
|
|
@@ -26490,6 +26491,11 @@ AGGR_OP::end_send()
|
}
|
}
|
|
+ if (keep_last_filesort_result)
|
+ {
|
+ delete join_tab->filesort_result;
|
+ join_tab->filesort_result= NULL;
|
+ }
|
// Finish rnd scn after sending records
|
if (join_tab->table->file->inited)
|
join_tab->table->file->ha_rnd_end();
|
diff --git a/sql/sql_window.cc b/sql/sql_window.cc
|
index 7ae967d7077..31ffbecd6e9 100644
|
--- a/sql/sql_window.cc
|
+++ b/sql/sql_window.cc
|
@@ -2758,7 +2758,7 @@ bool Window_func_runner::exec(THD *thd, TABLE *tbl, SORT_INFO *filesort_result)
|
}
|
|
|
-bool Window_funcs_sort::exec(JOIN *join)
|
+bool Window_funcs_sort::exec(JOIN *join, bool keep_filesort_result)
|
{
|
THD *thd= join->thd;
|
JOIN_TAB *join_tab= join->join_tab + join->exec_join_tab_cnt();
|
@@ -2773,8 +2773,11 @@ bool Window_funcs_sort::exec(JOIN *join)
|
|
bool is_error= runner.exec(thd, tbl, filesort_result);
|
|
- delete join_tab->filesort_result;
|
- join_tab->filesort_result= NULL;
|
+ if (!keep_filesort_result)
|
+ {
|
+ delete join_tab->filesort_result;
|
+ join_tab->filesort_result= NULL;
|
+ }
|
return is_error;
|
}
|
|
@@ -2883,14 +2886,17 @@ bool Window_funcs_computation::setup(THD *thd,
|
}
|
|
|
-bool Window_funcs_computation::exec(JOIN *join)
|
+bool Window_funcs_computation::exec(JOIN *join, bool keep_last_filesort_result)
|
{
|
List_iterator<Window_funcs_sort> it(win_func_sorts);
|
Window_funcs_sort *srt;
|
+ uint counter = 0; /* Count how many sorts we've executed. */
|
/* Execute each sort */
|
while ((srt = it++))
|
{
|
- if (srt->exec(join))
|
+ counter++;
|
+ bool keep_filesort_result = keep_last_filesort_result && counter == win_func_sorts.elements;
|
+ if (srt->exec(join, keep_filesort_result))
|
return true;
|
}
|
return false;
|
diff --git a/sql/sql_window.h b/sql/sql_window.h
|
index 6a56fc84392..2c5728b967f 100644
|
--- a/sql/sql_window.h
|
+++ b/sql/sql_window.h
|
@@ -195,7 +195,7 @@ class Window_funcs_sort : public Sql_alloc
|
public:
|
bool setup(THD *thd, SQL_SELECT *sel, List_iterator<Item_window_func> &it,
|
st_join_table *join_tab);
|
- bool exec(JOIN *join);
|
+ bool exec(JOIN *join, bool keep_last_filesort_result);
|
void cleanup() { delete filesort; }
|
|
friend class Window_funcs_computation;
|
@@ -225,7 +225,7 @@ class Window_funcs_computation : public Sql_alloc
|
List<Window_funcs_sort> win_func_sorts;
|
public:
|
bool setup(THD *thd, List<Item_window_func> *window_funcs, st_join_table *tab);
|
- bool exec(JOIN *join);
|
+ bool exec(JOIN *join, bool keep_last_filesort_result);
|
|
Explain_aggr_window_funcs *save_explain_plan(MEM_ROOT *mem_root, bool is_analyze);
|
void cleanup();
|
|
Reviewed the patch provided on slack. Cosmetic comments only.