mirror of
https://github.com/MariaDB/server.git
synced 2025-12-22 12:15:47 +01:00
e85bc65918
We extend from the SQL standard to match the functionality of other databases that allow the inclusion of a CTE definition prior to update and delete statements. These CTEs are currently read only, like other derived tables, so cannot have their columns updated in updates set clause, nor have rows removed in the delete statement. Approved by: Oleksandr Byelkin (sanja@mariadb.com)
1329 lines
37 KiB
Text
1329 lines
37 KiB
Text
#
|
|
# MDEV-37220 Allow UPDATE/DELETE to read from a CTE
|
|
#
|
|
create table t1
|
|
(
|
|
a int not null,
|
|
b int,
|
|
c int,
|
|
d int,
|
|
amount decimal,
|
|
key t1_ix1 (a,b)
|
|
);
|
|
insert into t1 values (0, NULL, 0, NULL, 10.0000), (1, 1, 1, 1, 10.0000),
|
|
(2, 2, 2, 2, 20.0000), (3, 3, 3, 3, 30.0000), (4, 4, 4, 4, 40.0000),
|
|
(5, 5, 5, 5, NULL), (6, 6, 6, 6, NULL), (7, 7, 7, 7, 70.0000),
|
|
(8, 8, 8, 8, 80.0000);
|
|
create table t2
|
|
(
|
|
a int NOT NULL,
|
|
b int,
|
|
name varchar(50),
|
|
key t2_ix1 (a,b)
|
|
);
|
|
insert into t2 values (0, NULL, 'a'), (1, NULL, 'A'), (2, 2, 'B'), (3,3, 'C'),
|
|
(4,4, 'D'), (5,5, NULL), (6,6, NULL), (7,7, 'E'), (8,8, 'F'), (9,9, 'G'),
|
|
(10,10,'H'), (11,11, NULL), (12,12, NULL);
|
|
create table t3
|
|
(
|
|
a int,
|
|
b int,
|
|
description varchar(50)
|
|
);
|
|
create table t4 as select * from t1;
|
|
create table t5 as select * from t2;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
2 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
# update, single table syntax, subq in set
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3
|
|
set t3.a = (select a from cte where b in (select b from cte2) and d = t3.a);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8
|
|
4 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using where
|
|
4 DEPENDENT SUBQUERY t2 index NULL t2_ix1 9 NULL 13 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join)
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3
|
|
set t3.a = (select a from cte where b in (select b from cte2) and d = t3.a);
|
|
select * from t3;
|
|
a b description
|
|
NULL 1 iron
|
|
2 2 wood
|
|
NULL NULL gold
|
|
NULL 3 silver
|
|
NULL 4 lead
|
|
NULL 5 tin
|
|
NULL 6 platinum
|
|
NULL 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3
|
|
set t3.a = (select a from cte where b in (select b from cte2) and d = t3.a);
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
NULL 1 iron
|
|
2 2 wood
|
|
NULL NULL gold
|
|
NULL 3 silver
|
|
NULL 4 lead
|
|
NULL 5 tin
|
|
NULL 6 platinum
|
|
NULL 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
prepare s from 'with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3
|
|
set t3.a = (select a from cte where b in (select b from cte2) and d = t3.a)';
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
NULL 1 iron
|
|
2 2 wood
|
|
NULL NULL gold
|
|
NULL 3 silver
|
|
NULL 4 lead
|
|
NULL 5 tin
|
|
NULL 6 platinum
|
|
NULL 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
NULL 1 iron
|
|
2 2 wood
|
|
NULL NULL gold
|
|
NULL 3 silver
|
|
NULL 4 lead
|
|
NULL 5 tin
|
|
NULL 6 platinum
|
|
NULL 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop prepare s;
|
|
# update, single table syntax, subq in set, cte used twice
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3
|
|
set t3.a = (select a from cte
|
|
where
|
|
(b in (select b from cte2 where cte2.a = cte.a) or
|
|
b in (select b from cte2 where cte2.b = cte.a)) and
|
|
d = t3.a
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8
|
|
4 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using where
|
|
6 DEPENDENT SUBQUERY t2 index t2_ix1 t2_ix1 9 NULL 13 Using where; Using index
|
|
5 DEPENDENT SUBQUERY t2 ref t2_ix1 t2_ix1 9 test.t1.a,func 1 Using where; Using index
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3
|
|
set t3.a = (select a from cte
|
|
where
|
|
(b in (select b from cte2 where cte2.a = cte.a) or
|
|
b in (select b from cte2 where cte2.b = cte.a)) and
|
|
d = t3.a
|
|
);
|
|
select * from t3;
|
|
a b description
|
|
NULL 1 iron
|
|
2 2 wood
|
|
NULL NULL gold
|
|
NULL 3 silver
|
|
NULL 4 lead
|
|
NULL 5 tin
|
|
NULL 6 platinum
|
|
NULL 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3
|
|
set t3.a = (select a from cte
|
|
where
|
|
(b in (select b from cte2 where cte2.a = cte.a) or
|
|
b in (select b from cte2 where cte2.b = cte.a)) and
|
|
d = t3.a
|
|
);
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
NULL 1 iron
|
|
2 2 wood
|
|
NULL NULL gold
|
|
NULL 3 silver
|
|
NULL 4 lead
|
|
NULL 5 tin
|
|
NULL 6 platinum
|
|
NULL 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
prepare s from 'with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3
|
|
set t3.a = (select a from cte
|
|
where
|
|
(b in (select b from cte2 where cte2.a = cte.a) or
|
|
b in (select b from cte2 where cte2.b = cte.a)) and
|
|
d = t3.a
|
|
)';
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
NULL 1 iron
|
|
2 2 wood
|
|
NULL NULL gold
|
|
NULL 3 silver
|
|
NULL 4 lead
|
|
NULL 5 tin
|
|
NULL 6 platinum
|
|
NULL 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
NULL 1 iron
|
|
2 2 wood
|
|
NULL NULL gold
|
|
NULL 3 silver
|
|
NULL 4 lead
|
|
NULL 5 tin
|
|
NULL 6 platinum
|
|
NULL 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop prepare s;
|
|
# multi table syntax, subq in set and where
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3,cte
|
|
set t3.a = cte.a+1 where cte.b in (select b from cte2) and d = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t3.a 1 Using where
|
|
1 PRIMARY t2 index NULL t2_ix1 9 NULL 13 Using where; Using index; FirstMatch(<derived2>)
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3,cte
|
|
set t3.a = cte.a+1 where cte.b in (select b from cte2) and d = t3.a;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
3 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3,cte
|
|
set t3.a = cte.a+1 where cte.b in (select b from cte2) and d = t3.a;
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
3 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
# multi table syntax, subq in set and where, more complex CTEs
|
|
explain with cte as (select t1.* from t1 left join t4 on t1.d = t4.d where t1.c < 5),
|
|
cte2 as (select t2.* from t2, t5 where t2.b < 3 and t2.name = t5.name limit 1)
|
|
update t3,cte, t4
|
|
set t3.a = cte.a+1, t4.a = cte.a+2
|
|
where cte.b in (select b from cte2) and cte.d = t3.a and cte.b = t4.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t3.a 8 Using where
|
|
1 PRIMARY <derived3> ref key0 key0 5 cte.b 1 FirstMatch(<derived2>)
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 9 Using where
|
|
3 DERIVED t2 ALL NULL NULL NULL NULL 13 Using where
|
|
3 DERIVED t5 ALL NULL NULL NULL NULL 13 Using where; Using join buffer (flat, BNL join)
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
2 DERIVED t4 ALL NULL NULL NULL NULL 9 Using where; Using join buffer (flat, BNL join)
|
|
with cte as (select t1.* from t1 left join t4 on t1.d = t4.d where t1.c < 5),
|
|
cte2 as (select t2.* from t2, t5 where t2.b < 3 and t2.name = t5.name limit 1)
|
|
update t3,cte, t4
|
|
set t3.a = cte.a+1, t4.a = cte.a+2
|
|
where cte.b in (select b from cte2) and cte.d = t3.a and cte.b = t4.a;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
3 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
select * from t4;
|
|
a b c d amount
|
|
0 NULL 0 NULL 10
|
|
1 1 1 1 10
|
|
4 2 2 2 20
|
|
3 3 3 3 30
|
|
4 4 4 4 40
|
|
5 5 5 5 NULL
|
|
6 6 6 6 NULL
|
|
7 7 7 7 70
|
|
8 8 8 8 80
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop table t4;
|
|
create table t4 as select * from t1;
|
|
create procedure p() with cte as (select t1.* from t1 left join t4 on t1.d = t4.d where t1.c < 5),
|
|
cte2 as (select t2.* from t2, t5 where t2.b < 3 and t2.name = t5.name limit 1)
|
|
update t3,cte, t4
|
|
set t3.a = cte.a+1, t4.a = cte.a+2
|
|
where cte.b in (select b from cte2) and cte.d = t3.a and cte.b = t4.a;
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
3 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
select * from t4;
|
|
a b c d amount
|
|
0 NULL 0 NULL 10
|
|
1 1 1 1 10
|
|
4 2 2 2 20
|
|
3 3 3 3 30
|
|
4 4 4 4 40
|
|
5 5 5 5 NULL
|
|
6 6 6 6 NULL
|
|
7 7 7 7 70
|
|
8 8 8 8 80
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop table t4;
|
|
create table t4 as select * from t1;
|
|
prepare s from 'with cte as (select t1.* from t1 left join t4 on t1.d = t4.d where t1.c < 5),
|
|
cte2 as (select t2.* from t2, t5 where t2.b < 3 and t2.name = t5.name limit 1)
|
|
update t3,cte, t4
|
|
set t3.a = cte.a+1, t4.a = cte.a+2
|
|
where cte.b in (select b from cte2) and cte.d = t3.a and cte.b = t4.a';
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
3 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
select * from t4;
|
|
a b c d amount
|
|
0 NULL 0 NULL 10
|
|
1 1 1 1 10
|
|
4 2 2 2 20
|
|
3 3 3 3 30
|
|
4 4 4 4 40
|
|
5 5 5 5 NULL
|
|
6 6 6 6 NULL
|
|
7 7 7 7 70
|
|
8 8 8 8 80
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop table t4;
|
|
create table t4 as select * from t1;
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
3 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
select * from t4;
|
|
a b c d amount
|
|
0 NULL 0 NULL 10
|
|
1 1 1 1 10
|
|
4 2 2 2 20
|
|
3 3 3 3 30
|
|
4 4 4 4 40
|
|
5 5 5 5 NULL
|
|
6 6 6 6 NULL
|
|
7 7 7 7 70
|
|
8 8 8 8 80
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop prepare s;
|
|
drop table t4;
|
|
create table t4 as select * from t1;
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3 left join cte on t3.b = cte.b
|
|
set t3.a = cte.a+1 where cte.b in (select b from cte2) and d = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 10 test.t3.b,test.t3.a 1
|
|
1 PRIMARY t2 index NULL t2_ix1 9 NULL 13 Using where; Using index; FirstMatch(<derived2>)
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3 left join cte on t3.b = cte.b
|
|
set t3.a = cte.a+1 where cte.b in (select b from cte2) and d = t3.a;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
3 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3, cte, cte2 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t3.a 1 Using where
|
|
1 PRIMARY <derived3> ref key0 key0 5 cte.b 1
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
3 DERIVED t2 ALL NULL NULL NULL NULL 13 Using where
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update t3, cte, cte2 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
4 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key1 key1 5 test.t3.a 1 Using where
|
|
1 PRIMARY <derived3> ref key0 key0 5 cte.b 1
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
3 DERIVED t2 ALL NULL NULL NULL NULL 13 Using where
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
4 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
explain with cte as (select * from t1 where c < 5 group by a),
|
|
cte2 as (select * from t2 where b < 3 group by a)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key1 key1 5 test.t3.a 1 Using where
|
|
1 PRIMARY <derived3> ref key0 key0 5 cte.b 1
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort
|
|
3 DERIVED t2 ALL NULL NULL NULL NULL 13 Using where; Using temporary; Using filesort
|
|
with cte as (select * from t1 where c < 5 group by a),
|
|
cte2 as (select * from t2 where b < 3 group by a)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
4 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create view v1 as select * from t1;
|
|
explain with cte as (select * from v1 where c < 5 group by a),
|
|
cte2 as (select * from t2 where b < 3 group by a)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key1 key1 5 test.t3.a 1 Using where
|
|
1 PRIMARY <derived3> ref key0 key0 5 cte.b 1
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort
|
|
3 DERIVED t2 ALL NULL NULL NULL NULL 13 Using where; Using temporary; Using filesort
|
|
with cte as (select * from v1 where c < 5 group by a),
|
|
cte2 as (select * from t2 where b < 3 group by a)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
4 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
with T as (select * from t1) update T set T.a=3;
|
|
ERROR HY000: The target table T of the UPDATE is not updatable
|
|
with T as (select * from v1) update T set T.a=3;
|
|
ERROR HY000: The target table T of the UPDATE is not updatable
|
|
with T as (select * from t1) delete from T where a=3;
|
|
ERROR HY000: The target table T of the DELETE is not updatable
|
|
with T as (select * from v1) delete from T where a=3;
|
|
ERROR HY000: The target table T of the DELETE is not updatable
|
|
drop view v1;
|
|
create view v1 as select * from t1 where b IS NOT NULL limit 1;
|
|
explain with cte as (select * from v1 where c < 5 group by a),
|
|
cte2 as (select * from t2 where b < 3 group by a)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where d = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t3.a 1
|
|
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 13
|
|
2 DERIVED <derived4> ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
|
|
4 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
3 DERIVED t2 ALL NULL NULL NULL NULL 13 Using where; Using temporary; Using filesort
|
|
with cte as (select * from v1 where c < 5 group by a),
|
|
cte2 as (select * from t2 where b < 3 group by a)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where d = t3.a;
|
|
select * from t3;
|
|
a b description
|
|
3 1 iron
|
|
2 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop view v1;
|
|
CREATE FUNCTION f1 () RETURNS BLOB RETURN 1;
|
|
CREATE TABLE tf (f1 DATE);
|
|
INSERT INTO tf VALUES('2001-01-01');
|
|
explain with cte as (select f1 as a, f1() as b, 2 as d from tf),
|
|
cte2 as (select * from t2 where b < 3 group by a)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
|
|
1 PRIMARY <derived3> ref key0 key0 5 const 1 Using where
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
2 DERIVED tf system NULL NULL NULL NULL 1
|
|
3 DERIVED t2 ALL NULL NULL NULL NULL 13 Using where; Using temporary; Using filesort
|
|
with cte as (select f1 as a, f1() as b, 2 as d from tf),
|
|
cte2 as (select * from t2 where b < 3 group by a)
|
|
update cte, cte2, t3 set t3.a = cte.a+2 where cte.b = cte2.b and d = t3.a;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
2 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop function f1;
|
|
drop table tf;
|
|
# delete single table syntax
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
where t3.a = (select a from cte where b in (select b from cte2));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
4 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using where
|
|
4 SUBQUERY t2 index NULL t2_ix1 9 NULL 13 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join)
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
where t3.a = (select a from cte where b in (select b from cte2));
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
where t3.a = (select a from cte where b in (select b from cte2));
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
# delete multi table syntax
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 4 test.t3.a 1 Using where
|
|
1 PRIMARY t2 index NULL t2_ix1 9 NULL 13 Using where; Using index; FirstMatch(<derived2>)
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2);
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2);
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
# delete multi table syntax, multi table delete
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3, t4
|
|
using t3, t4, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2)
|
|
and t4.a = t3.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 4 test.t3.a 1 Using where
|
|
1 PRIMARY t2 index NULL t2_ix1 9 NULL 13 Using where; Using index; FirstMatch(<derived2>)
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 9 Using where
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3, t4
|
|
using t3, t4, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2)
|
|
and t4.a = t3.a;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
select * from t4;
|
|
a b c d amount
|
|
0 NULL 0 NULL 10
|
|
1 1 1 1 10
|
|
3 3 3 3 30
|
|
4 4 4 4 40
|
|
5 5 5 5 NULL
|
|
6 6 6 6 NULL
|
|
7 7 7 7 70
|
|
8 8 8 8 80
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3, t4
|
|
using t3, t4, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2)
|
|
and t4.a = t3.a;
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
2 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
# delete multi table syntax, multi table CTEs
|
|
explain with cte as (select t1.* from t1 left join t4 on t1.d = t4.d where t1.c < 5),
|
|
cte2 as (select t2.* from t2, t5 where t2.b < 3 and t2.name = t5.name limit 1)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 4 test.t3.a 8 Using where
|
|
1 PRIMARY <derived3> ref key0 key0 5 cte.b 1 FirstMatch(<derived2>)
|
|
3 DERIVED t2 ALL NULL NULL NULL NULL 13 Using where
|
|
3 DERIVED t5 ALL NULL NULL NULL NULL 13 Using where; Using join buffer (flat, BNL join)
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
2 DERIVED t4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join)
|
|
with cte as (select t1.* from t1 left join t4 on t1.d = t4.d where t1.c < 5),
|
|
cte2 as (select t2.* from t2, t5 where t2.b < 3 and t2.name = t5.name limit 1)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2);
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with cte as (select t1.* from t1 left join t4 on t1.d = t4.d where t1.c < 5),
|
|
cte2 as (select t2.* from t2, t5 where t2.b < 3 and t2.name = t5.name limit 1)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2);
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
prepare s from 'with cte as (select t1.* from t1 left join t4 on t1.d = t4.d where t1.c < 5),
|
|
cte2 as (select t2.* from t2, t5 where t2.b < 3 and t2.name = t5.name limit 1)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2)';
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop prepare s;
|
|
# delete multi table syntax, limit clause in delete
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2) limit 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 4 test.t3.a 1 Using where
|
|
1 PRIMARY t2 index NULL t2_ix1 9 NULL 13 Using where; Using index; FirstMatch(<derived2>)
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2) limit 1;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2) limit 1;
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
prepare s from 'with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
using t3, cte
|
|
where t3.a = cte.a and cte.b in (select b from cte2) limit 1';
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop prepare s;
|
|
explain with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
using t3, cte, cte2
|
|
where t3.a = cte.a and cte.b = cte2.b;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 4 test.t3.a 1 Using where
|
|
1 PRIMARY <derived3> ref key0 key0 5 cte.b 1
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 9 Using where
|
|
3 DERIVED t2 ALL NULL NULL NULL NULL 13 Using where
|
|
with cte as (select * from t1 where c < 5),
|
|
cte2 as (select * from t2 where b < 3)
|
|
delete from t3
|
|
using t3, cte, cte2
|
|
where t3.a = cte.a and cte.b = cte2.b;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
5 5 tin
|
|
6 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
# recursive CTE, test update on values 1 (NC), 2, 5, 6 from CTE
|
|
explain with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
)
|
|
update t3, cte_r
|
|
set t3.a = 1
|
|
where t3.b = c;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key1,distinct_key key1 5 test.t3.b 1
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 RECURSIVE UNION t1 index t1_ix1 t1_ix1 9 NULL 9 Using index
|
|
4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t1.a 1
|
|
NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL
|
|
with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
)
|
|
update t3, cte_r
|
|
set t3.a = 1
|
|
where t3.b = c;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
1 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
1 5 tin
|
|
1 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
)
|
|
update t3, cte_r
|
|
set t3.a = 1
|
|
where t3.b = c;
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
1 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
1 5 tin
|
|
1 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
prepare s from 'with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
)
|
|
update t3, cte_r
|
|
set t3.a = 1
|
|
where t3.b = c';
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
1 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
1 5 tin
|
|
1 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
1 1 iron
|
|
1 2 wood
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
1 5 tin
|
|
1 6 platinum
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop prepare s;
|
|
# recursive CTE, test delete, multi table syntax
|
|
explain with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
)
|
|
delete from t3
|
|
using t3, cte_r
|
|
where t3.b = c;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key1,distinct_key key1 5 test.t3.b 1
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 RECURSIVE UNION t1 index t1_ix1 t1_ix1 9 NULL 9 Using index
|
|
4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t1.a 1
|
|
NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL
|
|
with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
)
|
|
delete from t3
|
|
using t3, cte_r
|
|
where t3.b = c;
|
|
select * from t3;
|
|
a b description
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
)
|
|
delete from t3
|
|
using t3, cte_r
|
|
where t3.b = c;
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
prepare s from 'with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
)
|
|
delete from t3
|
|
using t3, cte_r
|
|
where t3.b = c';
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop prepare s;
|
|
# recursive CTE, test delete, single table syntax
|
|
explain with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
),
|
|
cte (a) as
|
|
(
|
|
select a from t2
|
|
)
|
|
delete from t3
|
|
where t3.b in (select c from cte_r, cte where cte.a = cte_r.a );
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where
|
|
1 PRIMARY <derived2> ref key1,distinct_key key1 5 test.t3.b 1 Using where
|
|
1 PRIMARY t2 ref t2_ix1 t2_ix1 4 cte_r.a 2 Using index; FirstMatch(t3)
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 RECURSIVE UNION t1 index t1_ix1 t1_ix1 9 NULL 9 Using index
|
|
4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t1.a 1
|
|
NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL
|
|
with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
),
|
|
cte (a) as
|
|
(
|
|
select a from t2
|
|
)
|
|
delete from t3
|
|
where t3.b in (select c from cte_r, cte where cte.a = cte_r.a );
|
|
select * from t3;
|
|
a b description
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
create procedure p() with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
),
|
|
cte (a) as
|
|
(
|
|
select a from t2
|
|
)
|
|
delete from t3
|
|
where t3.b in (select c from cte_r, cte where cte.a = cte_r.a );
|
|
call p();
|
|
drop procedure p;
|
|
select * from t3;
|
|
a b description
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
prepare s from 'with recursive cte_r(a, c) as
|
|
(
|
|
values (1,5)
|
|
union
|
|
values (2,6)
|
|
union
|
|
select t1.a, t1.b
|
|
from t1, cte_r
|
|
where t1.a = cte_r.a
|
|
),
|
|
cte (a) as
|
|
(
|
|
select a from t2
|
|
)
|
|
delete from t3
|
|
where t3.b in (select c from cte_r, cte where cte.a = cte_r.a )';
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
execute s;
|
|
select * from t3;
|
|
a b description
|
|
0 NULL gold
|
|
3 3 silver
|
|
4 4 lead
|
|
7 7 aluminium
|
|
truncate t3;
|
|
insert into t3 values (1, 1, 'iron'),(2,2,'wood'),(0,NULL, 'gold'),
|
|
(3, 3, 'silver'), (4, 4, 'lead'), (5, 5, 'tin'), (6, 6, 'platinum'),
|
|
(7, 7, 'aluminium');
|
|
drop prepare s;
|
|
drop table t1, t2, t3, t4, t5;
|
|
# End of 12.2 tests
|