Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10879

Window Functions final ordering of result set

Details

    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

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia added a comment -

          Reviewed the patch provided on slack. Cosmetic comments only.

          psergei Sergei Petrunia added a comment - Reviewed the patch provided on slack. Cosmetic comments only.
          cvicentiu Vicențiu Ciorbaru added a comment -

          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();

          cvicentiu Vicențiu Ciorbaru added a comment - 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();
          cvicentiu Vicențiu Ciorbaru added a comment -

          Fixed via MDEV-12779

          cvicentiu Vicențiu Ciorbaru added a comment - Fixed via MDEV-12779

          People

            cvicentiu Vicențiu Ciorbaru
            cvicentiu Vicențiu Ciorbaru
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.