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

It does not stop after fetch enough rows when query with fetch first n rows with ties

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Incomplete
    • 10.6.7
    • N/A
    • Query Cache
    • None
    • Ubuntu20.04 gcc9.4.0

    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 order by a fetch first 1 rows with ties;
      SELECT FOUND_ROWS();

      Expected ressult:
      4
      Actual result:
      6

      I think following code is the reason:
      select.cc
      if (!join->do_send_rows)
      {
      /*
      If we have used Priority Queue for optimizing order by with limit,
      then stop here, there are no more records to consume.
      When this optimization is used, end_send is called on the next
      join_tab.
      */
      if (join->order &&
      join->select_options & OPTION_FOUND_ROWS &&
      join_tab > join->join_tab &&
      (join_tab - 1)>filesort && (join_tab - 1)>filesort->using_pq)

      { DBUG_PRINT("info", ("filesort NESTED_LOOP_QUERY_LIMIT")); DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT); }

      DBUG_RETURN(NESTED_LOOP_OK);
      }

      /* For WITH TIES we keep sending rows until a group has changed. */
      if (join->unit->lim.is_with_ties())

      { /* Prepare the order_fields comparison for with ties. */ if (join->send_records == join->unit->lim.get_select_limit()) (void) test_if_group_changed(join->order_fields); /* One more loop, to check if the next row matches with_ties or not. */ DBUG_RETURN(NESTED_LOOP_OK); }

      Attachments

        Activity

          People

            Unassigned Unassigned
            lgj123456 guanjun li
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.