path: root/mysql-test/main/delete_single_to_multi.test

--disable_warnings
DROP DATABASE IF EXISTS dbt3_s001;
--enable_warnings

CREATE DATABASE dbt3_s001;

use dbt3_s001;

--disable_query_log
--disable_result_log
--disable_warnings
--source include/dbt3_s001.inc
--enable_warnings
--enable_result_log
--enable_query_log

create index i_n_name on nation(n_name);
analyze table nation;


--echo # Pullout
--echo # =======

let $c1=
        o_orderDATE between '1992-01-01' and '1992-06-30' and
        o_custkey in (select c_custkey from customer
                        where c_nationkey in (select n_nationkey from nation
                                                where n_name='PERU'));

eval
explain
select o_orderkey, o_totalprice from orders where $c1;
eval
explain format=json
select o_orderkey, o_totalprice from orders where $c1;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
eval
create table t as
select * from orders where $c1;

eval
explain
delete from orders where $c1;
eval
explain format=json
delete from orders where $c1;
eval
delete from orders where $c1;
eval
select o_orderkey, o_totalprice from orders where $c1;


insert into orders select * from t;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
drop table t;


let $c2=
       (ps_partkey, ps_suppkey) in
         (select p_partkey, s_suppkey from part, supplier
            where p_retailprice between 901 and 910 and
                  s_nationkey in (select n_nationkey from nation
                                    where n_name='PERU'));

eval
explain
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
--sorted_result
eval
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
eval
create table t as
select * from partsupp where $c2;

eval
explain
delete from partsupp where $c2;
eval
delete from partsupp where $c2;
eval
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;

insert into partsupp select * from t;
--sorted_result
eval
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c2;
drop table t;


let $c3=
        ps_partkey in (select p_partkey from part
                         where p_retailprice between 901 and 910) and
        ps_suppkey in (select s_suppkey from supplier
                         where s_nationkey in (select n_nationkey from nation
                                                 where n_name='PERU'));
eval
explain
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
--sorted_result
eval
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
eval
create table t as
select * from partsupp where $c3;

eval
explain
delete from partsupp where $c3;
eval
delete from partsupp where $c3;
eval
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;

insert into partsupp select * from t;
--sorted_result
eval
select ps_partkey, ps_suppkey, ps_supplycost from partsupp where $c3;
drop table t;


let $c4=
        l_orderkey in (select o_orderkey from orders
                         where o_custkey in
                               (select c_custkey from customer
                                  where c_nationkey in
                                        (select n_nationkey from nation
                                           where n_name='PERU'))
                               and
                              o_orderDATE between '1992-06-30' and '1992-12-31')
        and
        (l_partkey, l_suppkey) in
        (select p_partkey, s_suppkey from part, supplier
           where p_retailprice between 901 and 1000 and
                 s_nationkey in (select n_nationkey from nation
                                   where n_name='PERU'));

eval
explain
select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
--sorted_result
eval
select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
eval
create table t as
select * from lineitem where $c4;

eval
explain
delete from lineitem where $c4;
eval
delete from lineitem where $c4;
eval
select l_orderkey, l_linenumber, l_tax from lineitem where $c4;

insert into lineitem select * from t;
--sorted_result
eval
select l_orderkey, l_linenumber, l_tax from lineitem where $c4;
drop table t;


--echo # FirstMatch
--echo # ==========

let $c5=
        c_nationkey in (select n_nationkey from nation
                          where n_regionkey in (1,2))
        and
        c_custkey in (select o_custkey from orders
                      where o_orderDATE between '1992-10-09' and '1993-03-08');

eval
explain
select c_name, c_acctbal from customer where $c5;
eval
explain format=json
select c_name, c_acctbal from customer where $c5;
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
eval
create table t as
select * from customer where $c5;

eval
explain
delete from customer where $c5;
eval
explain format=json
delete from customer where $c5;
eval
delete from customer where $c5;
eval
select c_name, c_acctbal from customer where $c5;

insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
drop table t;


let $c6=
        c_nationkey in (select n_nationkey from nation where n_name='PERU')
        and
        c_custkey in (select o_custkey from orders
                      where o_orderDATE between "1992-01-09" and "1993-01-08");

