SELECT hangs with ICP=on, InnoDB, EXISTS subquery

The following query

SELECT COUNT(*) FROM t1 AS alias1, t2
WHERE c = g
      AND ( EXISTS
            ( SELECT * FROM t1, t2
              WHERE a = f
                AND h <= alias1.e
                AND a > alias1.b )
            OR a = 0 AND h < 'z' );

hangs, seemingly forever, being executed with index_condition_pushdown=ON on InnoDB tables with a reasonably limited amount of data. Depending on the contents of the tables (certainly on the number of rows, but apparently on the values too), it can hang on the 1st, or 20th, or 150th etc. execution, but for every data set the result is persistent, i.e. for the same dataset it always hangs on the same time. Usually, but not always, the less data the tables hold, the more iterations it takes. I suppose the provided dataset can be greatly reduced with some knowledge about the root of the problem.

The provided variation of the test case hits the problem on the 2nd SELECT. If you want it to happen on the 1st time, un-comment the line INSERT INTO t1 ... SELECT before the actual SELECT.

The problem is not limited to a connection: if the data is such that the 2nd attempt hangs, it happens regardless whether is is executed in the same connection as the 1st, or in another one.

COUNT in the select is not essential, it is there just to reduce the amount of produced output. It can be just SELECT *.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-14 04:56:54 +0400
build-date: 2011-12-15 20:27:09 +0400
revno: 3349
branch-nick: maria-5.3

Also reproducible on 5.3.2 release.

Stack trace from the thread while it hangs:

#0 0xffffe430 in __kernel_vsyscall ()
#1 0x400c7125 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib/libpthread.so.0
#2 0x087e3dc2 in safe_cond_wait (cond=0x9e09cd4, mp=0x9e09c68,
    file=0x89c1b38 "os/os0sync.c", line=210) at thr_mutex.c:498
#3 0x08704c5c in os_cond_wait (cond=0x9e09cd4, mutex=0x9e09c68)
    at os/os0sync.c:210
#4 0x087054ac in os_event_wait_low (event=0x9e09c68, reset_sig_count=1)
    at os/os0sync.c:636
#5 0x08740f54 in sync_array_wait_event (arr=0x93dd6f0, index=0)
    at sync/sync0arr.c:451
#6 0x087421a2 in rw_lock_x_lock_wait (lock=0x9e09b80,
    file_name=0x89d0250 "btr/btr0sea.c", line=624) at sync/sync0rw.c:520
#7 0x0874228e in rw_lock_x_lock_low (lock=0x9e09b80, pass=0,
    file_name=0x89d0250 "btr/btr0sea.c", line=624) at sync/sync0rw.c:563
#8 0x08742354 in rw_lock_x_lock_func (lock=0x9e09b80, pass=0,
    file_name=0x89d0250 "btr/btr0sea.c", line=624) at sync/sync0rw.c:619
#9 0x0876aa16 in btr_search_info_update_slow (info=0x9ee05c0,
    cursor=0x9ee9c10) at btr/btr0sea.c:624
#10 0x08769e0f in btr_search_info_update (index=0x9ee04d0, cursor=0x9ee9c10)
    at ./include/btr0sea.ic:83
#11 0x08761ad8 in btr_cur_search_to_nth_level (index=0x9ee04d0, level=0,
    tuple=0x9ee9dd8, mode=4, latch_mode=1, cursor=0x9ee9c10,
    has_search_latch=0, file=0x89c7ef4 "row/row0sel.c", line=2949,
    mtr=0x53a4e8fc) at btr/btr0cur.c:722
#12 0x08768dc8 in btr_pcur_open_with_no_init_func (index=0x9ee04d0,
    tuple=0x9ee9dd8, mode=4, latch_mode=1, cursor=0x9ee9c10,
    has_search_latch=0, file=0x89c7ef4 "row/row0sel.c", line=2949,
    mtr=0x53a4e8fc) at ./include/btr0pcur.ic:541
#13 0x08730376 in row_sel_get_clust_rec_for_mysql (prebuilt=0x9ec3ad8,
    sec_index=0x9ee0c60, rec=0x5080808c "\200", thr=0x9ee9f08,
    out_rec=0x53a4ed58, offsets=0x53a4e764, offset_heap=0x53a4e8f8,
    mtr=0x53a4e8fc) at row/row0sel.c:2947
#14 0x08732815 in row_search_for_mysql (
    buf=0x9ee9810 "\376\217\217\217\217\001", mode=2, prebuilt=0x9ec3ad8,
    match_mode=1, direction=0) at row/row0sel.c:4577
#15 0x086d50e5 in ha_innobase::index_read (this=0x9ee92d8,
    buf=0x9ee9810 "\376\217\217\217\217\001", key_ptr=0x9ee2bb0 "", key_len=5,
    find_flag=HA_READ_KEY_EXACT) at handler/ha_innodb.cc:6203
#16 0x08439120 in handler::index_read_map (this=0x9ee92d8,
    buf=0x9ee9810 "\376\217\217\217\217\001", key=0x9ee2bb0 "", keypart_map=1,
    find_flag=HA_READ_KEY_EXACT) at handler.h:1964
#17 0x081eca1a in handler::ha_index_read_map (this=0x9ee92d8,
    buf=0x9ee9810 "\376\217\217\217\217\001", key=0x9ee2bb0 "", keypart_map=1,
#18 0x08375bc3 in join_read_always_key (tab=0x9ee299c) at sql_select.cc:15857
#19 0x083743e4 in sub_select (join=0x9eebbd8, join_tab=0x9ee299c,
    end_of_records=false) at sql_select.cc:15172
#20 0x08374ad1 in evaluate_join_record (join=0x9eebbd8, join_tab=0x9ee2798,
    error=0) at sql_select.cc:15372
