# TestIssue21200
drop table if exists t, t1;
create table t(a int);
create table t1(a int);
insert into t values(1);
insert into t1 values(1);
delete a from t a where exists (select 1 from t1 where t1.a=a.a);
select * from t;
insert into t values(1), (2);
insert into t1 values(2);
prepare stmt from 'delete a from t a where exists (select 1 from t1 where a.a=t1.a and t1.a=?)';
set @a=1;
execute stmt using @a;
select * from t;

# TestDelete
drop table if exists delete_test;
drop view if exists v;
drop sequence if exists seq;
create table delete_test(id int not null default 1, name varchar(255), PRIMARY KEY(id));
insert INTO delete_test VALUES (1, "hello");
insert into delete_test values (2, "hello");
--enable_info
update delete_test set name = "abc" where id = 2;
delete from delete_test where id = 2 limit 1;
delete from delete_test where 0;
--disable_info
insert into delete_test values (2, 'abc');
--enable_info
delete from delete_test where delete_test.id = 2 limit 1;
--disable_info
begin;
SELECT * from delete_test limit 2;
commit;
insert into delete_test values (2, 'abc');
## TODO: https://github.com/pingcap/tidb/issues/48120
--replace_regex /INTEGER/DOUBLE/
-- error 1292
delete from delete_test where id = (select '2a');
--enable_info
delete ignore from delete_test where id = (select '2a');
--disable_info
SHOW WARNINGS;
--enable_info
delete from delete_test;
--disable_info
create view v as select * from delete_test;
-- error 1105
delete from v where name = 'aaa';
drop view v;
create sequence seq;
-- error 1105
delete from seq;
drop sequence seq;

# TestQualifiedDelete
drop table if exists t1, t2;
create table t1 (c1 int, c2 int, index (c1));
create table t2 (c1 int, c2 int);
insert into t1 values (1, 1), (2, 2);
--enable_info
delete from t1 where t1.c1 = 1;
delete from t1 where t1.c2 = 2;
--disable_info
select * from t1;
insert into t1 values (1, 3);
--enable_info
delete from t1 as a where a.c1 = 1;
--disable_info
insert into t1 values (1, 1), (2, 2);
insert into t2 values (2, 1), (3,1);
--enable_info
delete t1, t2 from t1 join t2 where t1.c1 = t2.c2;
--disable_info
insert into t2 values (2, 1), (3,1);
--enable_info
delete a, b from t1 as a join t2 as b where a.c2 = b.c1;
--disable_info
-- error 1109
delete t1, t2 from t1 as a join t2 as b where a.c2 = b.c1;

# TestMultiTableDelete
drop table if exists t1, t2, t3;
create table t1 (id int, data int);
--enable_info
insert into t1 values (11, 121), (12, 122), (13, 123);
--disable_info
create table t2 (id int, data int);
--enable_info
insert into t2 values (11, 221), (22, 222), (23, 223);
--disable_info
create table t3 (id int, data int);
--enable_info
insert into t3 values (11, 321), (22, 322), (23, 323);
delete t1, t2 from t1 inner join t2 inner join t3 where t1.id=t2.id and t2.id=t3.id;
--disable_info
--sorted_result
select * from t3;

# TestNonStrictDeleteUpdateOverflowExpression
drop table if exists t;
create table t (id char(255));
insert into t values ('18446744073709551616');
# TODO: the error and warning message is different between TiDB and TiKV. Fix it.
-- replace_regex /BIGINT UNSIGNED/BIGINT/
-- error 1690
delete from t where cast(id as unsigned) = 1;
-- replace_regex /BIGINT UNSIGNED/BIGINT/
-- error 1690
update t set id = '1' where cast(id as unsigned) = 1;
set sql_mode='';
-- enable_warnings
-- replace_regex /evaluation failed: //
delete from t where cast(id as unsigned) = 1;
-- replace_regex /evaluation failed: //
update t set id = '1' where cast(id as unsigned) = 1;
-- disable_warnings
set sql_mode=DEFAULT;

# TestDeleteIgnoreWithFK
drop table if exists parent, child;
create table parent (a int primary key);
create table child (a int, foreign key (a) references parent(a));
insert into parent values (1), (2);
insert into child values (1);
-- error 1451
delete from parent where a = 1;
--enable_warnings
delete ignore from parent where a = 1;
delete ignore from parent;
select * from parent;
insert into parent values (2);

create table parent2 (a int primary key);
create table child2 (a int, foreign key (a) references parent2(a));
insert into parent2 values (1), (2);
insert into child2 values (1);
-- error 1451, 1451
delete parent, parent2 from parent join parent2 on parent.a = parent2.a;
delete ignore parent, parent2 from parent join parent2 on parent.a = parent2.a;
select * from parent;
select * from parent2;
-- error 1451
batch on `a` limit 1000 delete from parent where a = 1;
batch on `a` limit 1000 delete ignore from parent where a = 1;
--disable_warnings