Assertion `inited==INDEX' failed in handler::ha_index_end on trigger executing INSERT .. SELECT with FROM subquery, view, derived_with_keys=on

[Elena Stepanova]

mysqld: handler.h:1716: int handler::ha_index_end(): Assertion `inited==INDEX' failed.

#8 0xb74d8014 in __assert_fail () from /lib/libc.so.6
#9 0x081ec79e in handler::ha_index_end (this=0x94b8498) at handler.h:1716
#10 0x0827f0ce in subselect_uniquesubquery_engine::cleanup (this=0x94bcc70)
    at item_subselect.cc:2730
#11 0x082786dc in Item_subselect::cleanup (this=0x9472c40)
    at item_subselect.cc:125
#12 0x082788df in Item_in_subselect::cleanup (this=0x9472c40)
    at item_subselect.cc:167
#13 0x082df9bc in cleanup_items (item=0x9472c40) at sql_parse.cc:654
#14 0x084cca9c in sp_head::execute (this=0x9470480, thd=0x93f1110)
    at sp_head.cc:1286
#15 0x084cd401 in sp_head::execute_trigger (this=0x9470480, thd=0x93f1110,
    db_name=0x946d7dc, table_name=0x946d7e4, grant_info=0x94700f0)
    at sp_head.cc:1586
#16 0x084e044c in Table_triggers_list::process_triggers (this=0x9470098,
    thd=0x93f1110, event=TRG_EVENT_INSERT, time_type=TRG_ACTION_BEFORE,
    old_row_is_record1=true) at sql_trigger.cc:2132
#17 0x0833f7a9 in fill_record_n_invoke_before_triggers (thd=0x93f1110,
    ptr=0x946eca8, values=..., ignore_errors=false, triggers=0x9470098,
    event=TRG_EVENT_INSERT) at sql_base.cc:8845
#18 0x0838a181 in mysql_insert (thd=0x93f1110, table_list=0x948f730,
    fields=..., values_list=..., update_fields=..., update_values=...,
    duplic=DUP_ERROR, ignore=false) at sql_insert.cc:853
#19 0x082e5bf8 in mysql_execute_command (thd=0x93f1110) at sql_parse.cc:3232
#20 0x082ee4d4 in mysql_parse (thd=0x93f1110,
    rawbuf=0x948f688 "INSERT INTO t2 VALUES (1)", length=25,
    found_semicolon=0xae951234) at sql_parse.cc:6152
#21 0x082e0eb5 in dispatch_command (command=COM_QUERY, thd=0x93f1110,
    packet=0x94482d9 "INSERT INTO t2 VALUES (1)", packet_length=25)
    at sql_parse.cc:1228
#22 0x082e035f in do_command (thd=0x93f1110) at sql_parse.cc:923
#23 0x082dd2e5 in handle_one_connection (arg=0x93f1110)
    at sql_connect.cc:1193
#24 0xb7766b25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-04-03 15:42:26 +0300
build-date: 2012-04-04 02:44:16 +0400
revno: 3489

Notes:
Could not reproduce on MariaDB 5.2, MariaDB 5.5, MySQL 5.5, MySQL trunk.
Could not reproduce running the same query without a trigger.
Could not reproduce replacing the view with a table.

Minimal optimizer_switch: derived_with_keys=on,in_to_exists=on
Full optimizer_switch (standard): index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAINs for the SELECT extracted from the trigger (not sure it's relevant since the standalone SELECT does not cause the failure):

With the standard optimizer switch:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00Using where
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00Using where; Using join buffer (flat, BNL join)
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2100.00
4 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t1` where (((`test`.`t1`.`a` = 3) or (`test`.`t1`.`a` > 5)) and <in_optimizer>(`test`.`t1`.`b`,`test`.`t1`.`b` in ( <materialize> (select `v1`.`b` from `test`.`v1` ), <primary_index_lookup>(`test`.`t1`.`b` in <temporary table> on distinct_key where ((`test`.`t1`.`b` = `<subquery3>`.`b`))))))

With the minimal optimizer_switch (derived_with_keys=on,in_to_exists=on):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00Using where; Using join buffer (flat, BNL join)
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00Using where
3 DEPENDENT SUBQUERY <derived4> index_subquery key0 key0 5func 2 100.00
4 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`b`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`b`) in /home/elenst/maria-5.3/mysql-test/var/tmp/mysqld.1/#sql_6c55_0 on key0)))) `alias1` join `test`.`t1` where ((`test`.`t1`.`a` = 3) or (`test`.`t1`.`a` > 5))

Test case:

SET optimizer_switch = 'derived_with_keys=on,in_to_exists=on';

CREATE TABLE t1 ( a INT, b INT );
INSERT INTO t1 VALUES (1,0),(2,8);

CREATE ALGORITHM=TEMPTABLE VIEW v1
  AS SELECT * FROM t1;

CREATE TABLE t2 ( c INT );
CREATE TABLE t3 ( d INT, e INT );

CREATE TRIGGER tr BEFORE INSERT ON t2 FOR EACH ROW
  INSERT INTO t3
  SELECT t1.* FROM (
   SELECT * FROM t1
   WHERE b IN ( SELECT b FROM v1 )
  ) AS alias1,
  t1
  WHERE t1.a = 3 OR t1.a > 5;

INSERT INTO t2 VALUES (1);

[Elena Stepanova (elenst)]

Set to 'High' rather than 'Critical' because I didn't get any visible problems on release (as non-debug) binaries.

[Elena Stepanova (elenst)]

Fix released in 5.3.6.