Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' on REPLACE ... SELECT with MRR

[Philip Stoev]

When executing a REPLACE ... SELECT query, maria 5.3 asserted as follows:

mysqld: field.cc:7099: virtual String* Field_varstring::val_str(String*, String*): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.

# 2010-08-28T17:12:54 #8 0x0038ed98 in __assert_fail () from /lib/libc.so.6
# 2010-08-28T17:12:54 #9 0x082567ec in Field_varstring::val_str (this=0xb5d83ce0, val_buffer=0xb5db7b8c, val_ptr=0xb5d14f94) at field.cc:7099
# 2010-08-28T17:12:54 #10 0x081bd437 in Item_field::val_str (this=0xb5d14f88, str=0xb5db7b8c) at item.cc:2103
# 2010-08-28T17:12:54 #11 0x08203051 in Item_func_like::val_int (this=0xb5db7ac0) at item_cmpfunc.cc:4691
# 2010-08-28T17:12:54 #12 0x084adc9f in index_cond_func_myisam (arg=0xb5d806d0) at ha_myisam.cc:1687
# 2010-08-28T17:12:54 #13 0x084bf78b in mi_check_index_cond (info=0xb5b49678, keynr=2, record=0xb5d3a2b0 <incomplete sequence \373>) at mi_key.c:521
# 2010-08-28T17:12:54 #14 0x084b7998 in mi_rkey (info=0xb5b49678, buf=0xb5d3a2b0 <incomplete sequence \373>, inx=2, key=0xb5de6ae0 "", keypart_map=1,
# 2010-08-28T17:12:54 search_flag=HA_READ_KEY_OR_NEXT) at mi_rkey.c:121
# 2010-08-28T17:12:54 #15 0x084addb8 in ha_myisam::index_read_map (this=0xb5d806d0, buf=0xb5d3a2b0 <incomplete sequence \373>, key=0xb5de6ae0 "", keypart_map=1,
# 2010-08-28T17:12:54 find_flag=HA_READ_KEY_OR_NEXT) at ha_myisam.cc:1722
# 2010-08-28T17:12:54 #16 0x081aa6e3 in handler::ha_index_read_map (this=0xb5d806d0, buf=0xb5d3a2b0 <incomplete sequence \373>, key=0xb5de6ae0 "", keypart_map=1,
# 2010-08-28T17:12:54 find_flag=HA_READ_KEY_OR_NEXT) at sql_class.h:3391
# 2010-08-28T17:12:54 #17 0x083cb279 in handler::read_range_first (this=0xb5d806d0, start_key=0xb5d80774, end_key=0xb5d80784, eq_range_arg=false, sorted=true) at handler.cc:4279
# 2010-08-28T17:12:54 #18 0x0849236a in handler::multi_range_read_next (this=0xb5d806d0, range_info=0xb60d1da8) at multi_range_read.cc:267
# 2010-08-28T17:12:54 #19 0x08492daf in DsMrr_impl::dsmrr_next (this=0xb5d80a44, range_info=0xb60d1da8) at multi_range_read.cc:538
# 2010-08-28T17:12:54 #20 0x084aedfc in ha_myisam::multi_range_read_next (this=0xb5d806d0, range_info=0xb60d1da8) at ha_myisam.cc:2190
# 2010-08-28T17:12:54 #21 0x083aa77b in QUICK_RANGE_SELECT::get_next (this=0xb5de18d8) at opt_range.cc:8402
# 2010-08-28T17:12:54 #22 0x083c0a99 in find_all_keys (param=0xb60d2068, select=0xb5de1230, sort_keys=0xb5de7100, buffpek_pointers=0xb60d1ea0, tempfile=0xb60d1f84, indexfile=0x0)
# 2010-08-28T17:12:54 at filesort.cc:568
# 2010-08-28T17:12:54 #23 0x083bfb26 in filesort (thd=0x963f408, table=0xb5d834f8, sortorder=0xb5de1370, s_length=2, select=0xb5de1230, max_rows=7, sort_positions=false,
# 2010-08-28T17:12:54 examined_rows=0xb60d21d0) at filesort.cc:246
# 2010-08-28T17:12:54 #24 0x083244bd in create_sort_index (thd=0x963f408, join=0xb5de1c50, order=0xb5db7d48, filesort_limit=7, select_limit=7, is_order_by=false)
# 2010-08-28T17:12:54 at sql_select.cc:15833
# 2010-08-28T17:12:54 #25 0x083027d7 in JOIN::exec (this=0xb5de1c50) at sql_select.cc:2298
# 2010-08-28T17:12:54 #26 0x08303209 in mysql_select (thd=0x963f408, rref_pointer_array=0x9640ea0, tables=0xb5d3eca0, wild_num=0, fields=..., conds=0xb5db7ac0, og_num=2,
# 2010-08-28T17:12:54 order=0xb5db7d48, group=0x0, having=0x0, proc_param=0x0, select_options=3489942016, result=0xb5de0110, unit=0x9640ae4, select_lex=0x9640d84)
# 2010-08-28T17:12:54 at sql_select.cc:2556
# 2010-08-28T17:12:54 #27 0x082fb7fb in handle_select (thd=0x963f408, lex=0x9640a88, result=0xb5de0110, setup_tables_done_option=1073741824) at sql_select.cc:276
# 2010-08-28T17:12:54 #28 0x08293cd7 in mysql_execute_command (thd=0x963f408) at sql_parse.cc:3264
# 2010-08-28T17:12:54 #29 0x0829c405 in mysql_parse (thd=0x963f408,
# 2010-08-28T17:12:54 inBuf=0xb5de0960 "REPLACE INTO `table10_myisam_key_pk_parts_2_varchar_255_not_null` ( `col_varchar_255_latin1_not_null` ) SELECT `col_varchar_255_utf8_not_null_key` FROM `table10_myisam_varchar_255_not_null` WHERE `col"..., length=337, found_semicolon=0xb60d3230) at sql_parse.cc:6027
# 2010-08-28T17:12:54 #30 0x0828e6d2 in dispatch_command (command=COM_QUERY, thd=0x963f408,
# 2010-08-28T17:12:54 packet=0x96492c9 "REPLACE INTO `table10_myisam_key_pk_parts_2_varchar_255_not_null` ( `col_varchar_255_latin1_not_null` ) SELECT `col_varchar_255_utf8_not_null_key` FROM `table10_myisam_varchar_255_not_null` WHERE `col"..., packet_length=337) at sql_parse.cc:1184
# 2010-08-28T17:12:54 #31 0x0828dbc0 in do_command (thd=0x963f408) at sql_parse.cc:890
# 2010-08-28T17:12:54 #32 0x0828ad58 in handle_one_connection (arg=0x963f408) at sql_connect.cc:1153
# 2010-08-28T17:12:54 #33 0x00116919 in start_thread () from /lib/libpthread.so.0
# 2010-08-28T17:12:54 #34 0x00448cbe in clone () from /lib/libc.so.6