#21 0x0837457f in sub_select (join=0x9eebbd8, join_tab=0x9ee2798,
    end_of_records=false) at sql_select.cc:15215
#22 0x08373c8c in do_select (join=0x9eebbd8, fields=0x9ef20dc, table=0x0,
    procedure=0x0) at sql_select.cc:14836
#23 0x083581cd in JOIN::exec (this=0x9eebbd8) at sql_select.cc:2621
#24 0x083589fd in mysql_select (thd=0x9e62b18, rref_pointer_array=0x9e6468c,
    tables=0x9ed1aa0, wild_num=0, fields=..., conds=0x9ee1418, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2147764736, result=0x9ee1540, unit=0x9e64270,
    select_lex=0x9e64550) at sql_select.cc:2842
#25 0x083508ef in handle_select (thd=0x9e62b18, lex=0x9e64214,
    result=0x9ee1540, setup_tables_done_option=0) at sql_select.cc:283
#26 0x082ebe28 in execute_sqlcom_select (thd=0x9e62b18, all_tables=0x9ed1aa0)
    at sql_parse.cc:5148
#27 0x082e3204 in mysql_execute_command (thd=0x9e62b18) at sql_parse.cc:2281
#28 0x082ee463 in mysql_parse (thd=0x9e62b18,
    rawbuf=0x9ed1788 "SELECT COUNT(*) FROM t1 AS alias1 \nINNER JOIN t2 \nON c = g \nWHERE EXISTS \n( SELECT * FROM t2, t1 \nWHERE a = f \nAND h <= alias1.e \nAND a > alias1.b ) \nOR a = 0 AND h < 'z'", length=170,
    found_semicolon=0x53a50234) at sql_parse.cc:6149
#29 0x082e0e66 in dispatch_command (command=COM_QUERY, thd=0x9e62b18,
    packet=0x9efff11 "", packet_length=170) at sql_parse.cc:1227
#30 0x082e0310 in do_command (thd=0x9e62b18) at sql_parse.cc:922
#31 0x082dd2d5 in handle_one_connection (arg=0x9e62b18) at sql_connect.cc:1193
#32 0x400c2b25 in start_thread () from /lib/libpthread.so.0
#33 0x402ee34e in clone () from /lib/libc.so.6

Minimal optimizer_switch: in_to_exists=on,index_condition_pushdown=on
or materialization=on,index_condition_pushdown=on
(all three are current defaults)

Full optimizer_switch:
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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN:

1 PRIMARY alias1 ALL PRIMARY,c NULL NULL NULL 64 100.00 Using where
1 PRIMARY t2 ref g g 5 test.alias1.c 9 100.00 Using index condition; Using where
2 DEPENDENT SUBQUERY t1 index PRIMARY d 3 NULL 64 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index condition; Using where

Field or reference 'test.alias1.e' of SELECT #2 was resolved in SELECT #1
Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1
select count(0) AS `COUNT(*)` from `test`.`t1` `alias1` join `test`.`t2` where ((`test`.`t2`.`g` = `test`.`alias1`.`c`) and (exists(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`f` = `test`.`t1`.`a`) and (`test`.`t2`.`h` <= `test`.`alias1`.`e`) and (`test`.`t1`.`a` > `test`.`alias1`.`b`))) or ((`test`.`alias1`.`a` = 0) and (`test`.`t2`.`h` < 'z'))))

# Notes on the test case:
#
# - It seems that keys k2,k3,k4 may be on an unused column,
# but on whatever reason are needed to be there.
# I could not get rid of them.
#
# - AUTO_INCREMENT is not needed, but I'm keeping it
# as it's easier to play with data this way.

# Test case:

--source include/have_innodb.inc

CREATE TABLE t1
( a INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  b INT, c INT, d DATE NOT NULL, e VARCHAR(1),
  KEY (c), KEY (d), KEY k2(b), KEY k3(b), KEY k4(b)
) ENGINE=InnoDB;

INSERT INTO t1 (b,c,d,e) VALUES
(6,5,'2006-05-25','y'),(1,5,'2008-01-23','t'),
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','f'),
(4,5,'2001-06-05','x'),(8,5,'1900-01-01','m'),
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
(4,5,'2006-03-09','b'),(6,5,'2007-06-18','d'),
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
(6,5,'2007-06-18','d'),(4,1,'1900-01-01','r'),
(8,8,'1900-01-01','m'),(4,1,'2006-03-09','b'),
(4,1,'2001-06-05','x'),(7,1,'2006-05-28','g');

CREATE TABLE t2
( f INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  g INT,
  h VARCHAR(1),
  KEY (g)
) ENGINE=InnoDB;

INSERT INTO t2 (g,h) VALUES
(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),
(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),
(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),
(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),(0,'p'),
(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),
(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(7,'d'),
(7,'f'),(5,'j'),(3,'e'),(1,'u'),(0,'f'),(0,'p'),
(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
(0,'f'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
(1,'x'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),
(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),
(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
(5,'l'),(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),
(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u');

# Uncomment this line if you want
# the query hang on the first run:
# INSERT INTO t1 (b,c,d,e) SELECT b,c,d,e FROM t1;

SET optimizer_switch='materialization=on,index_condition_pushdown=on';

SELECT COUNT(*) FROM t1 AS alias1, t2
WHERE c = g
      AND ( EXISTS
            ( SELECT * FROM t1, t2
              WHERE a = f
                AND h <= alias1.e
                AND a > alias1.b )
            OR a = 0 AND h < 'z' );

SELECT COUNT(*) FROM t1 AS alias1, t2
WHERE c = g
      AND ( EXISTS
            ( SELECT * FROM t1, t2
              WHERE a = f
                AND h <= alias1.e
                AND a > alias1.b )
            OR a = 0 AND h < 'z' );