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

The return rows has error when a statement has group by, order by and fetch with ties

    XMLWordPrintable

    Details

      Description

      Test case:

      create table t1 (a int, b int, index (a,b));
      insert into t1 values (1,1), (1,2), (1,3), (2,1), (2,2), (3,1);
      select SQL_CALC_FOUND_ROWS a, b from t1 group by a,b order by a fetch first 1 rows with ties;
      expected result:
      +------+------+
      | a    | b    |
      +------+------+
      |    1 |    1 |
      |    1 |    2 |
      |    1 |    3 |
      +------+------+
      actual result:
      +------+------+
      | a    | b    |
      +------+------+
      |    1 |    1 |
      |    1 |    2 |
      |    1 |    3 |
      |    2 |    1 |
      |    2 |    2 |
      |    3 |    1 |
      +------+------+
      

      The reason:
      Due to the order by field is the beginning part of group by fields, so it will neglect order by field to avoid reorder when group by will order the records.
      Below is the code in file sql_select.cc

        /*
          Remove ORDER BY in the following cases:
          - GROUP BY is more specific. Example GROUP BY a, b ORDER BY a
          - If there are aggregate functions and no GROUP BY, this always leads
            to one row result, no point in sorting.
        */
        if (test_if_subpart(group_list, order) ||
            (!group_list && tmp_table_param.sum_func_count))
        {
          order= 0;
          if (is_indexed_agg_distinct(this, NULL))
            sort_and_group= 0;
        }
      

      But if the statement has with ties, it will use order fields to judge if the order fields have same values with previous records. So here will result error and can not know how to stop to get next record.

      sql_select.cc: 
        if (join->send_records >= join->unit->lim.get_select_limit() &&
            join->unit->lim.is_with_ties())
        {
          /*
            Stop sending rows if the order fields corresponding to WITH TIES
            have changed.
          */
          int idx= test_if_item_cache_changed(join->order_fields);
          if (idx >= 0)
            join->do_send_rows= false;
        }
      

      Due to join->order_fields is null, idx will always equal -1 and join->do_send_rows always equal true.

      To fix this bug, I update with following code.

      --- a/sql/sql_select.cc
      +++ b/sql/sql_select.cc
      @@ -3960,7 +3960,7 @@ bool JOIN::make_aggr_tables_info()
         */
         if (unit->lim.is_with_ties())
         {
      -    if (alloc_order_fields(this, order, with_ties_order_count))
      +    if (alloc_order_fields(this, order ? order : group_list, with_ties_order_count))
             DBUG_RETURN(true);
         }
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              lgj123456 guanjun li
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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