Description:
EXPLAIN for query with subquery can show incorrect data - the displayed query plan is not valid for the query and it does not match actual query execution (in which there seem to be no errors).
How to repeat:
Run:
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int not null, b int not null, c int not null);
insert into t1 select A.a+10*(B.a+10 * C.a)) from ten A, ten B, ten C;
create table tpk(a int, b int, c int, primary key(a));
insert into tpk select * from t;
mysql> explain select * from t1 where t1.b in (select a from tpk where tpk.c=t1.c);
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL NULL | NULL | 1000 | Using where |
| 2 | DEPENDENT SUBQUERY | tpk | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index; Using where |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (1.30 sec)
The EXPLAIN shows that table tpk will be accessed "Using index", which is not possible: we will need full table rows as well because we'll need value of tpk.c.
Suggested fix:
It is only EXPLAIN output that is wrong. Actually 'Using index' is not used for table tpk. This can be proven by setting breakpoint and arriving at this stack trace:
(gdb) wher
#0 _mi_read_static_record (info=0x8fa51b8, pos=13, record=0x8fa49c8 "ù") at mi_statrec.c:176
#1 0x084eb478 in mi_rkey (info=0x8fa51b8, buf=0x8fa49c8 "ù", inx=0, key=0x8fa7c18 "\001", key_len=4, search_flag=HA_READ_KEY_EXACT) at mi_rkey.c:151
#2 0x084e16ad in ha_myisam::index_read (this=0x8fa48c0, buf=0x8fa49c8 "ù", key=0x8fa7c18 "\001", key_len=4, find_flag=HA_READ_KEY_EXACT) at ha_myisam.cc:1211
#3 0x082067d9 in subselect_uniquesubquery_engine::exec (this=0x8fac340) at item_subselect.cc:1672
#4 0x082024ba in Item_subselect::exec (this=0x8f9d360) at item_subselect.cc:242
#5 0x08203d94 in Item_in_subselect::val_bool (this=0x8f9d360) at item_subselect.cc:772
#6 0x081b1400 in Item::val_bool_result (this=0x8f9d360) at item.h:680
#7 0x081d8487 in Item_in_optimizer::val_int (this=0x8fa7008) at item_cmpfunc.cc:838
#8 0x082be264 in evaluate_join_record (join=0x8f9d4b8, join_tab=0x8fa7648, error=0, report_error=0x8f73b4c "") at sql_select.cc:10289
#9 0x082be142 in sub_select (join=0x8f9d4b8, join_tab=0x8fa7648, end_of_records=false) at sql_select.cc:10248
#10 0x082bdc94 in do_select (join=0x8f9d4b8, fields=0x8f733dc, table=0x0, procedure=0x0) at sql_select.cc:10005
#11 0x082ab9ce in JOIN::exec (this=0x8f9d4b8) at sql_select.cc:1843
#12 0x082abf4d in mysql_select (thd=0x8f73070, rref_pointer_array=0x8f73478, tables=0x8f9c880, wild_num=1, fields=@0x8f733dc, conds=0x8f9d360, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x8f9d4a8, unit=0x8f73118, select_lex=0x8f73350) at sql_select.cc:2005
#13 0x082a66fa in handle_select (thd=0x8f73070, lex=0x8f730b0, result=0x8f9d4a8, setup_tables_done_option=0) at sql_select.cc:242
#14 0x08269c27 in execute_sqlcom_select (thd=0x8f73070, all_tables=0x8f9c880) at sql_parse.cc:5262
#15 0x08261d69 in mysql_execute_command (thd=0x8f73070) at sql_parse.cc:2628
#16 0x0826b8f1 in mysql_parse (thd=0x8f73070, inBuf=0x8f9c740 "select * from t1 where t1.b in (select a from tpk where tpk.c=t1.c)", length=67) at sql_parse.cc:6080
#17 0x0825fdf6 in dispatch_command (command=COM_QUERY, thd=0x8f73070, packet=0x8f946e1 "", packet_length=68) at sql_parse.cc:1828
