Bug#68458 Wrong count from FOUND_ROWS() on MySQL 5.6

Tor Didriksen · Tor Didriksen · commit fcff3f9dff47 · 2013-02-28T16:01:55.000+01:00
Bug#16383173 WRONG COUNT FROM FOUND_ROWS() ON MYSQL 5.6

The handling of SQL_CALC_FOUND_ROWS in combination with
"order by with limit" was not specific enough,
and could lead to wrong results for FOUND_ROWS() 

The bug was that we took 'limit_found_rows' from filesort()
for cases where filesort() did not produce the final result.

Solution: let filesort() tell the execution machinery
that a priority queue was in fact used to filter the result.
diff --git a/sql/filesort.cc b/sql/filesort.cc
@@ -280,6 +280,7 @@ ha_rows filesort(THD *thd, TABLE *table, Filesort *filesort,
     }
     // For PQ queries (with limit) we initialize all pointers.
     table_sort.init_record_pointers();
+    filesort->using_pq= true;
   }
   else
   {
diff --git a/sql/filesort.h b/sql/filesort.h
@@ -44,13 +44,16 @@ class Filesort: public Sql_alloc
   SQL_SELECT *select;
   /** TRUE <=> free select on destruction */
   bool own_select;
+  /** true means we are using Priority Queue for order by with limit. */
+  bool using_pq;
 
   Filesort(ORDER *order_arg, ha_rows limit_arg, SQL_SELECT *select_arg):
     order(order_arg),
     limit(limit_arg),
     sortorder(NULL),
     select(select_arg),
-    own_select(false)
+    own_select(false), 
+    using_pq(false)
   {
     DBUG_ASSERT(order);
   };
diff --git a/sql/sql_executor.cc b/sql/sql_executor.cc
@@ -933,7 +933,15 @@ do_select(JOIN *join)
   }
 
   join->thd->limit_found_rows= join->send_records;
-  /* Use info provided by filesort. */
+  /*
+    Use info provided by filesort for "order by with limit":
+
+    When using a Priority Queue, we cannot rely on send_records, but need
+    to use the rowcount read originally into the join_tab applying the
+    filesort. There cannot be any post-filtering conditions, nor any
+    following join_tabs in this case, so this rowcount properly represents
+    the correct number of qualifying rows.
+  */
   if (join->order)
   {
     // Save # of found records prior to cleanup
@@ -950,7 +958,7 @@ do_select(JOIN *join)
       sort_tab= join_tab + const_tables;
     }
     if (sort_tab->filesort &&
-        sort_tab->filesort->sortorder)
+        sort_tab->filesort->using_pq)
     {
       join->thd->limit_found_rows= sort_tab->records;
     }
@@ -2773,16 +2781,15 @@ end_send(JOIN *join, JOIN_TAB *join_tab, bool end_of_records)
         !join->do_send_rows)
     {
       /*
-        If filesort is used for sorting, stop after select_limit_cnt+1
-        records are read. Because of optimization in some cases it can
-        provide only select_limit_cnt+1 records.
+        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->sortorder)
+          (join_tab - 1)->filesort && (join_tab - 1)->filesort->using_pq)
       {
         DBUG_PRINT("info", ("filesort NESTED_LOOP_QUERY_LIMIT"));
         DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT);

Original file line number	Diff line number	Diff line change
`@@ -280,6 +280,7 @@ ha_rows filesort(THD thd, TABLE table, Filesort *filesort,`
`280`	`280`	`}`
`281`	`281`	`// For PQ queries (with limit) we initialize all pointers.`
`282`	`282`	`table_sort.init_record_pointers();`
	`283`	`+ filesort->using_pq= true;`
`283`	`284`	`}`
`284`	`285`	`else`
`285`	`286`	`{`