mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
1e0a72a18b
Created tests for "delete" based on update_use_source.test For the update_use_source.test tests, data recovery in the table has been changed from a rollback transaction to a complete delete and re-insert of the data with optimize table. Cases are now being checked on three engines. Added tests for update/delete with LooseScan and DuplicateWeedout optimization strategies Added tests for engine MEMORY on delete and update Added tests for multi-update with JSON_TABLE Added tests for multi-update and multi-delete for engine Connect
188 lines
4.8 KiB
PHP
188 lines
4.8 KiB
PHP
# Include to test update with same table as source and target
|
|
|
|
create table t1 (old_c1 integer,
|
|
old_c2 integer,
|
|
c1 integer,
|
|
c2 integer,
|
|
c3 integer);
|
|
|
|
create view v1 as select * from t1 where c2=2;
|
|
delimiter /;
|
|
create trigger trg_t1 before update on t1 for each row
|
|
begin
|
|
set new.old_c1=old.c1;
|
|
set new.old_c2=old.c2;
|
|
end;
|
|
/
|
|
delimiter ;/
|
|
|
|
insert into t1(c1,c2,c3)
|
|
values (1,1,1), (1,2,2), (1,3,3),
|
|
(2,1,4), (2,2,5), (2,3,6),
|
|
(2,4,7), (2,5,8);
|
|
insert into t1 select NULL, NULL, c1+10,c2,c3+10 from t1;
|
|
insert into t1 select NULL, NULL, c1+20,c2+1,c3+20 from t1;
|
|
analyze table t1 persistent for all;
|
|
|
|
create table tmp as select * from t1;
|
|
|
|
--echo #######################################
|
|
--echo # Test without any index #
|
|
--echo #######################################
|
|
--source include/update_use_source_cases.inc
|
|
|
|
--echo #######################################
|
|
--echo # Test with an index #
|
|
--echo #######################################
|
|
create index t1_c2 on t1 (c2,c1);
|
|
analyze table t1;
|
|
--source include/update_use_source_cases.inc
|
|
|
|
--echo #######################################
|
|
--echo # Test with a primary key #
|
|
--echo #######################################
|
|
drop index t1_c2 on t1;
|
|
alter table t1 add primary key (c3);
|
|
analyze table t1;
|
|
--source include/update_use_source_cases.inc
|
|
|
|
--echo # Update with error "Subquery returns more than 1 row"
|
|
--error ER_SUBQUERY_NO_1_ROW
|
|
update t1 set c2=(select c2 from t1);
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
|
|
--echo # Update with error "Subquery returns more than 1 row"
|
|
--echo # and order by
|
|
--error ER_SUBQUERY_NO_1_ROW
|
|
update t1 set c2=(select c2 from t1) order by c3;
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
|
|
-- echo # Duplicate value on update a primary key
|
|
--error ER_DUP_ENTRY
|
|
|
|
update t1 set c3=0
|
|
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
-- echo # Duplicate value on update a primary key with ignore
|
|
--enable_info ONCE
|
|
update ignore t1 set c3=0
|
|
where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
-- echo # Duplicate value on update a primary key and limit
|
|
--error ER_DUP_ENTRY
|
|
update t1 set c3=0
|
|
where exists (select 'X' from t1 a where a.c2 = t1.c2)
|
|
and c2 >= 3 limit 2;
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
-- echo # Duplicate value on update a primary key with ignore
|
|
-- echo # and limit
|
|
--enable_info ONCE
|
|
update ignore t1 set c3=0
|
|
where exists (select 'X' from t1 a where a.c2 = t1.c2)
|
|
and c2 >= 3 limit 2;
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo # Update no rows found
|
|
--enable_info ONCE
|
|
update t1 set c1=10
|
|
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1 + 10);
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo # Update no rows changed
|
|
drop trigger trg_t1;
|
|
--enable_info ONCE
|
|
update t1 set c1=c1
|
|
where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1);
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Check call of after trigger
|
|
--echo #
|
|
|
|
delimiter /;
|
|
create or replace trigger trg_t2 after update on t1 for each row
|
|
begin
|
|
declare msg varchar(100);
|
|
if (new.c3 = 5) then
|
|
set msg=concat('in after update trigger on ',new.c3);
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
|
|
end if;
|
|
end;
|
|
/
|
|
delimiter ;/
|
|
--error 1644
|
|
|
|
update t1 set c1=2
|
|
where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Check update with order by and after trigger
|
|
--echo #
|
|
|
|
--error 1644
|
|
update t1 set c1=2
|
|
where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1)
|
|
order by t1.c2, t1.c1;
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
drop view v1;
|
|
|
|
--echo #
|
|
--echo # Check update on view with check option
|
|
--echo #
|
|
|
|
create view v1 as select * from t1 where c2=2 with check option;
|
|
|
|
-- error 1369
|
|
update v1 set c2=3 where c1=1;
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
-- error 1369
|
|
update v1 set c2=(select max(c3) from v1) where c1=1;
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
update v1 set c2=(select min(va.c3) from v1 va), c1=0 where c1=1;
|
|
--sorted_result
|
|
select c1,c2,c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
drop table tmp;
|
|
drop view v1;
|
|
drop table t1;
|