mirror of
https://github.com/MariaDB/server.git
synced 2025-01-15 19:42:28 +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
183 lines
5 KiB
PHP
183 lines
5 KiB
PHP
--echo #
|
|
--echo # Update with value from subquery on the same table
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $q = update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
|
|
eval explain select * from t1;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update with EXISTS subquery over the updated table
|
|
--echo # in WHERE + possibly sargable condition
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $c = c1 <2
|
|
and exists (select 'X' from t1 a where a.c1 = t1.c1);
|
|
let $q = update t1 set c1=10 where $c;
|
|
eval explain select * from t1 where $c;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update with EXISTS subquery over the updated table
|
|
--echo # in WHERE + non-sargable condition
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
|
|
let $q = update t1 set c1=c1+10 where $c;
|
|
eval explain select * from t1 where $c;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update with order by
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $c = exists (select 'X' from t1 a where a.c2 = t1.c2)
|
|
and c2 >= 3 order by c2;
|
|
let $q = update t1 set c1=c1+10 where $c;
|
|
eval explain select * from t1 where $c;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update with a reference to view in subquery
|
|
--echo # in settable value
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $q = update t1 set c1=c1 +(select max(a.c2) from v1 a
|
|
where a.c1 = t1.c1);
|
|
eval explain select * from t1;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update view
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $q = update v1 set c1=c1 + (select max(a.c2) from t1 a
|
|
where a.c1 = v1.c1) +10 where c3 > 3;
|
|
eval explain select * from v1;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update view with reference to the same view in subquery
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $c = c1 <2
|
|
and exists (select 'X' from v1 a where a.c1 = v1.c1);
|
|
let $q = update v1 set c1=c1 + 1 where $c;
|
|
eval explain select * from v1 where $c;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update view with EXISTS and reference to the same view in subquery
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $c = c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
|
|
let $q = update v1
|
|
set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
|
|
where $c;
|
|
eval explain select * from v1 where $c;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update with IN predicand over the updated table in WHERE
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $c = c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
|
|
let $q = update t1 set c3=c3+110 where $c;
|
|
eval explain select * from t1 where $c;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
--sorted_result
|
|
select c3 from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update with a limit
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $q = update t1
|
|
set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
|
|
eval explain select * from t1 limit 2;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|
|
|
|
--echo #
|
|
--echo # Update with a limit and an order by
|
|
--echo #
|
|
|
|
analyze table t1 persistent for all;
|
|
let $q=update t1
|
|
set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
|
|
order by c3 desc limit 2;
|
|
eval explain select * from t1 order by c3 desc limit 2;
|
|
eval explain $q;
|
|
--enable_info ONCE
|
|
eval $q;
|
|
select concat(old_c1,'->',c1),c3,
|
|
case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
|
|
truncate table t1;
|
|
insert into t1 select * from tmp;
|