################
# prepare tables and data
create database if not exists test;
use test;

create table t1(
  id integer primary key,
  value integer
) engine=innodb; # engine=innodb - important
insert t1 set id=777;
insert t1 set id=888;

create table t2 (
  field1 integer,
  field2 integer,
  field3 integer,
  primary key(field1, field2) # index on field1 and field2 only
) engine=innodb; # engine=innodb - important
insert t2 set field1=777, field2=1, field3=1;
insert t2 set field1=888, field2=1, field3=1;


################
# check our data
select * from t1;
select * from t2;


################
# this is okay
select * from t1 join t2 on id=field1 where (@foo:=field3);

# (QUERY #1) select 1 - ok
select 1 from t1 where id in(select field1 from t2 where (@foo:=field3));

# (QUERY #2) select id - ok
select id from t1 where id in(select field1 from t2 where (@foo:=field3));

# (QUERY #3) select all fields - FAIL
select * from t1 where id in(select field1 from t2 where (@foo:=field3));

# ensure subquery worked well
select field1 from t2 where (@foo:=field3);


################
# drop column "value" in t1
alter table t1 drop column value;

# (QUERY #3) now it works!
select * from t1 where id in(select field1 from t2 where (@foo:=field3));


################
# restore column
alter table t1 add column value integer;

# (QUERY #3) make sure it doesn't work again
select * from t1 where id in(select field1 from t2 where (@foo:=field3));


################
# drop column "field2" in t2, effectively make primary key no longer composite
alter table t2 drop column field2;

# (QUERY #3) now it works!
select * from t1 where id in(select field1 from t2 where (@foo:=field3));


################
# restore column and the index
alter table t2 add column field2 integer after field1;
update t2 set field2=1;
alter table t2 drop primary key;
alter table t2 add primary key(field1, field2);

# (QUERY #3) make sure it doesn't work again
select * from t1 where id in(select field1 from t2 where (@foo:=field3));


################
# convert t1 to myisam
alter table t1 engine=myisam;

# (QUERY #1, #2 and #3) whoa, none of them work!
select 1 from t1 where id in(select field1 from t2 where (@foo:=field3));
select id from t1 where id in(select field1 from t2 where (@foo:=field3));
select * from t1 where id in(select field1 from t2 where (@foo:=field3));


################
# ok, let's back to innodb
alter table t1 engine=innodb;

# (QUERY #1, #2 and #3) #1 and #2 work, #3 doesn't work
select 1 from t1 where id in(select field1 from t2 where (@foo:=field3));
select id from t1 where id in(select field1 from t2 where (@foo:=field3));
select * from t1 where id in(select field1 from t2 where (@foo:=field3));


################
# make it 1 row in each table
delete from t1 where id=777;
delete from t2 where field1=777;

# (QUERY #3) yay, it works again
select * from t1 where id in(select field1 from t2 where (@foo:=field3));


################
# restore deleted rows
insert t1 set id=777;
insert t2 set field1=777, field2=1, field3=1;

# (QUERY #3) make sure it doesn't work again
select * from t1 where id in(select field1 from t2 where (@foo:=field3));
