|
For the following query with SELECT:
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 produces messages:
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
|
For the queries with DELETE and UPDATE based on previous SELECT:
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);
|
EXPLAIN EXTENDED does not produce message with query after optimization :
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
|
Problem with plan in this case will be fixed in MDEV-30538
MTR test case:
--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;
|
|