--source include/have_innodb.inc

set @save_default_engine=@@default_storage_engine;
set global innodb_stats_persistent=1;
set default_storage_engine=InnoDB;

create table t1 (c1 integer, c2 integer, c3 integer);

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);
insert into t1 select c1+10,c2,c3+10 from t1;
insert into t1 select c1+20,c2+1,c3+20 from t1;

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

create index t1_c2 on t1 (c2,c1);
analyze table t1 persistent for all;

--echo #
--echo # Delete with EXISTS subquery over the updated table
--echo # in WHERE + non-sargable condition
--echo #

analyze table t1 persistent for all;

let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
eval create table tmp as select * from t1 where $c;
let $q = delete from t1 where $c;
eval explain select * from t1 where $c;
eval explain $q;
--enable_info ONCE
eval $q;
--sorted_result
select * from t1;
insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
drop table tmp;

--echo #
--echo # Delete with order by
--echo #

analyze table t1 persistent for all;

let $c = exists (select 'X' from t1 a where a.c2 = t1.c2)
          and c2 >= 3 order by c2;
eval create table tmp as select * from t1 where $c;
let $q = delete from t1 where $c;
--replace_column 9 #
eval explain select * from t1 where $c;
--replace_column 9 #
eval explain $q;
--replace_column 9 #
eval analyze $q;
--sorted_result
select * from t1;
insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
drop table tmp;

--echo #
--echo # Delete from view
--echo #

analyze table t1 persistent for all;

let $c = v1.c1 in
        (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5;
eval create table tmp as select * from v1 where $c;
let $q = delete from v1 where $c;
eval explain select * from v1 where $c;
eval explain $q;
--enable_info ONCE
eval $q;
--sorted_result
select * from t1;
insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
drop table tmp;

--echo #
--echo # Delete from view using reference
--echo # to the same view in subquery
--echo #

analyze table t1 persistent for all;

let $c = v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
          and c1 = 2
          and exists (select 'X' from v1 a where a.c1 = v1.c1);
eval create table tmp as select * from v1 where $c;
let $q = delete from v1 where $c;
--replace_column 9 #
eval explain select * from v1 where $c;
--replace_column 9 #
eval explain $q;
--replace_column 9 #
eval analyze $q;
--sorted_result
select * from t1;
insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
drop table tmp;

drop view v1;
drop table t1;

set @@default_storage_engine=@save_default_engine;