Bug #48131 crash group by with rollup, distinct, filesort, with temporary tables

• Submitted: 17 Oct 2009 20:48
• Modified: 18 Jun 2010 2:12
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.0.85, 5.1.39, 5.1.41
• OS: Any
• Assigned to: Alexey Kopytov
• CPU Architecture: Any

[17 Oct 2009 20:48] Shane Bester

Description:
5.1.39 stack trace:
mysqld-debug.exe!hp_rec_hashnr()[hp_hash.c:354]
mysqld-debug.exe!hp_write_key()[hp_write.c:342]
mysqld-debug.exe!heap_write()[hp_write.c:51]
mysqld-debug.exe!ha_heap::write_row()[ha_heap.cc:224]
mysqld-debug.exe!handler::ha_write_row()[handler.cc:4623]
mysqld-debug.exe!end_write()[sql_select.cc:12200]
mysqld-debug.exe!evaluate_join_record()[sql_select.cc:11250]
mysqld-debug.exe!sub_select()[sql_select.cc:11135]
mysqld-debug.exe!do_select()[sql_select.cc:10891]
mysqld-debug.exe!JOIN::exec()[sql_select.cc:1799]
mysqld-debug.exe!mysql_select()[sql_select.cc:2400]
mysqld-debug.exe!handle_select()[sql_select.cc:268]
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:5011]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2206]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5935]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1215]
mysqld-debug.exe!do_command()[sql_parse.cc:854]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:295]
mysqld-debug.exe!_threadstart()[thread.c:277]
kernel32.dll!BaseThreadStart()

How to repeat:
select distinct	`alias_col0`
from `t1`
natural left outer join  
(
  select   
   1 as `alias_col0`,
   1 as `alias_col23`
  from
    ( select '1','2','3','4','5','6','7') as `d1`
    inner join
    ( select 1 as `alias_col7` from `t2`) as `d2`
    on 1=1
) as `d2`
group by 
`alias_col0`,`a`,`alias_col23`
with rollup
;

[17 Oct 2009 20:49] MySQL Verification Team

valgrind output showing invalid reads

Attachment: bug48131_valgrind_errors_5.1.41.txt (text/plain), 6.34 KiB.

[17 Oct 2009 20:50] MySQL Verification Team

the table structures are:

drop table if exists `t1`,`t2`;
create table `t1`(`a` int primary key);
insert into `t1` values (1),(2);
create table `t2`(`a` int primary key);

[18 Oct 2009 7:04] MySQL Verification Team

when the temporary table is myisam instead of memory, the stack trace could look like this:

mysqld-debug.exe!mi_unique_comp()[mi_unique.c:165]
mysqld-debug.exe!_mi_cmp_static_unique()[mi_statrec.c:159]
mysqld-debug.exe!mi_check_unique()[mi_unique.c:46]
mysqld-debug.exe!mi_write()[mi_write.c:94]
mysqld-debug.exe!ha_myisam::write_row()[ha_myisam.cc:773]
mysqld-debug.exe!handler::ha_write_row()[handler.cc:4623]
mysqld-debug.exe!end_write()[sql_select.cc:12200]
mysqld-debug.exe!evaluate_join_record()[sql_select.cc:11250]
mysqld-debug.exe!sub_select()[sql_select.cc:11141]
mysqld-debug.exe!do_select()[sql_select.cc:10891]
mysqld-debug.exe!JOIN::exec()[sql_select.cc:1799]
mysqld-debug.exe!mysql_select()[sql_select.cc:2400]
mysqld-debug.exe!handle_select()[sql_select.cc:268]
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:5011]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2206]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5935]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1215]
mysqld-debug.exe!do_command()[sql_parse.cc:854]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:295]
mysqld-debug.exe!_threadstart()[thread.c:277]
kernel32.dll!BaseThreadStart()

[19 Oct 2009 2:17] MySQL Verification Team

and this, my friends, is my simplified testcase:

drop table if exists `t1`,`t4`;
create table `t1`(`id` int not null primary key)engine=myisam;
insert into `t1` values (1),(2);
create table `t4`(`a` longblob)engine=myisam;
insert into `t4` values ('1');
select distinct `a`
from `t4`
inner join  `t1` on 1=1
group by `id`,`a`
with rollup;

[30 Oct 2009 15:55] 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/88774

