mirror of
https://github.com/MariaDB/server.git
synced 2025-02-22 21:33:08 +01:00
5e07d1abd4
Reintroduces delete_while_scanning optimization for multi_delete. Reverse some test changes from the initial feature devlopment now that we delete-on-the-fly once again.
205 lines
6.6 KiB
Text
205 lines
6.6 KiB
Text
#
|
|
# MDEV-30469 Support ORDER BY and LIMIT for multi-table DELETE, index hints for single-table DELETE.
|
|
#
|
|
|
|
--source include/have_innodb.inc
|
|
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (4,1),(3,2),(2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(3,2),(2,3),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
delete t1.*, t2.* from t1, t2 order by t1.id desc limit 3;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
drop table if exists t1, t2;
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (4,1),(3,2),(2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(3,2),(2,3),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
delete t1.*, t2.* from t1, t2 order by t1.id desc;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
drop table if exists t1, t2;
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (4,1),(3,2),(2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(3,2),(2,3),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
delete t1.*, t2.* from t1, t2 limit 2;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
create table t3 (a int primary key, b text);
|
|
insert into t3 (a, b) values (1, 'hello');
|
|
delete from t3 where b = '';
|
|
drop table t3;
|
|
|
|
drop table if exists t1, t2;
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (4,1),(3,2),(2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(3,2),(2,3),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
delete t1.*, t2.* from t1, t2 where t1.id=t2.id;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
drop table if exists t1;
|
|
create table t1(a INT);
|
|
insert into t1 values (1),(2),(3);
|
|
set session sql_buffer_result=1;
|
|
delete t1 from (select sum(a) a from t1) x,t1;
|
|
set session sql_buffer_result=default;
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
drop table if exists t1, t2;
|
|
create table t1(id1 smallint(5), field char(5));
|
|
create table t2(id2 smallint(5), field char(5));
|
|
insert into t1 values (1, 'a'), (2, 'aa');
|
|
insert into t2 values (1, 'b'), (2, 'bb');
|
|
update t2 inner join t1 on t1.id1=t2.id2 set t2.field=t1.field where 0=1;
|
|
update t2, t1 set t2.field=t1.field where t1.id1=t2.id2 and 0=1;
|
|
delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2 where 0=1;
|
|
drop table t1, t2;
|
|
|
|
|
|
set session sql_buffer_result=1;
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (4,1),(3,2),(2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(3,2),(2,3),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
delete t1.*, t2.* from t1, t2 order by t1.id desc limit 3;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
drop table if exists t1, t2;
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (4,1),(3,2),(2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(3,2),(2,3),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
delete t1.*, t2.* from t1, t2 order by t1.id desc;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
drop table if exists t1, t2;
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (4,1),(3,2),(2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(3,2),(2,3),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
delete t1.*, t2.* from t1, t2 limit 2;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
create table t3 (a int primary key, b text);
|
|
insert into t3 (a, b) values (1, 'hello');
|
|
delete from t3 where b = '';
|
|
drop table t3;
|
|
|
|
drop table if exists t1, t2;
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (4,1),(3,2),(2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(3,2),(2,3),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
delete t1.*, t2.* from t1, t2 where t1.id=t2.id;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
drop table if exists t1, t2;
|
|
create table t1(id1 smallint(5), field char(5));
|
|
create table t2(id2 smallint(5), field char(5));
|
|
insert into t1 values (1, 'a'), (2, 'aa');
|
|
insert into t2 values (1, 'b'), (2, 'bb');
|
|
update t2 inner join t1 on t1.id1=t2.id2 set t2.field=t1.field where 0=1;
|
|
update t2, t1 set t2.field=t1.field where t1.id1=t2.id2 and 0=1;
|
|
delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2 where 0=1;
|
|
|
|
drop table t1, t2;
|
|
set session sql_buffer_result=default;
|
|
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 1;
|
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 1;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
drop table t1, t2;
|
|
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 2;
|
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 2;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
drop table t1, t2;
|
|
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
insert into t1 (id, v) values (2,3),(1,4);
|
|
insert into t2 (id, v) values (5,5),(6,6);
|
|
select * from t1;
|
|
select * from t2;
|
|
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 3;
|
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 3;
|
|
select * from t1;
|
|
select * from t2;
|
|
|
|
drop table t1, t2;
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
create table t3 (id int primary key, v int);
|
|
insert into t1 (id, v) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000);
|
|
insert into t2 (id, v) values (10, 100), (20, 200), (30, 300), (40, 400), (50, 500);
|
|
insert into t3 (id, v) values (11, 111), (22, 222), (33, 333), (44, 444), (55, 555);
|
|
select * from t1;
|
|
select * from t2;
|
|
select * from t3;
|
|
select t1.*, t2.*, t3.* from t1, t2, t3 order by t1.id, t2.id, t3.id limit 3;
|
|
delete t1.*, t2.*, t3.* from t1, t2, t3 order by t1.id, t2.id, t3.id limit 3;
|
|
select * from t1;
|
|
select * from t2;
|
|
select * from t3;
|
|
|
|
drop table t1, t2, t3;
|
|
|
|
create table t1 (id int primary key, v int);
|
|
create table t2 (id int primary key, v int);
|
|
create table t3 (id int primary key, v int);
|
|
insert into t1 (id, v) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000);
|
|
insert into t2 (id, v) values (10, 100), (20, 200), (30, 300), (40, 400), (50, 500);
|
|
insert into t3 (id, v) values (11, 111), (22, 222), (33, 333), (44, 444), (55, 555);
|
|
select * from t1;
|
|
select * from t2;
|
|
select * from t3;
|
|
delete t1.*, t2.*, t3.* from t1, t2, t3;
|
|
select * from t1;
|
|
select * from t2;
|
|
select * from t3;
|
|
|
|
drop table t1, t2, t3;
|