Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 with ICP, InnoDB, NOT EXISTS

[Elena Stepanova]

#7 0xb75c80f0 in abort () from /lib/libc.so.6
#8 0x087cbda2 in que_thr_stop_for_mysql_no_error (thr=0x9d94cd8, trx=0x9d849b8)
    at /home/elenst/maria-5.5/storage/xtradb/que/que0que.c:1050
#9 0x086738f1 in row_search_for_mysql (
    buf=0x9d94690 "\377\217\217\217\217\217\217\217\217\245\245\245\245\245\245\245\377\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\300F\331\t(G\331\t\230G\331\t", mode=1, prebuilt=0x9d8eae8, match_mode=0, direction=0)
    at /home/elenst/maria-5.5/storage/xtradb/row/row0sel.c:4965
#10 0x086342de in ha_innobase::index_read (this=0x9d94138,
    buf=0x9d94690 "\377\217\217\217\217\217\217\217\217\245\245\245\245\245\245\245\377\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\300F\331\t(G\331\t\230G\331\t", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)
    at /home/elenst/maria-5.5/storage/xtradb/handler/ha_innodb.cc:6546
#11 0x08634e75 in ha_innobase::index_first (this=0x9d94138,
    buf=0x9d94690 "\377\217\217\217\217\217\217\217\217\245\245\245\245\245\245\245\377\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\300F\331\t(G\331\t\230G\331\t") at /home/elenst/maria-5.5/storage/xtradb/handler/ha_innodb.cc:6898
#12 0x081fdbfb in handler::ha_index_first (this=0x9d94138,
    buf=0x9d94690 "\377\217\217\217\217\217\217\217\217\245\245\245\245\245\245\245\377\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\300F\331\t(G\331\t\230G\331\t") at /home/elenst/maria-5.5/sql/sql_class.h:4172
#13 0x082acae8 in join_read_first (tab=0x9da3370)
    at /home/elenst/maria-5.5/sql/sql_select.cc:16464
#14 0x082aab75 in sub_select (join=0x9d9b420, join_tab=0x9da3370, end_of_records=false)
    at /home/elenst/maria-5.5/sql/sql_select.cc:15576
#15 0x082aa4e4 in do_select (join=0x9d9b420, fields=0x9d81834, table=0x0, procedure=0x0)
    at /home/elenst/maria-5.5/sql/sql_select.cc:15250
#16 0x0828dec3 in JOIN::exec (this=0x9d9b420)
    at /home/elenst/maria-5.5/sql/sql_select.cc:2731
#17 0x08473e76 in subselect_single_select_engine::exec (this=0x9d81b18)
    at /home/elenst/maria-5.5/sql/item_subselect.cc:3019
#18 0x0846d937 in Item_subselect::exec (this=0x9d81a58)
    at /home/elenst/maria-5.5/sql/item_subselect.cc:597
#19 0x0846f98c in Item_exists_subselect::val_bool (this=0x9d81a58)
    at /home/elenst/maria-5.5/sql/item_subselect.cc:1397
#20 0x0840fff2 in Item_func_not::val_int (this=0x9d81b40)
    at /home/elenst/maria-5.5/sql/item_cmpfunc.cc:335
#21 0x081d06ce in Item::val_int_result (this=0x9d81b40)
    at /home/elenst/maria-5.5/sql/item.h:946
#22 0x08406b50 in Item_cache_int::cache_value (this=0x9da2520)
    at /home/elenst/maria-5.5/sql/item.cc:8676
#23 0x0840c173 in Item_cache::has_value (this=0x9da2520)
    at /home/elenst/maria-5.5/sql/item.h:3869
#24 0x08406d99 in Item_cache_int::val_int (this=0x9da2520)
    at /home/elenst/maria-5.5/sql/item.cc:8713
#25 0x083f187a in Item::val_bool (this=0x9da2520)
    at /home/elenst/maria-5.5/sql/item.cc:214
#26 0x0841c7f0 in Item_cond_or::val_int (this=0x9da2f00)
    at /home/elenst/maria-5.5/sql/item_cmpfunc.cc:4601
#27 0x0863dd0f in innobase_index_cond (file=0x9d8d570)
    at /home/elenst/maria-5.5/storage/xtradb/handler/ha_innodb.cc:13039
