Bug #37894 Assertion in init_read_record_seq in handler.h line 1444
Submitted: 5 Jul 2008 20:46 Modified: 10 Nov 2008 18:19
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0,5.1,6.0 OS:Any
Assigned to: Gleb Shchepa
Triage: Triaged: D1 (Critical)

[5 Jul 2008 20:46] Philip Stoev
Description:
When executing the SELECT query below (which contains a JOIN and a WHERE inside a subquery), mysqld crashed as follows:

#0  0x00110416 in __kernel_vsyscall ()
#1  0x00581c78 in pthread_kill () from /lib/libpthread.so.0
#2  0x085b0da4 in my_write_core (sig=6) at stacktrace.c:307
#3  0x08213e98 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  0x082794c9 in init_read_record_seq (tab=0x946e7a8) at handler.h:1444
#10 0x08281615 in sub_select (join=0x946c3b0, join_tab=0x946e7a8, end_of_records=false) at sql_select.cc:13636
#11 0x0828b5b9 in do_select (join=0x946c3b0, fields=0x945df7c, table=0x0, procedure=0x0) at sql_select.cc:13387
#12 0x0828d761 in JOIN::exec (this=0x946c3b0) at sql_select.cc:2811
#13 0x081d26c4 in subselect_single_select_engine::exec (this=0x945f4b8) at item_subselect.cc:2277
#14 0x081d0c4a in Item_subselect::exec (this=0x945f3f0) at item_subselect.cc:280
#15 0x081d42fc in Item_in_subselect::exec (this=0x945f3f0) at item_subselect.cc:330
#16 0x081d1363 in Item_in_subselect::val_bool (this=0x945f3f0) at item_subselect.cc:899
#17 0x0817cd6d in Item::val_bool_result (this=0x945f3f0) at ../item.h:742
#18 0x081a1706 in Item_in_optimizer::val_int (this=0x9462ba0) at item_cmpfunc.cc:1566
#19 0x0816f04b in Item::val_bool (this=0x9462ba0) at item.cc:184
#20 0x081a1976 in Item_func_not::val_int (this=0x945f4e0) at item_cmpfunc.cc:275
#21 0x082887e5 in JOIN::optimize (this=0x94616a8) at sql_select.cc:7555
#22 0x0828e1a8 in mysql_select (thd=0x93f9eb0, rref_pointer_array=0x93fb394, tables=0x945da58, wild_num=0, fields=@0x93fb324, conds=0x945f4e0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x945f570, unit=0x93fb000, select_lex=0x93fb290)
    at sql_select.cc:2987
#23 0x082910dc in handle_select (thd=0x93f9eb0, lex=0x93fafa4, result=0x945f570, setup_tables_done_option=0) at sql_select.cc:302
#24 0x082201e2 in execute_sqlcom_select (thd=0x93f9eb0, all_tables=0x945da58) at sql_parse.cc:4847
#25 0x08220f61 in mysql_execute_command (thd=0x93f9eb0) at sql_parse.cc:2015
#26 0x0822a02e in mysql_parse (thd=0x93f9eb0,
    inBuf=0x945d590 "SELECT varchar_nokey\nFROM t1\nWHERE NULL NOT IN (\nSELECT INNR . pk\nFROM t1 AS INNR2\nLEFT JOIN t1 AS INNR\nON ( INNR2 . `int_key` = INNR . `int_key` ) \nWHERE INNR . `varchar_key` > 'n{' \n)", length=185, found_semicolon=0xadddc314) at sql_parse.cc:5811
#27 0x0822a921 in dispatch_command (command=COM_QUERY, thd=0x93f9eb0,
    packet=0x944e9d1 "SELECT varchar_nokey\nFROM t1\nWHERE NULL NOT IN (\nSELECT INNR . pk\nFROM t1 AS INNR2\nLEFT JOIN t1 AS INNR\nON ( INNR2 . `int_key` = INNR . `int_key` ) \nWHERE INNR . `varchar_key` > 'n{' \n)", packet_length=185) at sql_parse.cc:1051
#28 0x0822ba49 in do_command (thd=0x93f9eb0) at sql_parse.cc:724
#29 0x0821bef0 in handle_one_connection (arg=0x93f9eb0) at sql_connect.cc:1153
#30 0x0057d32f in start_thread () from /lib/libpthread.so.0
#31 0x0049a27e in clone () from /lib/libc.so.6

the crash is here:

1439      }
1440      int ha_rnd_init(bool scan)
1441      {
1442        int result;
1443        DBUG_ENTER("ha_rnd_init");
1444        DBUG_ASSERT(inited==NONE || (inited==RND && scan)); <<<< HERE
1445        inited= (result= rnd_init(scan)) ? NONE: RND;
1446        DBUG_RETURN(result);
1447      }
1448      int ha_rnd_end()

however GDB does not allow me to dump the value of "inited".

How to repeat:
Note: some columns from this table may be unnecessary.

