[MDEV-29379] It does not stop after fetch enough rows when query with fetch first n rows with ties Created: 2022-08-25  Updated: 2023-02-05  Resolved: 2023-02-05

Status: Closed
Project: MariaDB Server
Component/s: Query Cache
Affects Version/s: 10.6.7
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: guanjun li Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Environment:

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

 Comments   
Comment by Alice Sherepa [ 2022-11-03 ]

Could you please explain why the expected result is 4?

Generated at Thu Feb 08 10:08:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.