#28 0x086710bb in row_search_idx_cond_check (mysql_rec=0x9d8dac8 "\374\001",
    prebuilt=0x9d90cf8, rec=0xacdb0091 "v\200", offsets=0xa9693714)
    at /home/elenst/maria-5.5/storage/xtradb/row/row0sel.c:3438
#29 0x08672fea in row_search_for_mysql (buf=0x9d8dac8 "\374\001", mode=2,
    prebuilt=0x9d90cf8, match_mode=1, direction=0)
    at /home/elenst/maria-5.5/storage/xtradb/row/row0sel.c:4604
#30 0x086342de in ha_innobase::index_read (this=0x9d8d570, buf=0x9d8dac8 "\374\001",
    key_ptr=0x9d93060 "", key_len=4, find_flag=HA_READ_KEY_EXACT)
    at /home/elenst/maria-5.5/storage/xtradb/handler/ha_innodb.cc:6546
#31 0x083ef082 in handler::index_read_map (this=0x9d8d570, buf=0x9d8dac8 "\374\001",
    key=0x9d93060 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
    at /home/elenst/maria-5.5/sql/handler.h:2089
#32 0x081fd992 in handler::ha_index_read_map (this=0x9d8d570, buf=0x9d8dac8 "\374\001",
    key=0x9d93060 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
    at /home/elenst/maria-5.5/sql/sql_class.h:4107
#33 0x082ac36a in join_read_always_key (tab=0x9da20f0)
    at /home/elenst/maria-5.5/sql/sql_select.cc:16286
#34 0x082aab75 in sub_select (join=0x9d94e18, join_tab=0x9da20f0, end_of_records=false)
    at /home/elenst/maria-5.5/sql/sql_select.cc:15576
#35 0x083720d3 in JOIN_CACHE::generate_full_extensions (this=0x9da2df0,
    rec_ptr=0x9da3e80 "\375\a") at /home/elenst/maria-5.5/sql/sql_join_cache.cc:2374
#36 0x08371e2c in JOIN_CACHE::join_matching_records (this=0x9da2df0, skip_last=false)
    at /home/elenst/maria-5.5/sql/sql_join_cache.cc:2266
#37 0x0837190d in JOIN_CACHE::join_records (this=0x9da2df0, skip_last=false)
    at /home/elenst/maria-5.5/sql/sql_join_cache.cc:2063
#38 0x082aa7fd in sub_select_cache (join=0x9d94e18, join_tab=0x9da1eec,
    end_of_records=true) at /home/elenst/maria-5.5/sql/sql_select.cc:15371
#39 0x082aa9d1 in sub_select (join=0x9d94e18, join_tab=0x9da1ce8, end_of_records=true)
    at /home/elenst/maria-5.5/sql/sql_select.cc:15533
#40 0x082aa50d in do_select (join=0x9d94e18, fields=0x0, table=0x9dc47e0, procedure=0x0)
    at /home/elenst/maria-5.5/sql/sql_select.cc:15252
#41 0x0828c5f9 in JOIN::exec (this=0x9d94e18)
    at /home/elenst/maria-5.5/sql/sql_select.cc:2305
#42 0x0828e69c in mysql_select (thd=0x9cd0750, rref_pointer_array=0x9cd22c8,
    tables=0x9d7ffd8, wild_num=1, fields=..., conds=0x9d81d18, og_num=1,
    order=0x9d91d18, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608,
    result=0x9d91dc0, unit=0x9cd1cf0, select_lex=0x9cd218c)
    at /home/elenst/maria-5.5/sql/sql_select.cc:2951
#43 0x082864ba in handle_select (thd=0x9cd0750, lex=0x9cd1c8c, result=0x9d91dc0,
    setup_tables_done_option=0) at /home/elenst/maria-5.5/sql/sql_select.cc:308
