Bug #48709 Assertion failed in sql_select.cc:11782: int join_read_key(JOIN_TAB*)
Submitted: 12 Nov 2009 0:20 Modified: 12 Mar 2010 16:53
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: crashing bug

[12 Nov 2009 0:20] Patrick Crews
Description:
Crashing bug in 5.1 and 5.0 - *not* present in 6.0-codebase

Query:
'SELECT `int_key`  
FROM C  
WHERE `int_key`  !=  ALL (  
SELECT SUBQUERY3_t1 .`pk`  
FROM CC SUBQUERY3_t1  JOIN C SUBQUERY3_t2  ON SUBQUERY3_t2 .`int_key`  )  
ORDER  BY `pk`   '

Causes the following crash output (full output attached as separate file):

mysqld: sql_select.cc:11782: int join_read_key(JOIN_TAB*): Assertion `tab->ref.has_record' failed.
091112  3:12:18 - mysqld got signal 6 ;

<snip>

Thread 1 (process 4062):
#0  0xb7fbd430 in __kernel_vsyscall ()
#1  0xb7f9f1c8 in pthread_kill () from /lib/tls/i686/cmov/libpthread.so.0
#2  0x0910782b in my_write_core (sig=6) at stacktrace.c:310
#3  0x0847c068 in handle_segfault (sig=6) at mysqld.cc:2570
#4  <signal handler called>
#5  0xb7fbd430 in __kernel_vsyscall ()
#6  0xb7dd06d0 in raise () from /lib/tls/i686/cmov/libc.so.6
#7  0xb7dd2098 in abort () from /lib/tls/i686/cmov/libc.so.6
#8  0xb7dc95ce in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
#9  0x086104ed in join_read_key (tab=0xb0dff28) at sql_select.cc:11782
#10 0x0860b28c in sub_select (join=0xb0e0758, join_tab=0xb0dff28, end_of_records=false)
    at sql_select.cc:11247
#11 0x0863b876 in do_select (join=0xb0e0758, fields=0xb0d30c4, table=0x0, procedure=0x0)
    at sql_select.cc:11004
#12 0x086699c0 in JOIN::exec (this=0xb0e0758) at sql_select.cc:2255
#13 0x0839a5c2 in subselect_single_select_engine::exec (this=0xb0d3ce8) at item_subselect.cc:1968
#14 0x083a87db in Item_subselect::exec (this=0xb0d3c38) at item_subselect.cc:261
#15 0x08395236 in Item_in_subselect::val_bool (this=0xb0d3c38) at item_subselect.cc:861
#16 0x0825b601 in Item::val_bool_result (this=0xb0d3c38) at item.h:738
#17 0x082e6011 in Item_in_optimizer::val_int (this=0xb0d3fd0) at item_cmpfunc.cc:1703
#18 0x0822d5e1 in Item::val_bool (this=0xb0d3fd0) at item.cc:184
#19 0x082e6507 in Item_func_not::val_int (this=0xb0d3d10) at item_cmpfunc.cc:284
#20 0x0866af84 in SQL_SELECT::skip_record (this=0xb0d4920) at opt_range.h:711
#21 0x0881cb83 in find_all_keys (param=0xb747c66c, select=0xb0d4920, sort_keys=0xb0f3950, 
    buffpek_pointers=0xb747c73c, tempfile=0xb747c800, indexfile=0x0) at filesort.cc:607
#22 0x0881ea0f in filesort (thd=0xb0a1f58, table=0xb0f56a8, sortorder=0xb0d4a28, s_length=1, 
    select=0xb0d4920, max_rows=18446744073709551615, sort_positions=false, examined_rows=0xb747c948)
    at filesort.cc:243
#23 0x0861e2a4 in create_sort_index (thd=0xb0a1f58, join=0xb0de770, order=0xb0d3e28, 
    filesort_limit=18446744073709551615, select_limit=18446744073709551615, is_order_by=false)
    at sql_select.cc:13653
#24 0x086692c8 in JOIN::exec (this=0xb0de770) at sql_select.cc:2198
#25 0x0865ad51 in mysql_select (thd=0xb0a1f58, rref_pointer_array=0xb0a33f4, tables=0xb0d2d98, 
    wild_num=0, fields=@0xb0a3390, conds=0xb0d3d10, og_num=1, order=0xb0d3e28, group=0x0, 
    having=0x0, proc_param=0x0, select_options=2147764736, result=0xb0d3ec8, unit=0xb0a308c, 
    select_lex=0xb0a32fc) at sql_select.cc:2444
#26 0x0866a10c in handle_select (thd=0xb0a1f58, lex=0xb0a3030, result=0xb0d3ec8, 
    setup_tables_done_option=0) at sql_select.cc:269
#27 0x084ae6b5 in execute_sqlcom_select (thd=0xb0a1f58, all_tables=0xb0d2d98) at sql_parse.cc:5051
#28 0x084b47c6 in mysql_execute_command (thd=0xb0a1f58) at sql_parse.cc:2246
#29 0x084d31cc in mysql_parse (thd=0xb0a1f58, 
    inBuf=0xb0d2b10 "SELECT `int_key`  \nFROM C  \nWHERE `int_key`  !=  ALL (  \nSELECT SUBQUERY3_t1 .`pk`  \nFROM CC SUBQUERY3_t1  JOIN C SUBQUERY3_t2  ON SUBQUERY3_t2 .`int_key`  )  \nORDER  BY `pk`", length=174, found_semicolon=0xb747e050) at sql_parse.cc:5970
#30 0x084d5c81 in dispatch_command (command=COM_QUERY, thd=0xb0a1f58, 
    packet=0xb0c6c81 "SELECT `int_key`  \nFROM C  \nWHERE `int_key`  !=  ALL (  \nSELECT SUBQUERY3_t1 .`pk`  \nFROM CC SUBQUERY3_t1  JOIN C SUBQUERY3_t2  ON SUBQUERY3_t2 .`int_key`  )  \nORDER  BY `pk`   ", packet_length=177) at sql_parse.cc:1231
#31 0x084d9c6b in do_command (thd=0xb0a1f58) at sql_parse.cc:872
#32 0x0849b826 in handle_one_connection (arg=0xb0a1f58) at sql_connect.cc:1127
#33 0xb7f9a4ff in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
#34 0xb7e8949e in clone () from /lib/tls/i686/cmov/libc.so.6

How to repeat:
#/* Server0: MySQL 5.1.41-gcov-debug-log */

/*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8);
INSERT INTO `CC` VALUES (11,9);
INSERT INTO `CC` VALUES (12,9);
INSERT INTO `CC` VALUES (13,186);
INSERT INTO `CC` VALUES (14,NULL);
INSERT INTO `CC` VALUES (15,2);
INSERT INTO `CC` VALUES (16,3);
INSERT INTO `CC` VALUES (17,0);
INSERT INTO `CC` VALUES (18,133);
INSERT INTO `CC` VALUES (19,1);
INSERT INTO `CC` VALUES (20,8);
INSERT INTO `CC` VALUES (21,5);
INSERT INTO `CC` VALUES (22,5);
INSERT INTO `CC` VALUES (23,8);
INSERT INTO `CC` VALUES (24,6);
INSERT INTO `CC` VALUES (25,51);
INSERT INTO `CC` VALUES (26,4);
INSERT INTO `CC` VALUES (27,7);
INSERT INTO `CC` VALUES (28,6);
INSERT INTO `CC` VALUES (29,4);
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2);
INSERT INTO `C` VALUES (2,9);
INSERT INTO `C` VALUES (3,3);
INSERT INTO `C` VALUES (4,9);
INSERT INTO `C` VALUES (5,NULL);
INSERT INTO `C` VALUES (6,9);
INSERT INTO `C` VALUES (7,3);
INSERT INTO `C` VALUES (8,8);
INSERT INTO `C` VALUES (9,8);
INSERT INTO `C` VALUES (10,53);
INSERT INTO `C` VALUES (11,0);
INSERT INTO `C` VALUES (12,5);
INSERT INTO `C` VALUES (13,166);
INSERT INTO `C` VALUES (14,3);
INSERT INTO `C` VALUES (15,0);
INSERT INTO `C` VALUES (16,1);
INSERT INTO `C` VALUES (17,9);
INSERT INTO `C` VALUES (18,5);
INSERT INTO `C` VALUES (19,6);
INSERT INTO `C` VALUES (20,2);

 
SELECT `int_key`  
FROM C  
WHERE `int_key`  !=  ALL (  
SELECT SUBQUERY3_t1 .`pk`  
FROM CC SUBQUERY3_t1  JOIN C SUBQUERY3_t2  ON SUBQUERY3_t2 .`int_key`  )  
ORDER  BY `pk`   ;

DROP TABLE CC;
DROP TABLE C;
#/* End of test case for query 0 */
[12 Nov 2009 0:21] Patrick Crews
full crash output (from MTR run of the test case)

Attachment: bug48709_crash_output.txt (text/plain), 12.03 KiB.

[12 Nov 2009 11:40] Konstantin Osipov
This assertion was introduced by my fix for Bug#41756.
It should not affect production, however.
[13 Nov 2009 20:18] Philip Stoev
my gut feeling is that this is also reproducible with 6.0, just not with the default optimizer settings.

If EXPLAIN on the crashing query shows that some optimization is triggered in 6.0, turning this optimization off should cause the assertion to happen in 6.0 as well.
[13 Nov 2009 21:03] Patrick Crews
When testing against 6.0, the following settings were used - my apologies for neglecting to post these in the initial report:

SET GLOBAL OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off';

SET GLOBAL optimizer_use_mrr = 'disable';

SET GLOBAL debug = '+d,optimizer_no_icp';

SET GLOBAL engine_condition_pushdown = 1;

SET GLOBAL join_cache_level = 1;
[3 Dec 2009 15: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/92706

2859 Georgi Kodinov	2009-12-03
      Bug #48709: Assertion failed in sql_select.cc:11782:
       int join_read_key(JOIN_TAB*)
      
      The eq_ref access method TABLE_REF (accessed through 
      JOIN_TAB) to save state and to track if this is the 
      first row it finds or not.
      This state was not reset on subquery re-execution
      causing an assert.
      
      Fixed by resetting the state before the subquery 
      re-execution.
[3 Dec 2009 15:53] 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/92708

2859 Georgi Kodinov	2009-12-03
      Bug #48709: Assertion failed in sql_select.cc:11782:
       int join_read_key(JOIN_TAB*)
      
      The eq_ref access method TABLE_REF (accessed through 
      JOIN_TAB) to save state and to track if this is the 
      first row it finds or not.
      This state was not reset on subquery re-execution
      causing an assert.
      
      Fixed by resetting the state before the subquery 
      re-execution.
[14 Dec 2009 12:01] Tor Didriksen
This bug is indeed present in next-mr and 6.0-codebase also.
Randgen with subquery_semijoin.yy generates queries which expose this bug.
A stripped down example:
SELECT  BIT_OR( OUTR . `col_time_key` ) AS X FROM C AS OUTR2 
LEFT JOIN C AS OUTR 
ON ( OUTR2 . `col_varchar_nokey` <> OUTR . `col_varchar_nokey` ) 
WHERE ( OUTR . `col_int_key` , OUTR . `col_int_key` ) 
IN ( SELECT DISTINCT INNR . `pk` AS X ,
                     INNR . `col_int_key` AS Y FROM CC AS INNR
     WHERE INNR . `col_varchar_key` <= 'z'
)
AND OUTR . `col_int_nokey` >= 7 XOR OUTR . `col_date_nokey` = '2007-10-18'
[14 Dec 2009 12:03] Tor Didriksen
The ASSERT affects only debug builds, but we will get wrong use_count
and this affects row locking/unlocking??
[15 Dec 2009 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/94175

2822 Georgi Kodinov	2009-12-15
      Bug #48709: Assertion failed in sql_select.cc:11782:
       int join_read_key(JOIN_TAB*)
      
      The eq_ref access method TABLE_REF (accessed through 
      JOIN_TAB) to save state and to track if this is the 
      first row it finds or not.
      This state was not reset on subquery re-execution
      causing an assert.
      
      Fixed by resetting the state before the subquery 
      re-execution.
[15 Dec 2009 16:10] 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/94183

3276 Georgi Kodinov	2009-12-15
      Bug #48709: Assertion failed in sql_select.cc:11782:
       int join_read_key(JOIN_TAB*)
      
      The eq_ref access method TABLE_REF (accessed through 
      JOIN_TAB) to save state and to track if this is the 
      first row it finds or not.
      This state was not reset on subquery re-execution
      causing an assert.
      
      Fixed by resetting the state before the subquery 
      re-execution.
[15 Dec 2009 17:10] 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/94217

2859 Georgi Kodinov	2009-12-15
      Bug #48709: Assertion failed in sql_select.cc:11782:
       int join_read_key(JOIN_TAB*)
      
      The eq_ref access method TABLE_REF (accessed through 
      JOIN_TAB) to save state and to track if this is the 
      first row it finds or not.
      This state was not reset on subquery re-execution
      causing an assert.
      
      Fixed by resetting the state before the subquery 
      re-execution.
[15 Dec 2009 17:11] 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/94219

2822 Georgi Kodinov	2009-12-15
      Bug #48709: Assertion failed in sql_select.cc:11782:
       int join_read_key(JOIN_TAB*)
      
      The eq_ref access method TABLE_REF (accessed through 
      JOIN_TAB) to save state and to track if this is the 
      first row it finds or not.
      This state was not reset on subquery re-execution
      causing an assert.
      
      Fixed by resetting the state before the subquery 
      re-execution.
[19 Dec 2009 8:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:33] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091216134707-o96eqw0u2ynvo9gm) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:37] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[14 Jan 2010 8:27] Bugs System
Pushed into 5.0.90 (revid:joro@sun.com-20100114082402-05fod2h6z9x9wok8) (version source revid:joro@sun.com-20091215171006-60wvk91kktlhviit) (merge vers: 5.0.89) (pib:16)
[15 Jan 2010 9:01] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:joro@sun.com-20091216090324-hh91ouoj4gxk12eq) (merge vers: 5.1.42) (pib:16)
[16 Jan 2010 1:51] Paul DuBois
Noted in 5.0.90, 5.1.43, 5.5.1, 6.0.14 changelogs.

Incomplete reset of internal TABLE structures could cause a crash
with eq_ref table access in subqueries.

Setting report to NDI pending push to Celosia.
[12 Mar 2010 14:15] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:31] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:47] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 16:53] Paul DuBois
Fixed in earlier 5.1.x, 5.5.x.