Bug #21077 Server crashes in InnoDB code when performing NOT IN (...) nested query ...
Submitted: 15 Jul 2006 17:08 Modified: 30 Aug 2006 20:35
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:4.1BK OS:Any (any)
Assigned to: Sergey Petrunya

[15 Jul 2006 17:08] Sinisa Milivojevic
Description:
When Innodb prepares for search condition, it did not take into account the way
that SQL layer adds the upper column in the WHERE list in the inner , nested query.

So, when a query has two WHERE clauses, the column for a second one will not be correctly fetched. Instead field with zero length anda "data_error" content will be returned.

How to repeat:
DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (id INT NOT NULL, privil INT NULL) ENGINE=MEMORY;

INSERT INTO t1 (id) VALUES (1),(2),(3),(12),(13),(14),(28),(60);

CREATE TABLE `t2` (
  `interface_id` smallint(6) NOT NULL default '0',
  `node_id` int(11) NOT NULL default '0',
  `parent` int(11) default NULL,
  `ptr_type` tinyint(4) default NULL,
  `info_ptr` int(11) default NULL,
  `strength` int(11) default NULL,
  `leaf_cnt` int(11) default NULL,
  `tot_leaf_cnt` int(11) default NULL,
  UNIQUE KEY `t2$intf_id$node_id` (`interface_id`,`node_id`),
  KEY `t2$intf_id$parent` (`interface_id`,`parent`),
  KEY `t2$intf_id$ptr_type` (`interface_id`,`ptr_type`),
  KEY `t2$info_ptr` (`info_ptr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `t2` VALUES (1,10000,NULL,1,10000,NULL,5,5),(1,100001,10000,2,1,1851,0,NULL),(1,100013,10000,2,13,4103,0,NULL),(1,100015,10000,2,15,3418,0,NULL),(1,100059,10000,2,59,2632,0,NULL),(1,100060,10000,2,60,2044,0,NULL);

SELECT t1.privil FROM t1 WHERE t1.id NOT IN (SELECT info_ptr FROM t2 WHERE interface_id = 1 AND ptr_type = 2);

DROP TABLE t1, t2;

Suggested fix:
Fix code in InnoDB where search conditions are prepared. Most likely in function 
row_search_for_mysql.
[22 Jul 2006 12:09] Shane Bester
simpler testcase, crashed 4.1

Attachment: testcase.sql (text/x-delimtext), 626 bytes.

[27 Jul 2006 22:02] Heikki Tuuri
Hi!

This is strangely nondeterministic. After an upgrade from 4.1.18:

(gdb) run
Starting program: /home/heikki/mysql-4.1.20/sql/mysqld
[Thread debugging using libthread_db enabled]
[New Thread 1076527872 (LWP 4075)]
060728  0:11:40 [Warning] Changed limits: max_open_files: 1024  max_connections: 1014  table_cache: 64
[New Thread 1096506288 (LWP 4078)]
[New Thread 1104894896 (LWP 4079)]
[New Thread 1113283504 (LWP 4080)]
[New Thread 1121672112 (LWP 4081)]
[New Thread 1134840752 (LWP 4082)]
[New Thread 1143229360 (LWP 4083)]
[New Thread 1151617968 (LWP 4084)]
060728  0:11:40  InnoDB: Started; log sequence number 0 63995
[New Thread 1160207280 (LWP 4085)]
060728  0:11:40 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
/home/heikki/mysql-4.1.20/sql/mysqld: ready for connections.
Version: '4.1.20-log'  socket: '/home/heikki/bugsocket'  port: 3307  Source distribution
[New Thread 1160407984 (LWP 4099)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1160407984 (LWP 4099)]
cmp_dtuple_rec_with_match (dtuple=0x40327068,
    rec=0x404c8135 "\200\001\200\001\206¯", matched_fields=0x452a5770,
    matched_bytes=0x452a5774) at rem0cmp.c:580
580                                     dtuple_byte = *dtuple_b_ptr;
Current language:  auto; currently c
(gdb) bt
#0  cmp_dtuple_rec_with_match (dtuple=0x40327068,
    rec=0x404c8135 "\200\001\200\001\206¯", matched_fields=0x452a5770,
    matched_bytes=0x452a5774) at rem0cmp.c:580
#1  0x0830ba84 in cmp_dtuple_rec (dtuple=0x404c8137,
    rec=0x404c8137 "\200\001\206¯") at rem0cmp.c:652
#2  0x08246d54 in row_search_for_mysql (buf=0x896de58 "ó\001",
    mode=1078755637, prebuilt=0x40328268, match_mode=1, direction=256)
    at row0sel.c:3402
#3  0x081bdf6f in ha_innobase::general_fetch (this=0x896dd50,
    buf=0x404c8137 "\200\001\206¯", direction=1078755639,
    match_mode=1078755639) at ha_innodb.cc:3365
#4  0x081b4d87 in handler::read_range_next (this=0x896dd50) at handler.cc:1651
#5  0x081ad98b in QUICK_SELECT::get_next (this=0x8926ae8) at opt_range.cc:2773
#6  0x081affcd in rr_quick (info=0x896efb4) at records.cc:219
#7  0x0817620d in join_init_read_record (tab=0x896ef90) at sql_select.cc:6484
#8  0x081750fc in sub_select (join=0x894b770, join_tab=0x896ef90,
    end_of_records=5) at sql_select.cc:6043
#9  0x08174cae in do_select (join=0x894b770, fields=0x896ef90, table=0x0,
    procedure=0x404c8137) at sql_select.cc:5953
#10 0x0816bfbf in JOIN::exec (this=0x894b770) at sql_select.cc:1503
#11 0x0810e836 in subselect_single_select_engine::exec (this=0x894ac80)
    at item_subselect.cc:1443
#12 0x0810b2d6 in Item_subselect::exec (this=0x894abf0)
    at item_subselect.cc:204
#13 0x0810c1d7 in Item_in_subselect::val_int (this=0x894abf0)
    at item_subselect.cc:628
#14 0x080d0e21 in Item::val_int_result (this=0x404c8137) at item.h:220
#15 0x080e7f8e in Item_in_optimizer::val_int (this=0x894c168)
    at item_cmpfunc.cc:666
#16 0x080e23f1 in Item_int_func::val (this=0x404c8137) at item_func.h:221
#17 0x080e69c9 in Item_func_not::val_int (this=0x894ab90)
    at item_cmpfunc.cc:123
#18 0x08175189 in sub_select (join=0x894ad78, join_tab=0x896e7f8,
    end_of_records=5) at sql_select.cc:6065
#19 0x08174cae in do_select (join=0x894ad78, fields=0x896e7f8, table=0x0,
    procedure=0x404c8137) at sql_select.cc:5953
#20 0x0816bfbf in JOIN::exec (this=0x894ad78) at sql_select.cc:1503
#21 0x0816c942 in mysql_select (thd=0x8922bc0, rref_pointer_array=0x8922dd4,
    tables=0x894a480, wild_num=0, fields=@0x404c8137, conds=0x894ab90,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=143797184, result=0x894ad68, unit=0x8922c08,
    select_lex=0x8922ce8) at sql_select.cc:1621
#22 0x081691df in handle_select (thd=0x8922bc0, lex=0x8922bfc,
    result=0x894ad68) at sql_select.cc:177
---Type <return> to continue, or q <return> to quit---
#23 0x081424b0 in mysql_execute_command (thd=0x8922bc0) at sql_parse.cc:2116
#24 0x08147edc in mysql_parse (thd=0x8922bc0,
    inBuf=0x894a348 "SELECT t1.privil FROM t1 WHERE t1.id NOT IN (SELECT info_ptr FROM t2 WHERE\ninterface_id = 1 AND ptr_type = 2)", length=143797244)
    at sql_parse.cc:4351
#25 0x08141047 in dispatch_command (command=COM_QUERY, thd=0x8922bc0,
    packet=0x8942319 "", packet_length=3) at sql_parse.cc:1519
#26 0x08140ce6 in do_command (thd=0x8922bc0) at sql_parse.cc:1322
#27 0x08140448 in handle_one_connection (arg=0x8922bc0) at sql_parse.cc:1054
#28 0x40041b63 in start_thread () from /lib/tls/libpthread.so.0
#29 0x4024b18a in clone () from /lib/tls/libc.so.6
(gdb) list
1054          if (do_command(thd))
1055            break;
1056        }
1057        if (thd->user_connect)
1058          decrease_user_connections(thd->user_connect);
1059        free_root(thd->mem_root,MYF(0));
1060        if (net->error && net->vio != 0 && net->report_error)
1061        {
1062          if (!thd->killed && thd->variables.log_warnings > 1)
1063            sql_print_warning(ER(ER_NEW_ABORTING_CONNECTION),

(gdb) frame 2
#2  0x08246d54 in row_search_for_mysql (buf=0x896de58 "ó\001",
    mode=1078755637, prebuilt=0x40328268, match_mode=1, direction=256)
    at row0sel.c:3402
3402                    if (0 != cmp_dtuple_rec(search_tuple, rec)) {
(gdb) print *search_tuple
$1 = {info_bits = 0, n_fields = 10, n_fields_cmp = 10, fields = 0x40327084,
  tuple_list = {prev = 0xa282060, next = 0x69602020}, magic_n = 1919251566}
(gdb) list
3397                    /* Test if the index record matches completely to search_tuple
3398                    in prebuilt: if not, then we return with DB_RECORD_NOT_FOUND */
3399
3400                    /* fputs("Comparing rec and search tuple\n", stderr); */
3401
3402                    if (0 != cmp_dtuple_rec(search_tuple, rec)) {
3403
3404                            if (prebuilt->select_lock_type != LOCK_NONE
3405                                && set_also_gap_locks) {
3406

search_tuple looks nonsensical. Maybe MySQL has passed a nonsensical key value to InnoDB.

But I cannot repeat the crash any more!

InnoDB does not do any WHERE condition processing internally. MySQL simply tells InnoDB to search using a certain key value and fetch certain columns.

Since the bug is nondeterministic, this is probably an unitialized variable or memory corruption.

Sinisa, Shane, can you still repeat the crash? If you run inside gdb, where does it crash and why? Does MySQL pass a sensible search key value to InnoDB?

Regards,

Heikki
[28 Jul 2006 9:26] Heikki Tuuri
Hi!

Actually, the search tuple in the stack trace looks like it was never initialized.

The crash could happen if MySQL calls ::index_next_same() without first calling ::index_read() with a key value.

In the stack trace, the function below was called and 'this'->last_match_mode was ROW_SEL_EXACT (equivalent to HA_READ_KEY_EXACT).

Regards,

Heikki

/***********************************************************************
Reads the next row matching to the key value given as the parameter. */

int
ha_innobase::index_next_same(
/*=========================*/
                                /* out: 0, HA_ERR_END_OF_FILE, or error
                                number */
        mysql_byte*     buf,    /* in/out: buffer for the row */
        const mysql_byte* key,  /* in: key value */
        uint            keylen) /* in: key value length */
{
        statistic_increment(current_thd->status_var.ha_read_next_count,
                            &LOCK_status);

        return(general_fetch(buf, ROW_SEL_NEXT, last_match_mode));
}
[28 Jul 2006 14:30] Heikki Tuuri
Hi!

I am now able to repeat a bug.

This query:

mysql> SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
+------+
| id1  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

uses a nonsensical 6-field search tuple at one point:

Converting MySQL key val of len 4 to InnoDB, index id2
Resulting InnoDB search tuple has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

Converting MySQL key val of len 4 to InnoDB, index id2
Resulting InnoDB search tuple has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

Using in fetch InnoDB search tuple that has 6 fields

Converting MySQL key val of len 4 to InnoDB, index id2
Resulting InnoDB search tuple has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

Using in fetch InnoDB search tuple that has 1 fields

--Heikki
[28 Jul 2006 15:54] Heikki Tuuri
This bug is probably in the MySQL query optimizer/executor.

MySQL calls that strange ::index_init() that resets the InnoDB search_tuple to length 6. And AFTER that, MySQL again calls ::index_next_same() on the table handle though the handle has been reset in the call ::index_init()!

I studied what happens just before that strange ::index_init().

#0  row_sel_store_mysql_rec (mysql_rec=0x8926988 "ù\001", prebuilt=0x40328c68,
    rec=0x404c40b2 "\200") at row0sel.c:2250
#1  0x08246a00 in row_search_for_mysql (buf=0x8926988 "ù\001",
    mode=1078739122, prebuilt=0x40328c68, match_mode=1, direction=1078739122)
    at row0sel.c:3635
#2  0x081be007 in ha_innobase::general_fetch (this=0x8926880,
    buf=0x40328c68 "ïo®\004h|2@hÄ1@", direction=1077054568, match_mode=0)
    at ha_innodb.cc:3393
#3  0x081b4d87 in handler::read_range_next (this=0x8926880) at handler.cc:1651
#4  0x081ad98b in QUICK_SELECT::get_next (this=0x89506a8) at opt_range.cc:2773
#5  0x081affcd in rr_quick (info=0x894e52c) at records.cc:219
#6  0x081751d6 in sub_select (join=0x894b7a0, join_tab=0x894e508,
    end_of_records=104) at sql_select.cc:6088
#7  0x08174cae in do_select (join=0x894b7a0, fields=0x894e508, table=0x0,
    procedure=0x40328c68) at sql_select.cc:5953
#8  0x0816bfbf in JOIN::exec (this=0x894b7a0) at sql_select.cc:1503
#9  0x0810e836 in subselect_single_select_engine::exec (this=0x894acb0)
    at item_subselect.cc:1443
#10 0x0810b2d6 in Item_subselect::exec (this=0x894ac20)
    at item_subselect.cc:204
#11 0x0810c1d7 in Item_in_subselect::val_int (this=0x894ac20)
    at item_subselect.cc:628
#12 0x080d0e21 in Item::val_int_result (this=0x40328c68) at item.h:220
#13 0x080e7f8e in Item_in_optimizer::val_int (this=0x894c198)
    at item_cmpfunc.cc:666
#14 0x080e23f1 in Item_int_func::val (this=0x40328c68) at item_func.h:221
#15 0x080e69c9 in Item_func_not::val_int (this=0x894abc0)
    at item_cmpfunc.cc:123
#16 0x08175189 in sub_select (join=0x894ada8, join_tab=0x894dd70,
    end_of_records=104) at sql_select.cc:6065
#17 0x08174cae in do_select (join=0x894ada8, fields=0x894dd70, table=0x0,
    procedure=0x40328c68) at sql_select.cc:5953
#18 0x0816bfbf in JOIN::exec (this=0x894ada8) at sql_select.cc:1503
#19 0x0816c942 in mysql_select (thd=0x8922bc0, rref_pointer_array=0x8922dd4,
    tables=0x894a488, wild_num=0, fields=@0x40328c68, conds=0x894abc0,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=143797184, result=0x894ad98, unit=0x8922c08,
    select_lex=0x8922ce8) at sql_select.cc:1621
#20 0x081691df in handle_select (thd=0x8922bc0, lex=0x8922bfc,
    result=0x894ad98) at sql_select.cc:177
#21 0x081424b0 in mysql_execute_command (thd=0x8922bc0) at sql_parse.cc:2116
#22 0x08147edc in mysql_parse (thd=0x8922bc0,
    inBuf=0x894a348 "SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2)", length=143797244) at sql_parse.cc:4351

#### INNODB CORRECTLY RETURNS tuple (5, 1, 2, ...)

Breakpoint 6, row_search_for_mysql (buf=0x8926988 "ù\005", mode=0,
    prebuilt=0x40328c68, match_mode=1, direction=1078739163) at row0sel.c:3664
3664            ret = DB_SUCCESS;
(gdb) x/20b buf
0x8926988:      0xf9    0x05    0x00    0x00    0x00    0x01    0x00    0x00
0x8926990:      0x00    0x02    0x00    0x00    0x00    0x00    0x00    0x00
0x8926998:      0x00    0x00    0x20    0x20
(gdb) c
Continuing.
[28 Jul 2006 15:54] Heikki Tuuri
###### MYSQL LETS THE CURSOR ON TABLE t1 TO STEP FORWARD:

Breakpoint 5, row_sel_store_mysql_rec (mysql_rec=0x8926db8 "ý\005",
    prebuilt=0x40326a68, rec=0x404b80c9 "") at row0sel.c:2250
2250            if (prebuilt->blob_heap != NULL) {
(gdb) bt
#0  row_sel_store_mysql_rec (mysql_rec=0x8926db8 "ý\005", prebuilt=0x40326a68,
    rec=0x404b80c9 "") at row0sel.c:2250
#1  0x08246a00 in row_search_for_mysql (buf=0x8926db8 "ý\005",
    mode=1078689993, prebuilt=0x40326a68, match_mode=0, direction=1078689993)
    at row0sel.c:3635
#2  0x081be007 in ha_innobase::general_fetch (this=0x8926cb0,
    buf=0x40326a68 "ïo®\004h^2@hÄ1@", direction=1077045864, match_mode=0)
    at ha_innodb.cc:3393
#3  0x081be312 in ha_innobase::rnd_next (this=0x8926cb0,
    buf=0x40326a68 "ïo®\004h^2@hÄ1@") at ha_innodb.cc:3584
#4  0x081b0185 in rr_sequential (info=0x894dd94) at records.cc:290
#5  0x081751d6 in sub_select (join=0x894ada8, join_tab=0x894dd70,
    end_of_records=104) at sql_select.cc:6088
#6  0x08174cae in do_select (join=0x894ada8, fields=0x894dd70, table=0x0,
    procedure=0x40326a68) at sql_select.cc:5953
#7  0x0816bfbf in JOIN::exec (this=0x894ada8) at sql_select.cc:1503
#8  0x0816c942 in mysql_select (thd=0x8922bc0, rref_pointer_array=0x8922dd4,
    tables=0x894a488, wild_num=0, fields=@0x40326a68, conds=0x894abc0,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=143797184, result=0x894ad98, unit=0x8922c08,
    select_lex=0x8922ce8) at sql_select.cc:1621
#9  0x081691df in handle_select (thd=0x8922bc0, lex=0x8922bfc,
    result=0x894ad98) at sql_select.cc:177
#10 0x081424b0 in mysql_execute_command (thd=0x8922bc0) at sql_parse.cc:2116
#11 0x08147edc in mysql_parse (thd=0x8922bc0,
    inBuf=0x894a348 "SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2)", length=143797244) at sql_parse.cc:4351
#12 0x08141047 in dispatch_command (command=COM_QUERY, thd=0x8922bc0,
    packet=0x8942319 "", packet_length=3) at sql_parse.cc:1519
#13 0x08140ce6 in do_command (thd=0x8922bc0) at sql_parse.cc:1322
#14 0x08140448 in handle_one_connection (arg=0x8922bc0) at sql_parse.cc:1054
#15 0x40041b63 in start_thread () from /lib/tls/libpthread.so.0
#16 0x4024b18a in clone () from /lib/tls/libc.so.6
(gdb) c

######## MYSQL CALLS index_init() ON THE CURSOR ON t2:

(gdb)
Continuing.
Using table handle 8926880, calling ::index_init()
Calling ::change_active_index on table handle 8926880

Breakpoint 4, build_template (prebuilt=0x40328c68, thd=0x8922bc0,
    table=0x8926288, templ_type=1076485824) at ha_innodb.cc:2270
2270            if (prebuilt->select_lock_type == LOCK_X) {
(gdb)
Continuing.

###### MYSQL CALLS index_next_same() ON THE CURSOR ON t2! THIS MAKES NO SENSE SINCE THE CURSOR WAS RESET.

Breakpoint 2, ha_innobase::index_next_same (this=0x8926880,
    buf=0x8926988 "ù\005", key=0x8950750 "\001", keylen=4) at ha_innodb.cc:3448
3448            statistic_increment(ha_read_next_count, &LOCK_status);
(gdb) bt
#0  ha_innobase::index_next_same (this=0x8926880, buf=0x8926988 "ù\005",
    key=0x8950750 "\001", keylen=4) at ha_innodb.cc:3448
#1  0x081b4d87 in handler::read_range_next (this=0x8926880) at handler.cc:1651
#2  0x081ad98b in QUICK_SELECT::get_next (this=0x89506a8) at opt_range.cc:2773
#3  0x081affcd in rr_quick (info=0x894e52c) at records.cc:219
#4  0x0817620d in join_init_read_record (tab=0x894e508) at sql_select.cc:6484
#5  0x081750fc in sub_select (join=0x894b7a0, join_tab=0x894e508,
    end_of_records=136) at sql_select.cc:6043
#6  0x08174cae in do_select (join=0x894b7a0, fields=0x894e508, table=0x0,
    procedure=0x8926288) at sql_select.cc:5953
#7  0x0816bfbf in JOIN::exec (this=0x894b7a0) at sql_select.cc:1503
#8  0x0810e836 in subselect_single_select_engine::exec (this=0x894acb0)
    at item_subselect.cc:1443
#9  0x0810b2d6 in Item_subselect::exec (this=0x894ac20)
    at item_subselect.cc:204
#10 0x0810c1d7 in Item_in_subselect::val_int (this=0x894ac20)
    at item_subselect.cc:628
#11 0x080d0e21 in Item::val_int_result (this=0x8926288) at item.h:220
#12 0x080e7f8e in Item_in_optimizer::val_int (this=0x894c198)
    at item_cmpfunc.cc:666
#13 0x080e23f1 in Item_int_func::val (this=0x8926288) at item_func.h:221
#14 0x080e69c9 in Item_func_not::val_int (this=0x894abc0)
    at item_cmpfunc.cc:123
#15 0x08175189 in sub_select (join=0x894ada8, join_tab=0x894dd70,
    end_of_records=136) at sql_select.cc:6065
#16 0x08174cae in do_select (join=0x894ada8, fields=0x894dd70, table=0x0,
    procedure=0x8926288) at sql_select.cc:5953
#17 0x0816bfbf in JOIN::exec (this=0x894ada8) at sql_select.cc:1503
#18 0x0816c942 in mysql_select (thd=0x8922bc0, rref_pointer_array=0x8922dd4,
    tables=0x894a488, wild_num=0, fields=@0x8926288, conds=0x894abc0,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=143797184, result=0x894ad98, unit=0x8922c08,
    select_lex=0x8922ce8) at sql_select.cc:1621
#19 0x081691df in handle_select (thd=0x8922bc0, lex=0x8922bfc,
    result=0x894ad98) at sql_select.cc:177
#20 0x081424b0 in mysql_execute_command (thd=0x8922bc0) at sql_parse.cc:2116
#21 0x08147edc in mysql_parse (thd=0x8922bc0,
    inBuf=0x894a348 "SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2)", length=143797244) at sql_parse.cc:4351
#22 0x08141047 in dispatch_command (command=COM_QUERY, thd=0x8922bc0,
    packet=0x8942319 "", packet_length=3) at sql_parse.cc:1519
#23 0x08140ce6 in do_command (thd=0x8922bc0) at sql_parse.cc:1322
#24 0x08140448 in handle_one_connection (arg=0x8922bc0) at sql_parse.cc:1054
#25 0x40041b63 in start_thread () from /lib/tls/libpthread.so.0
---Type <return> to continue, or q <return> to quit---
#26 0x4024b18a in clone () from /lib/tls/libc.so.6
[28 Jul 2006 15:57] Heikki Tuuri
This is the test I used on 4.1.20:

DROP TABLE IF EXISTS `t1`;
DROP TABLE IF EXISTS `t2`;

CREATE TABLE `t1` (`id1` INT) ;
INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);

CREATE TABLE `t2` (
  `id1` INT,
  `id2` INT NOT NULL,
  `id3` INT,
  `id4` INT NOT NULL,
  UNIQUE (`id2`,`id4`),
  KEY (`id1`)
) ENGINE=InnoDB;

INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES 
(1,1,1,0),
(1,1,2,1),
(5,1,2,2),
(6,1,2,3),
(1,2,2,2),
(1,2,1,1);

SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);

Looks like the bug happens after MySQL has determined that (5) in t1 is not in the result set of the SELECT.
[28 Jul 2006 21:02] Heikki Tuuri
This bug may be a duplicate of http://bugs.mysql.com/bug.php?id=8804
[14 Aug 2006 16:41] 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/10378

ChangeSet@1.2536, 2006-08-14 20:41:53+04:00, sergefp@mysql.com +3 -0
  BUG#21077: Possible crash caused by invalid sequence of handler::* calls: 
  The crash was caused by invalid sequence of handler::** calls: 
    ha_smth->index_init();
    ha_smth->index_next_same(); (2)
  (2) is an invalid call as it was not preceeded by any 'scan setup' call
  like index_first() or index_read(). The cause was that QUICK_SELECT::reset()
  didn't "fully reset" the quick select- current QUICK_RANGE wasn't forgotten,
  and quick select might attempt to continue reading the range, which would 
  result in the above mentioned invalid sequence of handler calls.
[15 Aug 2006 13:10] Sergey Petrunya
The bug doesn't occur in 5.x versions.

In 5.x, QUICK_RANGE_SELECT::reset() already has the "range= NULL" statement that was missing in 4.1.
[15 Aug 2006 13:16] 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/10476

ChangeSet@1.2536, 2006-08-15 17:16:44+04:00, sergefp@mysql.com +3 -0
  BUG#21077: Possible crash caused by invalid sequence of handler::* calls: 
  The crash was caused by invalid sequence of handler::** calls: 
    ha_smth->index_init();
    ha_smth->index_next_same(); (2)
  (2) is an invalid call as it was not preceeded by any 'scan setup' call
  like index_first() or index_read(). The cause was that QUICK_SELECT::reset()
  didn't "fully reset" the quick select- current QUICK_RANGE wasn't forgotten,
  and quick select might attempt to continue reading the range, which would 
  result in the above mentioned invalid sequence of handler calls.
  
  5.x versions are not affected by the bug - they already have the missing
  "range=NULL" clause.
[15 Aug 2006 16:33] 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/10489

ChangeSet@1.2532, 2006-08-15 20:33:14+04:00, sergefp@mysql.com +3 -0
  BUG#21077: Possible crash caused by invalid sequence of handler::* calls:                                                
  The crash was caused by invalid sequence of handler::** calls:                                                           
    ha_smth->index_init();                                                                                                 
    ha_smth->index_next_same(); (2)                                                                                        
  (2) is an invalid call as it was not preceeded by any 'scan setup' call
  like index_first() or index_read(). The cause was that QUICK_SELECT::reset()
  didn't "fully reset" the quick select- current QUICK_RANGE wasn't forgotten,
  and quick select might attempt to continue reading the range, which would
  result in the above mentioned invalid sequence of handler calls.
  
  5.x versions are not affected by the bug - they already have the missing
  "range=NULL" clause.
[15 Aug 2006 17:15] 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/10496

ChangeSet@1.2257, 2006-08-15 21:16:02+04:00, sergefp@mysql.com +3 -0
  BUG#21077: 4.1->5.0 merge
  MERGE: 1.1616.2696.2
[15 Aug 2006 17:30] Sergey Petrunya
Heikki, thanks for the detailed analysis, I could fix it very quickly.
[29 Aug 2006 13:20] Evgeny Potemkin
Fixed in 4.1.22, 5.0.25
[30 Aug 2006 20:35] Paul Dubois
Noted in 4.1.22, 5.0.25 changelogs.
[4 Sep 2006 11:44] Evgeny Potemkin
Fixed in 5.1.12