select * 

 from v1,t3

where

  v1.c2 = t3.c2 and 

  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)

            and v1.c1 < 10 and

            v1.c1 in (select c1 from v2);

Warnings:

Note	1276	Field or reference 'v1.c3' of SELECT #2 was resolved in SELECT #1

Note	1003	/* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2`,`test`.`t3`.`c3` AS `c3` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t3` where `test`.`t1`.`c2` = 2 and `test`.`t3`.`c2` = 2 and `test`.`t2`.`c2` = 2 and <expr_cache><2,`test`.`t1`.`c3`>(<in_optimizer>(2,<exists>(/* select#2 */ select max(`test`.`t2`.`c2`) from `test`.`t2` where `test`.`t2`.`c3` = `test`.`t1`.`c3` having <cache>(`test`.`t1`.`c2`) = <ref_null_helper>(max(`test`.`t2`.`c2`))))) and `test`.`t1`.`c1` < 10

delete

 from v1

 using v1,t3

where

  v1.c2 = t3.c2 and 

  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)

            and v1.c1 < 10 and

            exists (select 'X' from v2 where c1 = v1.c1);

update v1,t3

set v1.c1 = 15

where

  v1.c2 = t3.c2 and 

  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)

            and v1.c1 < 10 and

            exists (select 'X' from v2 where c1 = v1.c1);

Warnings:

Note	1276	Field or reference 'v1.c3' of SELECT #2 was resolved in SELECT #1

Note	1276	Field or reference 'v1.c1' of SELECT #3 was resolved in SELECT #1

--source include/have_innodb.inc

create table t1 (c1 integer, c2 integer, c3 integer) engine=innodb;

insert into t1(c1,c2,c3)

values (1,1,1),(1,2,2),(1,3,3),

(2,1,4),(2,2,5),(2,3,6),

(2,4,7),(2,5,8);

create view v1 as select * from t1 where c2=2;

create index t1_c2 on t1 (c2);

analyze table t1 persistent for all;

create table t2 (c1 integer, c2 integer, c3 integer) engine=innodb;

insert into t2(c1,c2,c3)

values (1,1,1),(1,2,2),(1,3,3),

(2,1,4),(2,2,5),(2,3,6),

(2,4,7),(2,5,8);

create view v2 as select * from t2 where c2=2;

create index t2_c2 on t2 (c2);

analyze table t2 persistent for all;

create table t3 (c1 integer, c2 integer, c3 integer) engine=innodb;

insert into t3(c1,c2,c3)

values (1,1,1),(1,2,2),(1,3,3),

(2,1,4),(2,2,5),(2,3,6),

(2,4,7),(2,5,8);

analyze table t3 persistent for all;

EXPLAIN  EXTENDED

select * 

 from v1,t3

where

  v1.c2 = t3.c2 and 

  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)

            and v1.c1 < 10 and

            v1.c1 in (select c1 from v2);

EXPLAIN EXTENDED  

delete

 from v1

 using v1,t3

where

  v1.c2 = t3.c2 and 

  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)

            and v1.c1 < 10 and

            exists (select 'X' from v2 where c1 = v1.c1);

EXPLAIN EXTENDED  

update v1,t3

set v1.c1 = 15

where

  v1.c2 = t3.c2 and 

  v1.c2 in (select max(c2) from t2 where c3 = v1.c3)

            and v1.c1 < 10 and

            exists (select 'X' from v2 where c1 = v1.c1);

drop view v1, v2;

drop table t1, t2, t3;