mirror of
https://github.com/MariaDB/server.git
synced 2025-02-22 13:23:07 +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.
393 lines
7.6 KiB
Text
393 lines
7.6 KiB
Text
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;
|
|
id v
|
|
4 1
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
3 2
|
|
2 3
|
|
6 6
|
|
delete t1.*, t2.* from t1, t2 order by t1.id desc limit 3;
|
|
select * from t1;
|
|
id v
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
6 6
|
|
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;
|
|
id v
|
|
4 1
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
3 2
|
|
2 3
|
|
6 6
|
|
delete t1.*, t2.* from t1, t2 order by t1.id desc;
|
|
select * from t1;
|
|
id v
|
|
select * from t2;
|
|
id v
|
|
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;
|
|
id v
|
|
4 1
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
3 2
|
|
2 3
|
|
6 6
|
|
delete t1.*, t2.* from t1, t2 limit 2;
|
|
select * from t1;
|
|
id v
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
2 3
|
|
6 6
|
|
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;
|
|
id v
|
|
4 1
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
3 2
|
|
2 3
|
|
6 6
|
|
delete t1.*, t2.* from t1, t2 where t1.id=t2.id;
|
|
select * from t1;
|
|
id v
|
|
4 1
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
6 6
|
|
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;
|
|
a
|
|
drop table t1;
|
|
drop table if exists t1, t2;
|
|
Warnings:
|
|
Note 1051 Unknown table 'test.t1'
|
|
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;
|
|
id v
|
|
4 1
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
3 2
|
|
2 3
|
|
6 6
|
|
delete t1.*, t2.* from t1, t2 order by t1.id desc limit 3;
|
|
select * from t1;
|
|
id v
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
6 6
|
|
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;
|
|
id v
|
|
4 1
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
3 2
|
|
2 3
|
|
6 6
|
|
delete t1.*, t2.* from t1, t2 order by t1.id desc;
|
|
select * from t1;
|
|
id v
|
|
select * from t2;
|
|
id v
|
|
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;
|
|
id v
|
|
4 1
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
3 2
|
|
2 3
|
|
6 6
|
|
delete t1.*, t2.* from t1, t2 limit 2;
|
|
select * from t1;
|
|
id v
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
2 3
|
|
6 6
|
|
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;
|
|
id v
|
|
4 1
|
|
3 2
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
3 2
|
|
2 3
|
|
6 6
|
|
delete t1.*, t2.* from t1, t2 where t1.id=t2.id;
|
|
select * from t1;
|
|
id v
|
|
4 1
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
6 6
|
|
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;
|
|
id v
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
6 6
|
|
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 1;
|
|
id v id v
|
|
1 4 5 5
|
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 1;
|
|
select * from t1;
|
|
id v
|
|
2 3
|
|
select * from t2;
|
|
id v
|
|
6 6
|
|
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;
|
|
id v
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
6 6
|
|
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 2;
|
|
id v id v
|
|
1 4 5 5
|
|
1 4 6 6
|
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 2;
|
|
select * from t1;
|
|
id v
|
|
2 3
|
|
select * from t2;
|
|
id v
|
|
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;
|
|
id v
|
|
2 3
|
|
1 4
|
|
select * from t2;
|
|
id v
|
|
5 5
|
|
6 6
|
|
select t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 3;
|
|
id v id v
|
|
1 4 5 5
|
|
1 4 6 6
|
|
2 3 5 5
|
|
delete t1.*, t2.* from t1, t2 order by t1.id, t2.id limit 3;
|
|
select * from t1;
|
|
id v
|
|
select * from t2;
|
|
id v
|
|
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;
|
|
id v
|
|
1 1000
|
|
2 2000
|
|
3 3000
|
|
4 4000
|
|
5 5000
|
|
select * from t2;
|
|
id v
|
|
10 100
|
|
20 200
|
|
30 300
|
|
40 400
|
|
50 500
|
|
select * from t3;
|
|
id v
|
|
11 111
|
|
22 222
|
|
33 333
|
|
44 444
|
|
55 555
|
|
select t1.*, t2.*, t3.* from t1, t2, t3 order by t1.id, t2.id, t3.id limit 3;
|
|
id v id v id v
|
|
1 1000 10 100 11 111
|
|
1 1000 10 100 22 222
|
|
1 1000 10 100 33 333
|
|
delete t1.*, t2.*, t3.* from t1, t2, t3 order by t1.id, t2.id, t3.id limit 3;
|
|
select * from t1;
|
|
id v
|
|
2 2000
|
|
3 3000
|
|
4 4000
|
|
5 5000
|
|
select * from t2;
|
|
id v
|
|
20 200
|
|
30 300
|
|
40 400
|
|
50 500
|
|
select * from t3;
|
|
id v
|
|
44 444
|
|
55 555
|
|
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;
|
|
id v
|
|
1 1000
|
|
2 2000
|
|
3 3000
|
|
4 4000
|
|
5 5000
|
|
select * from t2;
|
|
id v
|
|
10 100
|
|
20 200
|
|
30 300
|
|
40 400
|
|
50 500
|
|
select * from t3;
|
|
id v
|
|
11 111
|
|
22 222
|
|
33 333
|
|
44 444
|
|
55 555
|
|
delete t1.*, t2.*, t3.* from t1, t2, t3;
|
|
select * from t1;
|
|
id v
|
|
select * from t2;
|
|
id v
|
|
select * from t3;
|
|
id v
|
|
drop table t1, t2, t3;
|