Bug #44290 crash in SQL_SELECT::test_quick_select for query with distinct or order/group by
Submitted: 15 Apr 2009 11:54 Modified: 28 Jun 2009 0:52
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.1.33, 5.1.35, 6.0.11 OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: regression

[15 Apr 2009 11:54] Shane Bester
Description:
mysqld.exe!SQL_SELECT::test_quick_select()[opt_range.cc:2212]
mysqld.exe!test_if_skip_sort_order()[sql_select.cc:13212]
mysqld.exe!JOIN::exec()[sql_select.cc:1757]
mysqld.exe!mysql_select()[sql_select.cc:2380]
mysqld.exe!mysql_explain_union()[sql_select.cc:16359]
mysqld.exe!select_describe()[sql_select.cc:16300]
mysqld.exe!JOIN::exec()[sql_select.cc:1764]
mysqld.exe!mysql_select()[sql_select.cc:2380]
mysqld.exe!mysql_explain_union()[sql_select.cc:16359]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4889]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2204]
mysqld.exe!mysql_parse()[sql_parse.cc:5835]
mysqld.exe!dispatch_command()[sql_parse.cc:1218]
mysqld.exe!do_command()[sql_parse.cc:857]
mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:277]
kernel32.dll!BaseThreadStart()

How to repeat:
drop table if exists `t2`;
create table `t2` (`c1` int,`c2` int,`c3` int,`c4` int,key (`c3`),key (`c4`),key (`c2`,`c3`)) engine=innodb;
insert into `t2` values (1,1,1,3);
insert into `t2` values (1,1,1,1);
insert into `t2` values (1,1,2,1);
insert into `t2` values (1,1,1,3);
insert into `t2` values (1,1,2,1);

explain select 1 from ( select count(distinct `c1`) from `t2`where  `c2` in (1,1) and `c3` = 2 and `c4` = 3 group by `c2` ) x;
[15 Apr 2009 12:36] MySQL Verification Team
5,1 bzr call stack

Attachment: callstack44290.txt (text/plain), 2.85 KiB.

[17 Apr 2009 19:45] 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/72429

2858 Gleb Shchepa	2009-04-18
      Bug #44290: explain crashes for subquery with distinct in
                  SQL_SELECT::test_quick_select
                  
      The crash was caused by a beforehand cleanup of JOIN_TAB::select
      during the filesort. Queries combining quick index access and
      filesort was affected.
      
      Unnecessary cleanup has been deferred to prevent
      inconsistent join structure passing into
      mysql_explain_union/test_if_skip_sort_order functions.
     @ mysql-test/include/mix1.inc
        Add test case for bug #44290.
     @ mysql-test/r/innodb_mysql.result
        Add test case for bug #44290.
     @ sql/sql_select.cc
        Bug #44290: explain crashes for subquery with distinct in
                    SQL_SELECT::test_quick_select
        
        The create_sort_index function has been modified to defer
        unneeded cleanup and keep consistent quick index access 
        fields till the test_quick_select function call (like
        at the end of the mysql_derived_filling function).
[24 Apr 2009 16:30] 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/72791

