MySQL Bugs: #22781: SQL_BIG_RESULT fails to influence sort plan

Bug #22781	SQL_BIG_RESULT fails to influence sort plan
Submitted:	28 Sep 2006 16:51	Modified:	23 Oct 2006 16:20
Reporter:	John David Duncan
Status:	Closed
Category:	Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.24/5.0BK	OS:
Assigned to:	Georgi Kodinov	Target Version:

Description:
Our documentation (http://dev.mysql.com/doc/refman/5.0/en/select.html) says:
"SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the
result set has many rows. In this case, MySQL directly uses disk-based temporary tables if
needed, and prefers sorting to using a temporary table with a key on the GROUP BY
elements." But this is not always true.  

This report is a continuation of bug 21174 and a restatement of the problem there, with
the same test case.  There is a related issue -- but not a duplicate, I think -- in bug
15130

How to repeat:
create table t1 (   
  pk int primary key not null auto_increment, 
  category int,
  amount float, 
  misc int  
) engine = innodb;

create table t2 like t1;
alter table t2 add index (category) ; 

create table t3 like t2;
alter table t3 engine = myisam;

DELIMITER //
CREATE PROCEDURE newrows(n int)
BEGIN
 DECLARE j int;
 SET j = 1;
 REPEAT 
  INSERT INTO t1 values (NULL, floor(rand() * 200) , rand() * 100 , 0) ;
  SET j = j + 1 ;
UNTIL j = n
END REPEAT;
END 
//

CALL newrows(250000) ;
insert into t2 select * from t1;
insert into t3 select * from t1;

 into t3 select * from t1;

 --- now look at query execution time and EXPLAINs ---
 --- t1 is OK 

 select category, sum(amount) from t1 group by category ; 
 explain  select category, sum(amount) from t1 group by category ; 

 --- t2 is worse because of the index
 select category, sum(amount) from t2 group by category ; 
 explain select category, sum(amount) from t2 group by category ; 

 --- and these things don't help:
  explain select SQL_BIG_RESULT category, sum(amount) from t2 (category) group by
category;
  explain select SQL_BIG_RESULT category, sum(amount) from t2 IGNORE INDEX(category) 
group by category ;

 --- data point: t3 behaves differently in some cases (myisam vs. innodb?)

Thank you for the bug report. Verified as described changing some copy-paste
mistakes in the test case.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12897

ChangeSet@1.2293, 2006-09-30 14:14:40+03:00, gkodinov@macbook.local +8 -0
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   There is a check for SQL_BIG_RESULT at compile time that will
   disable using the index for GROUP BY.
   However on executuon time this is re-checked to account for the
   additional optimizations made inbetween.
   So we must disable skiping the sort order for GROUP BY also at 
   execution time if SQL_BIG_RESULT is on.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13340

ChangeSet@1.2293, 2006-10-09 19:51:41+04:00, gkodinov@macbook.local +8 -0
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   Currently SQL_BIG_RESULT is checked only at compile time.
   However, additional optimizations may take place after
   this check that change the sort method from 'filesort'
   to sorting via index. As a result the actual plan
   executed is not the one specified by the SQL_BIG_RESULT
   hint. Similarly, there is no such test when executing
   EXPLAIN, resulting in incorrect output.
   The patch corrects the problem by testing for
   SQL_BIG_RESULT both during the explain and execution
   phases.

Pushed in 5.0.27/5.1.13-beta

Noted in 5.0.27, 5.1.13 changelogs.

Use of SQL_BIG_RESULT did not influence the sort plan for query execution.