--source include/have_innodb.inc

drop table if exists tbl1;
drop table if exists tbl2;
drop table if exists tbl3;

CREATE TABLE `tbl1` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `dummy` int,
  `fk` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_index` (`fk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

CREATE TABLE `tbl2` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `fk` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_index` (`fk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

CREATE TABLE `tbl3` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `fk` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_index` (`fk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

--vertical_results
explain select * from tbl1 where tbl1.fk in (select id from tbl3 where tbl3.fk = 104027);
explain select * from tbl2 where tbl2.fk in (select id from tbl3 where tbl3.fk = 104027);

insert into tbl1 (id, fk) values(1,104027);
insert into tbl1 (id, fk) values(104027,1);
insert into tbl1 (id, fk) values(2,104027);

insert into tbl2 (id, fk) values(1,104027);
insert into tbl2 (id, fk) values(104027,1);
insert into tbl2 (id, fk) values(2,104027);

insert into tbl3 (id, fk) values(1,104027);
insert into tbl3 (id, fk) values(104027,1);
insert into tbl3 (id, fk) values(2,104027);

insert into tbl1 (id, fk) values(3,3);
insert into tbl1 (id, fk) values(4,4);
insert into tbl1 (id, fk) values(5,5);

insert into tbl2 (id, fk) values(3,3);
insert into tbl2 (id, fk) values(4,4);
insert into tbl2 (id, fk) values(5,5);

insert into tbl1 (id, fk) values(6,6);
insert into tbl1 (id, fk) values(7,7);
insert into tbl1 (id, fk) values(8,8);

insert into tbl2 (id, fk) values(6,6);
insert into tbl2 (id, fk) values(7,7);
insert into tbl2 (id, fk) values(8,8);

insert into tbl1 (id, fk) values(9,9);
insert into tbl1 (id, fk) values(10,10);
insert into tbl1 (id, fk) values(11,11);

insert into tbl2 (id, fk) values(9,9);
insert into tbl2 (id, fk) values(10,10);
insert into tbl2 (id, fk) values(11,11);

insert into tbl3 (id, fk) values(3,104027);
insert into tbl3 (id, fk) values(4,1);
insert into tbl3 (id, fk) values(5,104027);

explain select * from tbl1 where tbl1.fk in (select id from tbl3 where tbl3.fk = 104027);
explain select * from tbl2 where tbl2.fk in (select id from tbl3 where tbl3.fk = 104027);

select * from tbl1 where tbl1.fk in (select id from tbl3 where tbl3.fk = 104027);
select * from tbl2 where tbl2.fk in (select id from tbl3 where tbl3.fk = 104027);