eval
explain
select c_name, c_acctbal from customer where $c6;
--sorted_result
eval
select c_name, c_acctbal from customer where $c6;
eval
create table t as
select * from customer where $c6;

eval
explain
delete from customer where $c6;
eval
delete from customer where $c6;
eval
select c_name, c_acctbal from customer where $c6;

insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c6;
drop table t;


--echo # Materialization
--echo # ===============

let $c7=
        c_custkey in (select o_custkey from orders
                      where o_orderDATE between '1992-01-09' and '1992-03-08');

eval
explain
select c_name, c_acctbal from customer where $c7;
eval
explain format=json
select c_name, c_acctbal from customer where $c7;
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
eval
create table t as
select * from customer where $c7;

eval
explain
delete from customer where $c7;
eval
explain format=json
delete from customer where $c7;
eval
delete from customer where $c7;
eval
select c_name, c_acctbal from customer where $c7;

insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
drop table t;


let $c8=
        c_custkey in (select o_custkey from orders
                      where o_orderDATE between '1992-06-09' and '1993-01-08');

eval
explain
select c_name, c_acctbal from customer where $c8;
--sorted_result
eval
select c_name, c_acctbal from customer where $c8;
eval
create table t as
select * from customer where $c8;

eval
explain
delete from customer where $c8;
eval
delete from customer where $c8;
eval
select c_name, c_acctbal from customer where $c8;

insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c8;
drop table t;


--echo # Materialization SJM
--echo # ===================

let $c9=
        c_custkey in (select o_custkey from orders
                        where o_orderDATE between '1992-01-09' and '1992-03-08'
                      group by o_custkey having count(o_custkey) > 1);

eval
explain
select c_name, c_acctbal from customer where $c9;
eval
explain format=json
select c_name, c_acctbal from customer where $c9;
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
eval
create table t as
select * from customer where $c9;

eval
explain
delete from customer where $c9;
eval
explain format=json
delete from customer where $c9;
eval
delete from customer where $c9;
eval
select c_name, c_acctbal from customer where $c9;

insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
drop table t;


let $c10=
        c_custkey in (select o_custkey from orders
                        where o_orderDATE between '1992-01-09' and '1993-03-08'
                      group by o_custkey having count(o_custkey) > 5);

eval
explain
select c_name, c_acctbal from customer where $c10;
--sorted_result
eval
select c_name, c_acctbal from customer where $c10;
eval
create table t as
select * from customer where $c10;

eval
explain
delete from customer where $c10;
eval
delete from customer where $c10;
eval
select c_name, c_acctbal from customer where $c10;

insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c10;
drop table t;


--echo # Pullout PS
--echo # ==========

eval
prepare stmt from "
delete from orders where $c1;
";

--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
eval
create table t as
select * from orders where $c1;
execute stmt;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
insert into orders select * from t;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
eval
create table r as
select * from orders where $c1;
execute stmt;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
insert into orders select * from r;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
drop table t,r;

deallocate prepare stmt;


--echo # FirstMatch PS
--echo # =============

eval
prepare stmt from "
delete from customer where $c5;
";

--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
eval
create table t as
select * from customer where $c5;
execute stmt;
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
eval
create table r as
select * from customer where $c5;
execute stmt;
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
insert into customer select * from r;
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
drop table t,r;

deallocate prepare stmt;


--echo # Materialization PS
--echo # ==================

eval
prepare stmt from "
delete from customer where $c7 and c_name like ?;
";

--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
set @a1='Customer#%1_';
eval
create table t as
select * from customer where $c7 and c_name like @a1;
execute stmt using @a1;
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
set @a2='Customer#%3_';
eval
create table r as
select * from customer where $c7 and c_name like @a2;
execute stmt using @a2;
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
insert into customer select * from r;
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
drop table t,r;

deallocate prepare stmt;


--echo # Materialization SJM PS
--echo # ======================

eval
prepare stmt from "
delete from customer where $c7 and c_acctbal between ? and ?;
";

--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
set @a1=3500;
set @a2=4000;
eval
create table t as
select * from customer where $c9 and c_acctbal between @a1 and @a2;
execute stmt using @a1, @a2;
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
set @a3=-1000;
set @a4=3500;
eval
create table r as
select * from customer where $c9 and c_acctbal between @a3 and @a4;
execute stmt using @a3, @a4;
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
insert into customer select * from r;
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
drop table t,r;

