Bug #37842 Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
Submitted: 3 Jul 2008 17:05 Modified: 20 Nov 2010 23:07
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:6.0 OS:Any
Assigned to: Sergey Petrunya
Triage: Triaged: D1 (Critical)

[3 Jul 2008 17:05] Philip Stoev
Description:
When executing a workload containing randomly generated queries tailored to exercise the subquery optimizer, mysqld crashed as follows:

#0  0x00110416 in __kernel_vsyscall ()
#1  0x00581c78 in pthread_kill () from /lib/libpthread.so.0
#2  0x085b0dc4 in my_write_core (sig=6) at stacktrace.c:307
#3  0x08213e88 in handle_segfault (sig=6) at mysqld.cc:2638
#4  <signal handler called>
#5  0x00110416 in __kernel_vsyscall ()
#6  0x003e6660 in raise () from /lib/libc.so.6
#7  0x003e8028 in abort () from /lib/libc.so.6
#8  0x003df57e in __assert_fail () from /lib/libc.so.6
#9  0x08309a15 in DsMrr_impl::dsmrr_init (this=0x99ae13c, h=0x99ae000, key=0x99ae2f4, seq_funcs=0xadb39c00, seq_init_param=0x99f40f8, n_ranges=2, mode=4,
    buf=0x9a537d0) at handler.cc:4307
#10 0x083afa6b in ha_myisam::multi_range_read_init (this=0x99ae000, seq=0xadb39c00, seq_init_param=0x99f40f8, n_ranges=2, mode=4, buf=0x9a537d0)
    at ha_myisam.cc:2038
#11 0x082f45b3 in QUICK_RANGE_SELECT::reset (this=0x99f40f8) at opt_range.cc:8357
#12 0x0826e83e in join_init_read_record (tab=0x9983cb0) at sql_select.cc:14477
#13 0x08281615 in sub_select (join=0x99e1788, join_tab=0x9983cb0, end_of_records=false) at sql_select.cc:13636
#14 0x0828b5b9 in do_select (join=0x99e1788, fields=0x99f5ee4, table=0x0, procedure=0x0) at sql_select.cc:13387
#15 0x0828d761 in JOIN::exec (this=0x99e1788) at sql_select.cc:2811
#16 0x081d27ba in subselect_single_select_engine::exec (this=0x99f6c98) at item_subselect.cc:2277
#17 0x081d0c4a in Item_subselect::exec (this=0x99f6bd0) at item_subselect.cc:280
#18 0x081d42fc in Item_in_subselect::exec (this=0x99f6bd0) at item_subselect.cc:330
#19 0x081d1363 in Item_in_subselect::val_bool (this=0x99f6bd0) at item_subselect.cc:899
#20 0x0817cd6d in Item::val_bool_result (this=0x99f6bd0) at ../item.h:742
#21 0x081a17e1 in Item_in_optimizer::val_int (this=0x99e0388) at item_cmpfunc.cc:1595
#22 0x0816f04b in Item::val_bool (this=0x99e0388) at item.cc:184
#23 0x081a1976 in Item_func_not::val_int (this=0x99f6cc0) at item_cmpfunc.cc:275
#24 0x0816f04b in Item::val_bool (this=0x99f6cc0) at item.cc:184
#25 0x081a0ddf in Item_cond_and::val_int (this=0x99e2e08) at item_cmpfunc.cc:4255
#26 0x08281334 in evaluate_join_record (join=0x99defb8, join_tab=0x99e2b58, error=<value optimized out>) at sql_select.cc:13775
#27 0x08281633 in sub_select (join=0x99defb8, join_tab=0x99e2b58, end_of_records=false) at sql_select.cc:13647
#28 0x0828b5b9 in do_select (join=0x99defb8, fields=0xadc09694, table=0x0, procedure=0x0) at sql_select.cc:13387
#29 0x0828d761 in JOIN::exec (this=0x99defb8) at sql_select.cc:2811
#30 0x0828e1fa in mysql_select (thd=0xadc08220, rref_pointer_array=0xadc09704, tables=0x99f5960, wild_num=0, fields=@0xadc09694, conds=0x99f6ea0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x99f72b8, unit=0xadc09370, select_lex=0xadc09600)
    at sql_select.cc:3001
#31 0x082910dc in handle_select (thd=0xadc08220, lex=0xadc09314, result=0x99f72b8, setup_tables_done_option=0) at sql_select.cc:302
#32 0x082201e2 in execute_sqlcom_select (thd=0xadc08220, all_tables=0x99f5960) at sql_parse.cc:4847
#33 0x08220f61 in mysql_execute_command (thd=0xadc08220) at sql_parse.cc:2015
#34 0x0822a02e in mysql_parse (thd=0xadc08220,
    inBuf=0x99f5458 "SELECT `int_key` FROM D AS OUTR WHERE `pk` NOT IN ( SELECT `int_nokey` FROM D AS INNR WHERE INNR . `int_key` IS NULL OR INNR . `int_key` >= 70 ) AND OUTR . `int_nokey` IS NULL AND NOT OUTR . `pk` = 16"..., length=201, found_semicolon=0xadb3b314) at sql_parse.cc:5811