2820 Alexey Kopytov	2009-10-30
      Bug #48131: crash group by with rollup, distinct, filesort,
                  with temporary tables
      
      There were two problems the test case from this bug was
      triggering:
      
      1. JOIN::rollup_init() was supposed to wrap all constant Items
      into another object for queries with the WITH ROLLUP modifier
      to ensure they are never considered as constants and therefore
      are written into temporary tables if the optimizer chooses to
      employ them for DISTINCT/GROUP BY handling.
      
      However, JOIN::rollup_init() was called before
      make_join_statistics(), so Items corresponding to fields in
      const tables could not be handled as intended, which was
      causing all kinds of problems later in the query execution. In
      particular, create_tmp_table() assumed all constant items
      except "hidden" ones to be removed earlier by remove_const()
      which led to improperly initialized Field objects for the
      temporary table being created. This is what was causing crashes
      and valgrind errors in storage engines.
      
      2. Even when the above problem had been fixed, the query from
      the test case produced incorrect results due to some
      DISTINCT/GROUP BY optimizations being performed by the
      optimizer that are inapplicable in the WITH ROLLUP case.
      
      Fixed by disabling inapplicable DISTINCT/GROUP BY optimizations
      when the WITH ROLLUP modifier is present, and splitting the
      const-wrapping part of JOIN::rollup_init() into a separate
      method which is now invoked after make_join_statistics() when
      the const tables are already known.
     @ mysql-test/r/olap.result
        Added a test case for bug #48131.
     @ mysql-test/t/olap.test
        Added a test case for bug #48131.
     @ sql/sql_select.cc
        1. Disabled inapplicable DISTINCT/GROUP BY optimizations when
        the WITH ROLLUP modifier is present.
        2. Split the const-wrapping part of JOIN::rollup_init() into a
        separate method.
     @ sql/sql_select.h
        Added rollup_process_const_fields() declaration.

[4 Nov 2009 9:16] Bugs System

Pushed into 5.0.88 (revid:[e-mail]-20091104091355-hpz6dwgkrfmokj3k) (version source revid:[e-mail]-20091030161625-je1oafkrlafkdd9y) (merge vers: 5.0.88) (pib:13)

[4 Nov 2009 9:24] Bugs System

Pushed into 5.1.41 (revid:[e-mail]-20091104092152-qz96bzlf2o1japwc) (version source revid:[e-mail]-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)

[11 Nov 2009 6:49] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091110093407-rw5g8dys2baqkt67) (version source revid:[e-mail]-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)

[11 Nov 2009 6:57] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091109115615-nuohp02h8mdrz8m2) (version source revid:[e-mail]-20091105093726-d3rrbz9orzoqdwa6) (merge vers: 5.5.0-beta) (pib:13)

[13 Nov 2009 2:14] Paul DuBois

Noted in 5.0.88, 5.1.41, 5.5.0, 6.0.14 changelogs.

A combination of GROUP BY WITH ROLLUP, DISTINCT and the 'const' join
type in a query caused a server crash when the optimizer chose to
employ a temporary table to resolve DISTINCT.

[7 Dec 2009 16:44] Paul DuBois

Noted in 5.1.40sp1 changelog.

[8 Dec 2009 9:30] Bugs System

Pushed into 5.1.43 (revid:[e-mail]-20091208092611-pbno5awyb0v38hs7) (version source revid:[e-mail]-20091208092611-pbno5awyb0v38hs7) (merge vers: 5.1.43) (pib:13)

[16 Dec 2009 8:41] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091216083311-xorsasf5kopjxshf) (version source revid:[e-mail]-20091215065750-5m04ogppd5l0pol5) (merge vers: 6.0.14-alpha) (pib:14)

[16 Dec 2009 8:47] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091216082430-s0gtzibcgkv4pqul) (version source revid:[e-mail]-20091211070127-kl8uvlrv9cr11kva) (merge vers: 5.5.0-beta) (pib:14)

[16 Dec 2009 8:54] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20091216083231-rp8ecpnvkkbhtb27) (version source revid:[e-mail]-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

[18 Dec 2009 10:37] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:53] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 11:08] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:22] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

[17 Feb 2010 16:20] Paul DuBois

Noted in 5.0.87sp1 changelog.

[20 Feb 2010 17:11] Bugs System

Pushed into 5.0.91 (revid:[e-mail]-20100220170835-5kr6ztsg25va7qzz) (version source revid:[e-mail]-20100220170835-5kr6ztsg25va7qzz) (merge vers: 5.0.91) (pib:16)

[1 Mar 2010 8:46] Bugs System

Pushed into 5.1.45 (revid:[e-mail]-20100301083827-xnimmrjg6bh33o1o) (version source revid:[e-mail]-20100226131646-kpvzk740hxbtaexn) (merge vers: 5.1.45) (pib:16)

[2 Mar 2010 14:33] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:[e-mail]-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)

[2 Mar 2010 14:38] Bugs System

Pushed into 5.5.3-m2 (revid:[e-mail]-20100302072233-t3uqgjzdukt1pyhe) (version source revid:[e-mail]-20100301090215-63o2w2y16go8n53p) (merge vers: 5.5.3-m2) (pib:16)

[2 Mar 2010 14:43] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100302072432-k8xvfkgcggkwgi94) (version source revid:[e-mail]-20100301094536-2zc4uqyy3os8san7) (pib:16)

[12 Mar 2010 14:17] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

[12 Mar 2010 14:33] Bugs System

Pushed into 5.1.44-ndb-6.2.19 (revid:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

[12 Mar 2010 14:49] Bugs System

Pushed into 5.1.44-ndb-6.3.33 (revid:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

[17 Jun 2010 12:00] 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:40] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)

[17 Jun 2010 13:26] 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)