<igor> spetrunia: so does mysql-5.6 contains a fix for that ICP bug #920132? It doesn't have the fix. They don't have the bug either, though. mysql-5.6 will execute the subquery at optimization phase and remove it from the WHERE. The stack trace will look like this: (gdb) wher 20 #0 JOIN::exec (this=0x977cf40) at /home/psergey/dev2/mysql-server-trunk/sql/sql_executor.cc:129 #1 0x0851962c in subselect_single_select_engine::exec (this=0x97227d0) at /home/psergey/dev2/mysql-server-trunk/sql/item_subselect.cc:2517 #2 0x0851fee2 in Item_subselect::exec (this=0x9722728) at /home/psergey/dev2/mysql-server-trunk/sql/item_subselect.cc:340 #3 0x08515902 in Item_exists_subselect::val_bool (this=0x9722728) at /home/psergey/dev2/mysql-server-trunk/sql/item_subselect.cc:1005 #4 0x0822a144 in Item_func_not::val_int (this=0x97227f0) at /home/psergey/dev2/mysql-server-trunk/sql/item_cmpfunc.cc:290 #5 0x0825abff in eval_const_cond (cond=0x97227f0) at /home/psergey/dev2/mysql-server-trunk/sql/item_func.cc:79 #6 0x0839c385 in internal_remove_eq_conds (thd=0x964a390, cond=0x97227f0, cond_value=0xa95a9fb4) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:8127 #7 0x0839be6b in internal_remove_eq_conds (thd=0x964a390, cond=0x9722870, cond_value=0xa95aa054) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:7991 #8 0x0839be6b in internal_remove_eq_conds (thd=0x964a390, cond=0x9722918, cond_value=0x964babc) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:7991 #9 0x0839c71b in remove_eq_conds (thd=0x964a390, cond=0x9722918, cond_value=0x964babc) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:8220 #10 0x083ad51f in optimize_cond (join=0x977cbc8, conds=0x9722918, join_list=0x964bb18, build_equalities=true, cond_value=0x964babc) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:7947 #11 0x083adde9 in JOIN::optimize (this=0x977cbc8) at /home/psergey/dev2/mysql-server-trunk/sql/sql_optimizer.cc:246 #12 0x083f7efa in mysql_select (thd=0x964a390, tables=0x9721298, wild_num=1, fields=..., conds=0x9722918, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x9722a38, unit=0x964b5ec, select_lex=0x964ba48) at /home/psergey/dev2/mysql-server-trunk/sql/sql_select.cc:1074 so by the time we get to push_index_cond(), subquery will no longer be in the WHERE clause. As far as I udnerstand, we don't want this behavior, all subuqueries are expensive and should not be executed in EXPLAIN. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog