MySQL Bugs: #58985: Assertion tab->quick->index != 64 failed in make_join_select() in sql

Bug #58985	Assertion tab->quick->index != 64 failed in make_join_select() in sql_select.cc
Submitted:	16 Dec 2010 20:14	Modified:	12 Jan 2011 17:32
Reporter:	John Embretsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.5.9-debug, 5.6.1-debug	OS:	Any
Assigned to:	Jørgen Løland	CPU Architecture:	Any
Tags:	regression

Description:
When executing a complex query including subqueries, aggregates and joins, the following assertion failed: 

tab->quick->index != 64, file sql/sql_select.cc, line 9661
101216 20:12:28 - mysqld got signal 6 ;

Stacktrace:

  [12] _assert(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff2154f5
  [13] make_join_select(join = 0x434eda8, select = 0x4375148, cond = 0x43516d0), line 6507 in "sql_select.cc"
  [14] JOIN::optimize(this = 0x434eda8), line 1182 in "sql_select.cc"
  [15] mysql_select(thd = 0x424b4d0, rref_pointer_array = 0x424d880, tables = 0x4331b20, wild_num = 0, fields = CLASS, conds = 0x433cb40, og_num = 4U, order 
= 0x433cd50, group = (nil), having = (nil), proc_param = (nil), select_options = 2147748616ULL, result = 0x4340078, unit = 0x424d090, select_lex = 0x424d6b0)
, line 2557 in "sql_select.cc"
  [16] handle_select(thd = 0x424b4d0, lex = 0x424cfe0, result = 0x4340078, setup_tables_done_option = 0), line 285 in "sql_select.cc"
  [17] execute_sqlcom_select(thd = 0x424b4d0, all_tables = 0x4331b20), line 4459 in "sql_parse.cc"
  [18] mysql_execute_command(thd = 0x424b4d0), line 2053 in "sql_parse.cc"
  [19] mysql_parse(thd = 0x424b4d0, rawbuf = 0x42de4e0 "SELECT   SQL_SMALL_RESULT  (\nSELECT   MIN(  SUBQUERY1_t1 . `col_varchar_nokey` ) AS SUBQUERY1_field1\nFROM B AS SUBQUERY1_t1\nWHERE SUBQUERY1_t1 . `col_varchar_nokey` <> SUBQUERY1_t1 . `col_varchar_key` ) AS field1 ,\n( ( table1 . `col_int_key` ) - ( table2 . `col_int_key` ) ) AS field2 ,\n( SELECT 'v' FROM DUMMY ) AS field3\nFROM ( C AS table1\nSTRAIGHT_JOIN ( ( CC AS table2\nLEFT OUTER JOIN (\nSELECT   SUBQUERY3_t1 . *\nFROM ( C AS SUBQUERY3_t1\nRIGHT  JOIN CC AS SUBQUERY3_t2\nON (SUBQUERY3_t2 . `col_varchar_nokey` " ..., length = 1213U, parser_state = 0xfffffd7ffea8f900), line 5496 in "sql_parse.cc"
  [20] dispatch_command(command = COM_QUERY, thd = 0x424b4d0, packet = 0x42d6491 "SELECT   SQL_SMALL_RESULT  (\nSELECT   MIN(  SUBQUERY1_t1 . `col_varchar_nokey` ) AS SUBQUERY1_field1\nFROM B AS SUBQUERY1_t1\nWHERE SUBQUERY1_t1 . `col_varchar_nokey` <> SUBQUERY1_t1 . `col_varchar_key` ) AS field1 ,\n( ( table1 . `col_int_key` ) - ( table2 . `col_int_key` ) ) AS field2 ,\n( SELECT 'v' FROM DUMMY ) AS field3\nFROM ( C AS table1\nSTRAIGHT_JOIN ( ( CC AS table2\nLEFT OUTER JOIN (\nSELECT   SUBQUERY3_t1 . *\nFROM ( C AS SUBQUERY3_t1\nRIGHT  JOIN CC AS SUBQUERY3_t2\nON (SUBQUERY3_t2 . `col_varchar_nokey` " ..., packet_length = 1214U), line 1035 in "sql_parse.cc"
  [21] do_command(thd = 0x424b4d0), line 772 in "sql_parse.cc"
  [22] do_handle_one_connection(thd_arg = 0x424b4d0), line 748 in "sql_connect.cc"
  [23] handle_one_connection(arg = 0x424b4d0), line 684 in "sql_connect.cc"
  [24] pfs_spawn_thread(arg = 0x42bd630), line 1015 in "pfs.cc"
  [25] _thrp_setup(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff294ae4
  [26] _lwp_start(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff294da0

How to repeat:
A simplified test case may be provided later. However, a relatively small test case will be attached shortly that will reproduce the issue. 

Only debug builds are affected by this test case.
MySQL 5.0 and 5.1 as well as non-debug builds return the empty set.

Original test case to reproduce Bug#58985.

Attachment: bug58985_v1.test (application/octet-stream, text), 8.97 KiB.

According to bzrfind this is caused by the fix for Bug#58456.

Caused by incorrect sanity check. Quick selects may have index == MAX_KEY if it merges multiple indices.

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/127143

3208 Jorgen Loland	2010-12-17
      BUG#58985: Assertion tab->quick->index != 64 failed in 
                 make_join_select() in sql_select.cc
      
      Caused by incorrect ASSERT introduced by BUG#58456. Quick selects 
      may have index == MAX_KEY if it merges indices.
     @ sql/sql_select.cc
        Removed ASSERT. Quick selects may have index == MAX_KEY if it merges indices.

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/127167

3209 Jorgen Loland	2010-12-17
      BUG#58985: Assertion tab->quick->index != 64 failed in make_join_select() 
                 in sql_select.cc
      
      Follow-up patch. Add sanity check for quick select when it is
      decided that it should be used.
     @ sql/opt_range.h
        Add is_valid() to QUICK_SELECT*
     @ sql/sql_select.cc
        Add sanity check for quick select when it is decided that it should be used.

Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:georgi.kodinov@oracle.com-20101217111134-n5htzj2bnehvnv4w) (merge vers: 5.5.9) (pib:24)

Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:georgi.kodinov@oracle.com-20101217112525-t71wcarh16fii9bz) (merge vers: 5.6.1) (pib:24)

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/127198

3444 Jorgen Loland	2010-12-17 [merge]
      BUG#58985: Merge 5.5-bt -> trunk-bf

Pushed to 5.5-bt, merged to trunk-bf

Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101222212842-y0t3ibtd32wd9qaw) (version source revid:alexander.nozdrin@oracle.com-20101222212842-y0t3ibtd32wd9qaw) (merge vers: 5.6.1) (pib:24)

Pushed into mysql-5.5 5.5.9 (revid:alexander.nozdrin@oracle.com-20101229113652-km2v993aurv7h79j) (version source revid:alexander.nozdrin@oracle.com-20101229113132-uonlbcc2uopff8yb) (merge vers: 5.5.9) (pib:24)

Bug does not appear in any released version. No changelog entry needed.