#35 0x0822a921 in dispatch_command (command=COM_QUERY, thd=0xadc08220, packet=0xadc0a2f9 "", packet_length=201) at sql_parse.cc:1051
#36 0x0822ba49 in do_command (thd=0xadc08220) at sql_parse.cc:724
#37 0x0821bef0 in handle_one_connection (arg=0xadc08220) at sql_connect.cc:1153
#38 0x0057d32f in start_thread () from /lib/libpthread.so.0

At this location:

4302      uint keyno;
4303      Item *pushed_cond= NULL;
4304      handler *new_h2;
4305      DBUG_ENTER("DsMrr_impl::dsmrr_init");
4306      keyno= h->active_index;
4307      DBUG_ASSERT(h2 == NULL); <<<<<< HERE 
4308      if (mode & HA_MRR_USE_DEFAULT_IMPL || mode & HA_MRR_SORTED)
4309      {
4310        use_default_impl= TRUE;
4311        DBUG_RETURN(h->handler::multi_range_read_init(seq_funcs, seq_init_param,

(gdb) print h2
$1 = (handler *) 0x9a153a8

The offending query is:

SELECT `int_key`
FROM D AS OUTR
WHERE `pk` NOT IN (
 SELECT `int_nokey`
 FROM D AS INNR WHERE INNR . `int_key` IS NULL
 OR INNR . `int_key` >= 70
)
AND OUTR . `int_nokey` IS NULL
AND NOT OUTR . `pk` = 164;

How to repeat:
A simplifed test case will follow if this bug is repeatable.
[3 Jul 2008 17:56] Philip Stoev
Test case:

CREATE TABLE t1 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1,9),(2,3),(3,8),(4,6),(5,9),(6,5),(7,5),(8,9),(9,1),(10,10);

SELECT `pk` FROM t1 AS OUTR WHERE `int_key` = ALL (
 SELECT `int_key` FROM t1 AS INNR WHERE INNR . `pk` >= 9
);

The inner query must use a >= to cause the bug. The exact relationship between the inner and the outer query is not important, both "= ALL" and "NOT IN" crash.
[3 Jul 2008 18:05] Philip Stoev
The crash also happens if the same table is not used twice:

SELECT `pk` FROM B AS OUTR
WHERE `pk` NOT IN (
 SELECT `int_nokey` FROM CC AS INNR
 WHERE INNR . `int_key` IS NOT NULL
) OR OUTR . `pk` IS NOT NULL
[11 Jul 2008 21:03] Philip Stoev
Same crash happens for Innodb but a different query:

--source include/have_innodb.inc
DROP TABLE IF EXISTS `C`;
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;

INSERT INTO `C` VALUES (1,5,5,'2003-12-26','2003-12-26','08:05:38','08:05:38','0000-00-00 00:00:00','0000-00-00 00:00:00','',''),
(2,5,5,'0000-00-00','0000-00-00','15:34:08','15:34:08','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
(3,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-09-14 08:36:26','2009-09-14 08:36:26','',''),
(4,6,6,'2006-03-07','2006-03-07','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),
(5,0,0,'2006-07-08','2006-07-08','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
(6,0,0,'2006-11-21','2006-11-21','23:58:52','23:58:52','2005-12-02 14:27:44','2005-12-02 14:27:44','',''),
(7,3,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
(8,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),
(9,5,5,'2002-09-23','2002-09-23','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),
(10,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z');

SELECT COUNT( OUTR . `int_key` ) AS X
FROM C AS OUTR
WHERE OUTR . `varchar_nokey` = ANY (
 SELECT  INNR . `varchar_key` AS Y
 FROM C AS INNR
 WHERE INNR . `int_key` <> 8
)
 AND (
 OUTR . `int_key` IS NOT NULL
 OR OUTR . `int_nokey` IS NULL
)
ORDER BY OUTR . `int_key` , OUTR . `pk`
LIMIT 5;

optimizer_use_mrr  = disable has no effect - the crash still happens.
[28 Jul 2008 22: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/50614

2679 Sergey Petrunia	2008-07-29
      BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
      - Make DsMrr_impl::dsmrr_init() handle the situation when the SQL layer re-starts the MRR scan - 
        without having got eof, it can call h->ha_index_or_rnd_end(); h->ha_index_init(); 
        h->multi_range_read_init().
      - Remove unneded parameters from dsmrr_init() and dsmrr_next().
      - Don't reserve buffer space for key tuple in dsmrr_init() as it is not needed anymore (we used
        key tuple to save/restore MRR scan position but we've switched to cloning handler objects).
[2 Feb 2009 17:37] 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/64906

2690 Sergey Petrunia	2009-02-02
      Fix test results after merge of BUG#37842
[9 Feb 2009 10:31] Bugs System
Pushed into 6.0.10-alpha (revid:sergefp@mysql.com-20090205082222-ph8f7zi9cst1lvnk) (version source revid:sergefp@mysql.com-20090202173654-th9tq8czbfmzzuzb) (merge vers: 6.0.10-alpha) (pib:6)
[10 Feb 2009 14:07] 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/65750

2813 He Zhenxing	2009-02-10 [merge]
      Auto merge 6.0 -> 6.0-rpl
[16 Feb 2009 13:14] Sergey Petrunya
Notes for the changelog:

One could hit assertion failure when trying to execute a query with a subquery such that one of subquery's tables was accessed using DS-MRR.
[20 Feb 2009 20:50] Paul Dubois
Noted in 6.0.10 changelog.
[16 Aug 2010 6:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:05] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Nov 2010 23:07] Paul Dubois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:09] Paul Dubois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.