[Philip Stoev (pstoev-askmonty)]

Test case:

--disable_abort_on_error

CREATE TABLE `table10_myisam_varchar_255_not_null` ( `col_varchar_255_latin1` varchar(255) CHARACTER SET latin1, `col_varchar_255_utf8_not_null_key` varchar(255) CHARACTER SET utf8 not null, `col_varchar_255_utf8_not_null` varchar(255) CHARACTER SET utf8 not null, `col_varchar_255_utf8` varchar(255) CHARACTER SET utf8, `col_varchar_255_utf8_key` varchar(255) CHARACTER SET utf8, `col_varchar_255_latin1_not_null_key` varchar(255) CHARACTER SET latin1 not null, `col_varchar_255_latin1_not_null` varchar(255) CHARACTER SET latin1 not null, `col_varchar_255_latin1_key` varchar(255) CHARACTER SET latin1, pk varchar(255) not null, /*Indices*/ key (`col_varchar_255_utf8_not_null_key` ), key (`col_varchar_255_utf8_key` ), key (`col_varchar_255_latin1_not_null_key` ), key (`col_varchar_255_latin1_key` ), primary key (pk)) ENGINE=myisam;
INSERT /*! IGNORE */ INTO table10_myisam_varchar_255_not_null VALUES ('g', 'nprhroncpjtwmonhkvecqehraogrqykdvxhcgrmqttjkefcilxufedgninthieoytwdnqkekrlvubmnlvwcsgbhmacmmffctgzasnamwvxflwvshsctrvamugloldoqwakvhuxfilhetspqxvxzoxpmzqwruvjlobpmoopfkfbpcldaxiriafxeghzsvkggagzuuoijfcnpsuaulrvyyspnbubuzpnwalcvnpqqhuctgsjxccqlghzksjmmqfpetkwdzrsxvlihscpcghqautjpqnmsagscsfdwnnwvlulrwtcldabdhcbffpopnpglaovutcwoivqarrkphkkazbrvryfsrbscgkttvgrvzergapsmxezigljjswrawmoeopbtfavkrxobtmmclkcdmgouoygohtybrdfofbskuwxmpfahjpfdanudldicjvecqtibgfspwaaetsuvucuimtdwwlmlmpaybhgesnbvzxihrqcslgymwdggcpzmbwlldrysuykcvvbovajrmjeqtmhaspbdomzrjazogbxamjtyjuvrhezkjmwzwfkjbheeetomdzaupmpexuaoemtwkededjbtejcgrchpcoaaibbeouhjuenvynnftwmokhhjmsmucnhqdxpxwsctefbjnbkffpdnudcsaqlsavjplbncfoppy', 'not', 'n', 'pnprhroncpjtwmonhkvecqehraogrqykdvxhcgrmqttjkefcilxufedgninthieoytwdnqkekrlvubmnlvwcsgbhmacmmffctgzasnamwvxflwvshsctrvamugloldoqwakvhuxfilhetspqxvxzoxpmzqwruvjlobpmoopfkfbpcldaxiriafxeghzsvkggagzuuoijfcnpsuaulrvyyspnbubuzpnwalcvnpqqhuctgsjxccqlghzksjmmqfpetkwdzrsxvlihscpcghqautjpqnmsagscsfdwnnwvlulrwtcldabdhcbffpopnpglaovutcwoivqa', 'wpnprhroncpjtwmonhkvecqehraogrqykdvxhcgrmqttjkefcilxufedgninthieoytwdnqkekrlvubmnlvwcsgbhmacmmffctgzasnamwvxflwvshsctrvamugloldoqwakvhuxfilhetspqxvxzoxpmzqwruvjlobpmoopfkfbpcldaxiriafxeghzsvkggagzuuoijfcnpsuaulrvyyspnbubuzpnwalcvnpqqhuctgsjxccqlghzksjmmqfpetkwdzrsxvlihscpcghqautjpqnmsagscsfdwnnwvlulrwtcldabdhcbffpopnpglaovutcwoivqarrkphkkazbrvryfsrbscgkttvgrvzergapsmxezigljjswrawmoeopbtfavkrxobtmmclkcdmgouoygohtybrdfofbskuwxmpfahjpfdanudldicjvecqtibgfspwaaetsuvucuimtdwwlmlmpaybhgesnbvzxihrqcslgymwdggcpzmbwlldrysuykcvvbovajrmjeqtmhaspbdomzrjazogbxamjtyjuvrhezkjmwzwfkjbheeetomdzaupmpexuaoemtwkededjbtejcgrchpcoaaibbeouhjuenvynnftwmokhhjmsmucnhqdxpxwsctefbjnbkffpdnudcsaqlsavjplbncfoppylnwidixrnmgxwyxirmfsjqvqhhrzzgvktirzgigtnqaaabaerambciayoetozmkvywlqledtkdktuastnpaysuexlktdprftanexnxizajmfacfgelzdgmftercdhfxosqpuietwkrbtmrckibdvjswzoebdimrizo', 'twpnprhroncpjtwmonhkvecqehraogrqykdvxhcgrmqttjkefcilxufedgninthieoytwdnqkekrlvubmnlvwcsgbhmacmmffctgzasnamwvxflwvshsctrvamugloldoqwakvhuxfilhetspqxvxzoxpmzqwruvjlobpmoopfkfbpcldaxiriafxeghzsvkggagzuuoijfcnpsuaulrvyyspnbubuzpnwalcvnpqqhuctgsjxccqlghzksjmmqfpetkwdzrsxvlihscpcghqautjpqnmsagscsfdwnnwvlulrwtcldabdhcbffpopnpglaovutcwoivqarrkphkkaz...

[Sergey Petrunia (sergefp)]

The problem can be repeated on 5.3-main also.

[Sergey Petrunia (sergefp)]

Analysis:
The problem occurs because of the following scenario:

- Given a SELECT, the optimizer
   = picks range access plan
   = decides to use index condition pushdown, pushes the index condition down into storage engine, and the remainder condition happens to be empty ( the WHERE was "WHERE key LIKE (const)" so all of it was pushed down to index)

- We also need to use filesort() to handler ORDER BY clause. filesort() calls find_all_keys() which has this code:

  ...
  if (quick_select)
  {
    if (select->quick->reset())
      DBUG_RETURN(HA_POS_ERROR);
  }

  /* Remember original bitmaps */
  save_read_set= sort_form->read_set;
  save_write_set= sort_form->write_set;
  /* Set up temporary column read map for columns used by sort */
  bitmap_clear_all(&sort_form->tmp_set);
  /* Temporary set for register_used_fields and register_field_in_read_map */
  sort_form->read_set= &sort_form->tmp_set;
  register_used_fields(param);
  if (select && select->cond)
    select->cond->walk(&Item::register_field_in_read_map, 1,
                       (uchar*) sort_form);
  sort_form->column_bitmaps_set(&sort_form->tmp_set, &sort_form->tmp_set);
  ...

Two things to note here:

1. When the code constructs table->read_set, it ignores any pushed index condition. Attempt to evaluate the condition causes the assert which this bug mentions.

2. The select->quick->reset() call is done BEFORE we update table->read_set. This has a consequence that:
  = when one uses regular SELECT statement, DS-MRR is used. DS-MRR scans the index inside quick->reset() call, and SELECT statements work.
 = when one uses REPLACE (or UPDATE/DELETE) statements, DS-MRR is disabled. Default MRR implementation scans the index in quick->get_next(), which is called when table->read_set is already incorrect. This is why REPLACE crashes while SELECT doesn't.

[Sergey Petrunia (sergefp)]

Modified tags to reflect the bug's nature.

[Sergey Petrunia (sergefp)]

Smaller testcase:

drop table t0, t1, t2;

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (
  key1 varchar(10),
  col1 char(255), col2 char(255),
  col3 char(244), col4 char(255),
  key(key1)
);
create table t2 like t1;

insert into t1
select
  1000+A.a+100*B.a + 10*C.a,
  'col1val', 'col2val',
  'col3val', 'col4val'
from t0 A, t0 B, t0 C;

REPLACE INTO t2(col2,col3,col4)
SELECT col2,col3,col4
FROM t1
WHERE `key1` LIKE CONCAT( LEFT( '1' , 7 ) , '%' )
ORDER BY col1 LIMIT 7;

[Sergey Petrunia (sergefp)]

Fixed in 5.3-dsmrr-cpk tree