CREATE TABLE t1 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `date_key` date DEFAULT NULL,
  `date_nokey` date DEFAULT NULL,
  `time_key` time DEFAULT NULL,
  `time_nokey` time DEFAULT NULL,
  `datetime_key` datetime DEFAULT NULL,
  `datetime_nokey` datetime DEFAULT NULL,
  `varchar_key` varchar(5) DEFAULT NULL,
  `varchar_nokey` varchar(5) DEFAULT 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`)
);

INSERT INTO t1 VALUES (1,NULL,NULL,'2008-04-25','2008-04-25','16:26:06','16:26:06','2009-05-22 11:59:50','2009-05-22 11:59:50','qed','qed'),
(2,9,9,'2008-10-13','2008-10-13','03:24:01','03:24:01','0000-00-00 00:00:00','0000-00-00 00:00:00','gaujz','gaujz'),
(3,8,8,'2001-05-16','2001-05-16','01:20:11','01:20:11',NULL,NULL,'ave','ave'),
(4,6,6,'2000-07-21','2000-07-21',NULL,NULL,'2006-07-26 14:45:43','2006-07-26 14:45:43','gf','gf'),
(5,10,10,'2005-09-14','2005-09-14','23:13:40','23:13:40','2000-01-02 10:29:18','2000-01-02 10:29:18','o','o'),
(6,9,9,'2000-09-21','2000-09-21',NULL,NULL,'2008-09-30 11:13:27','2008-09-30 11:13:27','ux','ux'),
(7,9,9,'2003-08-18','2003-08-18','19:31:37','19:31:37','2001-09-29 00:00:00','2001-09-29 00:00:00','fvz','fvz'),
(8,8,8,'2008-04-10','2008-04-10','22:31:28','22:31:28','2007-03-05 10:43:22','2007-03-05 10:43:22','vxu','vxu'),
(9,7,7,'2005-04-04','2005-04-04','17:42:23','17:42:23',NULL,NULL,NULL,NULL),
(10,8,8,'0000-00-00','0000-00-00','05:05:44','05:05:44','2007-08-02 23:12:17','2007-08-02 23:12:17','p','p');

SELECT varchar_nokey
FROM t1
WHERE NULL NOT IN (
 SELECT INNR . pk
 FROM t1 AS INNR2
 LEFT JOIN t1 AS INNR
 ON ( INNR2 . `int_key` = INNR . `int_key` )
 WHERE INNR . `varchar_key` > 'n{'
);
[9 Jul 2008 16:10] Philip Stoev
Setting to Open so that the bug verification team can check previous releases.
[10 Jul 2008 15:38] Sveta Smirnova
Fails also with versions 5.0.68 with:

Version: '5.0.68-debug-log'  socket: '/users/ssmirnova/src/mysql-5.0/mysql-test/var/tmp/master.sock'  port: 9306  Source distribution
mysqld: handler.h:677: int handler::ha_rnd_init(bool): Assertion `inited==NONE || (inited==RND && scan)' failed.

And 5.1.28 with same error as 6.0
[19 Sep 2008 21:19] 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/54374

2662 Gleb Shchepa	2008-09-20
      Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
      
      Select with a "NULL NOT IN" condition containing complex
      subselect from the same table as in the outer select failed
      with an assertion.
      
      
      The failure was caused by a concatenation of circumstances:
      1) an inner select was optimized by make_join_statistics to use
         the QUICK_RANGE_SELECT access method (that implies an index
         scan of the table);
      2) a subselect was independent (constant) from the outer select;
      3) a condition was pushed down into inner select.
      
      During the evaluation of a constant IN expression an optimizer
      temporary changed the access method from index scan to table
      scan, but an engine handler was already initialized for index
      access by make_join_statistics. That caused an assertion.
      
      
      The subselect_single_select_engine::exec method has been
      modified to substitute a read_record engine handler with
      its clone for further table scan.
[6 Oct 2008 11:27] 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/55438

2662 Gleb Shchepa	2008-10-06
      Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
      
      Select with a "NULL NOT IN" condition containing complex
      subselect from the same table as in the outer select failed
      with an assertion.
      
      
      The failure was caused by a concatenation of circumstances:
      1) an inner select was optimized by make_join_statistics to use
         the QUICK_RANGE_SELECT access method (that implies an index
         scan of the table);
      2) a subselect was independent (constant) from the outer select;
      3) a condition was pushed down into inner select.
      
      During the evaluation of a constant IN expression an optimizer
      temporary changed the access method from index scan to table
      scan, but an engine handler was already initialized for index
      access by make_join_statistics. That caused an assertion.
      
      
      Unnecessary index initialization has been removed from
      the QUICK_RANGE_SELECT::init method (QUICK_RANGE_SELECT::reset
      reinvokes this initialization).
[24 Oct 2008 8:42] Bugs System
Pushed into 5.0.72  (revid:gshchepa@mysql.com-20081010102758-yl9wui7u0cbva97c) (version source revid:gshchepa@mysql.com-20081010102758-yl9wui7u0cbva97c) (pib:5)
[26 Oct 2008 0:30] Paul Dubois
Noted in 5.0.72 changelog.

A SELECT with a NULL NOT IN condition containing a complex subquery
from the same table as in the outer select caused an assertion
failure.

Setting report to NDI pending push into 5.1.x, 6.0.x.
[10 Nov 2008 10:52] Bugs System
Pushed into 6.0.8-alpha  (revid:gshchepa@mysql.com-20081010102758-yl9wui7u0cbva97c) (version source revid:kgeorge@mysql.com-20081010120446-ax2khl3pcsghoeew) (pib:5)
[10 Nov 2008 11:36] Bugs System
Pushed into 5.1.30  (revid:gshchepa@mysql.com-20081010102758-yl9wui7u0cbva97c) (version source revid:kgeorge@mysql.com-20081010130753-obt82wv52av801ed) (pib:5)
[10 Nov 2008 18:19] Paul Dubois
Note in 5.1.31, 6.0.8 changelogs.
[11 Nov 2008 16:32] Paul Dubois
6.0.9 changelog, not 6.0.8.
[19 Jan 2009 11:24] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:02] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:08] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)