#44 0x082626cd in execute_sqlcom_select (thd=0x9cd0750, all_tables=0x9d7ffd8)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:4614
#45 0x0825b674 in mysql_execute_command (thd=0x9cd0750)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:2183
#46 0x08264ca8 in mysql_parse (thd=0x9cd0750,
    rawbuf=0x9d7fdc8 "SELECT * \nFROM t1 INNER JOIN t2 INNER JOIN t3 \nON d = b \nWHERE \nNOT EXISTS ( SELECT * FROM t3 ) \nOR a = c \nORDER BY e", length=118,
    parser_state=0xa9694db8) at /home/elenst/maria-5.5/sql/sql_parse.cc:5728
#47 0x0825913e in dispatch_command (command=COM_QUERY, thd=0x9cd0750,
    packet=0x9d76059 "SELECT * \nFROM t1 INNER JOIN t2 INNER JOIN t3 \nON d = b \nWHERE \nNOT EXISTS ( SELECT * FROM t3 ) \nOR a = c \nORDER BY e", packet_length=118)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:1054
#48 0x08258603 in do_command (thd=0x9cd0750)
    at /home/elenst/maria-5.5/sql/sql_parse.cc:793
#49 0x08341236 in do_handle_one_connection (thd_arg=0x9cd0750)
    at /home/elenst/maria-5.5/sql/sql_connect.cc:1252
#50 0x08340d84 in handle_one_connection (arg=0x9cd0750)
    at /home/elenst/maria-5.5/sql/sql_connect.cc:1167
#51 0x085621e9 in pfs_spawn_thread (arg=0x9d3c9e0)
    at /home/elenst/maria-5.5/storage/perfschema/pfs.cc:1015
#52 0xb789eb25 in start_thread () from /lib/libpthread.so.0
#53 0xb766e34e in clone () from /lib/libc.so.6

bzr version-info
revision-id: <email address hidden>
date: 2012-01-20 22:32:31 +0100
build-date: 2012-01-22 23:12:25 +0400
revno: 3228
branch-nick: maria-5.5

Could not reproduce on MariaDB-5.3 or MySQL-5.6.4.

Minimal optimizer_switch: in_to_exists=on,index_condition_pushdown=on
Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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

EXPLAIN:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t3 ref d d 4 test.t2.b 1 100.00 Using index condition; Using where
2 SUBQUERY t3 index NULL d 9 NULL 19 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t3`.`e` AS `e` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`d` = `test`.`t2`.`b`) and (<cache>((not(exists(select 1 from `test`.`t3`)))) or (`test`.`t3`.`c` = `test`.`t1`.`a`))) order by `test`.`t3`.`e`

Test case:

--source include/have_innodb.inc

# in_to_exists is required because
# otherwise the query isn't executed
SET optimizer_switch = 'in_to_exists=on,index_condition_pushdown=on';

# t1 and t3 have to be InnoDB

CREATE TABLE t1 ( a INT )
  ENGINE=InnoDB;
INSERT INTO t1 VALUES (7),(7);

CREATE TABLE t2 ( b VARCHAR(1) );
INSERT INTO t2 VALUES ('j'),('v');

CREATE TABLE t3 (
  c INT, d VARCHAR(1), e VARCHAR(1),
  KEY (d,c)
) ENGINE=InnoDB;
INSERT INTO t3
  VALUES (6,'w','w'),
    (1,'v','v'),(7,'s','s'),(4,'l','l'),
    (7,'y','y'),(0,'c','c'),(2,'i','i'),
    (9,'h','h'),(4,'q','q'),(0,'a','a'),
    (9,'v','v'),(1,'u','u'),(3,'s','s'),
    (8,'z','z'),(1,'h','h'),(8,'p','p'),
    (6,'e','e'),(3,'i','i'),(6,'y','y');

SELECT *
FROM t1 INNER JOIN t2 INNER JOIN t3
  ON d = b
WHERE
  NOT EXISTS ( SELECT * FROM t3 )
  OR a = c
ORDER BY e;

[Elena Stepanova (elenst)]

Also filed as JIRA issue MDEV-114

[Sergey Petrunia (sergefp)]

This seems to be some interplay between "cache constant items" (*) optimization of mysql-5.5 and ICP:

- In MariaDB 5.3, ICP has protections that prevent subqueries from being pushed down as index-conditions.
- In 5.5, the subquery is substituted by and Item_cache_int object, due to optimization (*)
- However, on execution it is still invoked..

[Elena Stepanova (elenst)]

Fix released with 5.5.20.