Bug #22781 SQL_BIG_RESULT fails to influence sort plan

• Submitted: 28 Sep 2006 14:51
• Modified: 20 Jun 2010 22:35
• Reporter: John David Duncan
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.0.24/5.0BK
• OS: Any
• Assigned to: Georgi Kodinov
• CPU Architecture: Any

[28 Sep 2006 14:51] John David Duncan

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?)

[28 Sep 2006 16:14] MySQL Verification Team

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

[30 Sep 2006 11:15] Bugs System

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, [e-mail] +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.

[9 Oct 2006 15:58] Bugs System

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, [e-mail] +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.

[21 Oct 2006 9:15] Georgi Kodinov

Pushed in 5.0.27/5.1.13-beta

[23 Oct 2006 14:20] Paul DuBois

Noted in 5.0.27, 5.1.13 changelogs.

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

[5 May 2010 15:12] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 2:27] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

[28 May 2010 6:02] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:30] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:58] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[30 May 2010 0:32] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[17 Jun 2010 12:05] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:50] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:32] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)