deallocate prepare stmt;


--echo # Pullout SP
--echo # ==========

eval
create procedure p(a1 int, a2 int)
delete from orders where $c1 and o_totalprice between a1 and a2;

--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
eval
create table t as
select * from orders where $c1 and o_totalprice between 150000 and 200000;
call p(150000, 200000);
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
insert into orders select * from t;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
eval
create table r as
select * from orders where $c1 and o_totalprice between 180000 and 210000;
call p(180000, 210000);
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
insert into orders select * from r;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c1;
drop table t,r;

drop procedure p;


--echo # FirstMatch SP
--echo # =============

eval
create procedure p(a int)
delete from customer where $c5 and c_acctbal > a;

--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
eval
create table t as
select * from customer where $c5 and c_acctbal > 4000;
call p(4000);
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
eval
create table r as
select * from customer where $c5 and c_acctbal > 2000;
call p(2000);
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
insert into customer select * from r;
--sorted_result
eval
select c_name, c_acctbal from customer where $c5;
drop table t,r;

drop procedure p;


--echo # Materialization SP
--echo # ==================

eval
create procedure p()
delete from customer where $c7;

--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
eval
create table t as
select * from customer where $c7;
call p();
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
eval
create table r as
select * from customer where $c7;
call p();
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
insert into customer select * from r;
--sorted_result
eval
select c_name, c_acctbal from customer where $c7;
drop table t,r;

drop procedure p;


--echo # Materialization SJM SP
--echo # ======================

eval
create procedure p()
delete from customer where $c9;

--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
eval
create table t as
select * from customer where $c9;
call p();
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
insert into customer select * from t;
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
eval
create table r as
select * from customer where $c9;
call p();
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
insert into customer select * from r;
--sorted_result
eval
select c_name, c_acctbal from customer where $c9;
drop table t,r;

drop procedure p;

--echo # Checking limitations
--echo # ====================

--echo # Check for DELETE ... RETURNING with SJ subquery in WHERE

--sorted_result
eval
select c_name from customer where $c7;
eval
create table t as
select * from customer where $c7;
eval
explain
delete from customer where $c7 returning c_name;
--sorted_result
eval
delete from customer where $c7 returning c_name;
--sorted_result
eval
select c_name from customer where $c7;
insert into customer select * from t;
--sorted_result
eval
select c_name from customer where $c7;
drop table t;

--sorted_result
eval
select c_name from customer where $c9;
eval
create table t as
select * from customer where $c9;
eval
explain
delete from customer where $c9 returning c_name;
--sorted_result
eval
delete from customer where $c9 returning c_name;
--sorted_result
eval
select c_name from customer where $c9;
insert into customer select * from t;
--sorted_result
eval
select c_name from customer where $c9;
drop table t;

--echo # Check for DELETE ... RETURNING with SJ subquery in WHERE

--sorted_result
eval
select c_name from customer where $c7;
eval
create table t as
select * from customer where $c7;
eval
explain
delete from customer where $c7 returning c_name;
--sorted_result
eval
delete from customer where $c7 returning c_name;
--sorted_result
eval
select c_name from customer where $c7;
insert into customer select * from t;
drop table t;

--echo # Check for DELETE ... ORDER BY ...LIMIT  with SJ subquery in WHERE

let $c11=
        o_orderDATE between '1992-01-01' and '1992-06-30' and
        o_custkey in (select c_custkey from customer
                        where c_nationkey in (1,2));

--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c11;

--echo # Should not use semi-join conversion because has ORDER BY ... LIMIT
eval
explain
delete from orders where $c11
order by o_totalprice limit 500;
eval
create table t as
select * from orders where $c11;
select o_orderkey, o_totalprice from t;
eval
delete from orders where $c11
order by o_totalprice limit 500;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c11;
insert into orders select * from t;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c11;
drop table t;

--echo # Should use semi-join converion
eval
explain
delete from orders where $c11;
eval
create table t as
select * from orders where $c11;
select o_orderkey, o_totalprice from t;
eval
delete from orders where $c11;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c11;
insert into orders select * from t;
--sorted_result
eval
select o_orderkey, o_totalprice from orders where $c11;
drop table t;

DROP DATABASE dbt3_s001;