2858 Gleb Shchepa	2009-04-24
      Bug #44290: explain crashes for subquery with distinct in
                  SQL_SELECT::test_quick_select
            
      The crash was caused by an incomplete cleanup of JOIN_TAB::select
      during the filesort of rows for GROUP BY clause inside a subquery.
      Queries where a quick index access is replaced with filesort was
      was affected. For example:
      
        SELECT 1 FROM
          (SELECT COUNT(DISTINCT c1) FROM t1
             WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x
      
      Quick index access related data in the SQL_SELECT::test_quick_select
      function was inconsistent after an incomplete cleanup.
      This function has been completed to prevent crashes in the
      SQL_SELECT::test_quick_select function.
     @ mysql-test/include/mix1.inc
        Add test case for bug #44290.
     @ mysql-test/r/innodb_mysql.result
        Add test case for bug #44290.
     @ sql/sql_select.cc
        Bug #44290: explain crashes for subquery with distinct in
                    SQL_SELECT::test_quick_select
        
        Quick index access related data in the SQL_SELECT::test_quick_select
        function was inconsistent after an incomplete cleanup.
        This function has been completed to prevent crashes in the
        SQL_SELECT::test_quick_select function.
[12 May 2009 18:52] 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/73864

2874 Gleb Shchepa	2009-05-12
      Bug #44290: explain crashes for subquery with distinct in
                  SQL_SELECT::test_quick_select
      
      The crash was caused by an incomplete cleanup of JOIN_TAB::select
      during the filesort of rows for GROUP BY clause inside a subquery.
      Queries where a quick index access is replaced with filesort was
      was affected. For example:
      
        SELECT 1 FROM
          (SELECT COUNT(DISTINCT c1) FROM t1
             WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x
      
      Quick index access related data in the SQL_SELECT::test_quick_select
      function was inconsistent after an incomplete cleanup.
      This function has been completed to prevent crashes in the
      SQL_SELECT::test_quick_select function.
     @ mysql-test/include/mix1.inc
        Add test case for bug #44290.
     @ mysql-test/r/innodb_mysql.result
        Add test case for bug #44290.
     @ sql/sql_select.cc
        Bug #44290: explain crashes for subquery with distinct in
                    SQL_SELECT::test_quick_select
        
        Quick index access related data in the SQL_SELECT::test_quick_select
        function was inconsistent after an incomplete cleanup.
        This function has been completed to prevent crashes in the
        SQL_SELECT::test_quick_select function.
[13 May 2009 7:52] 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/73903

2858 Gleb Shchepa	2009-05-13
      Bug #44290: explain crashes for subquery with distinct in
                  SQL_SELECT::test_quick_select
      
      The crash was caused by an incomplete cleanup of JOIN_TAB::select
      during the filesort of rows for GROUP BY clause inside a subquery.
      Queries where a quick index access is replaced with filesort was
      was affected. For example:
      
        SELECT 1 FROM
          (SELECT COUNT(DISTINCT c1) FROM t1
             WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x
      
      Quick index access related data in the SQL_SELECT::test_quick_select
      function was inconsistent after an incomplete cleanup.
      This function has been completed to prevent crashes in the
      SQL_SELECT::test_quick_select function.
     @ mysql-test/include/mix1.inc
        Add test case for bug #44290.
     @ mysql-test/r/innodb_mysql.result
        Add test case for bug #44290.
     @ sql/sql_select.cc
        Bug #44290: explain crashes for subquery with distinct in
                    SQL_SELECT::test_quick_select
        
        Quick index access related data in the SQL_SELECT::test_quick_select
        function was inconsistent after an incomplete cleanup.
        This function has been completed to prevent crashes in the
        SQL_SELECT::test_quick_select function.
[13 May 2009 7:55] Bugs System
Pushed into 5.1.35 (revid:gshchepa@mysql.com-20090513075139-g50shsfjaf1dstdn) (version source revid:gshchepa@mysql.com-20090513075139-g50shsfjaf1dstdn) (merge vers: 5.1.35) (pib:6)
[15 May 2009 1:34] Paul DuBois
Noted in 5.1.35 changelog.

Incomplete cleanup of JOIN_TAB::select during the filesort of rows
for a GROUP BY clause inside a subquery caused a server crash.

Setting report to NDI pending push into 6.0.x.
[28 May 2009 8:14] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:gshchepa@mysql.com-20090512174231-qrc11b5bk7a66r8o) (merge vers: 5.1.35) (pib:6)
[1 Jun 2009 20:10] Paul DuBois
Setting report to NDI pending push into 6.0.x.
[15 Jun 2009 8:27] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:07] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:47] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)
[17 Jun 2009 19:21] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:joro@sun.com-20090515134506-5mq3a8fafgbkx6u1) (merge vers: 6.0.12-alpha) (pib:11)
[28 Jun 2009 0:52] Paul DuBois
Noted in 5.4.4 changelog.
[10 Jul 2009 23:19] Bugs System
Pushed into 5.1.37 (revid:build@mysql.com-20090710231213-9guqdu0avc0uwdkp) (version source revid:build@mysql.com-20090710231213-9guqdu0avc0uwdkp) (merge vers: 5.1.37) (pib:11)
[23 Jul 2009 10:24] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090723102221-ps4uaphwbxzj8p0q) (version source revid:joerg@mysql.com-20090721145751-rqqnhv0kage18wfi) (merge vers: 5.4.4-alpha) (pib:11)
[12 Aug 2009 22:35] Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:53] Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:45] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[7 Oct 2009 20:26] Paul DuBois
The 5.4 fix has been pushed to 5.4.2.