mirror of
https://github.com/MariaDB/server.git
synced 2025-02-05 13:22:17 +01:00
0a5e4a0191
Updated tests: cases with bugs or which cannot be run with the cursor-protocol were excluded with "--disable_cursor_protocol"/"--enable_cursor_protocol" Fix for v.10.5
217 lines
7.3 KiB
Text
217 lines
7.3 KiB
Text
--source include/have_innodb.inc
|
|
--source include/innodb_row_format.inc
|
|
|
|
####################################################################
|
|
# TC to test temp-table DML optimization changes for correctness #
|
|
# Sceanrio covered in single testcase : #
|
|
# - Tables with row format(redundant,dynamic,compact) #
|
|
# - Table with primary,composite,prefix,secondary index #
|
|
# - Insert/delete/update with transactioons #
|
|
# - Transaction with commit,rollback,savepoint statements #
|
|
# - Concurrency by execution of two clients creating tables with #
|
|
# same names #
|
|
# - Inserting data using #
|
|
# - Insert into .. , Load data infile..,insert ignore #
|
|
# - Insert into .. on duplicate update #
|
|
# - Check basic delete and update [ignore] #
|
|
# - Check constraints like duplicate key,default value #
|
|
# - Alter add column , add primary key #
|
|
# - with prepare and execute statement #
|
|
####################################################################
|
|
|
|
# Create procedure to perform
|
|
# 1. Create temp table with row types , index , sufficent data types
|
|
# 2. Perform DML with transaction
|
|
delimiter |;
|
|
create procedure populate_tables()
|
|
begin
|
|
declare n int default 20;
|
|
declare inner_loop int default 100;
|
|
|
|
create temporary table t5(c1 int not null,
|
|
c2 int not null,
|
|
c3 char(255) not null,
|
|
c4 text(6000) not null,
|
|
c5 blob(6000) not null,
|
|
c6 varchar(2000) not null,
|
|
c7 varchar(2000) not null,
|
|
c8 datetime,
|
|
c9 decimal(6,3),
|
|
primary key (c1),
|
|
index (c3,c4(50),c5(50)),
|
|
index (c2))
|
|
engine=innodb;
|
|
|
|
create temporary table t6 ( a int ) engine = innodb;
|
|
insert into t6 values (50),(100),(150),(190);
|
|
|
|
while (n > 0) do
|
|
start transaction;
|
|
insert ignore into t5 values(n,n,repeat(concat(' tc3_',n),30),
|
|
repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
|
|
repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
|
|
now(),(100.55+n));
|
|
|
|
if (n > 10) then
|
|
commit;
|
|
else
|
|
delete from t5 where c1 > 10 ;
|
|
|
|
rollback;
|
|
start transaction;
|
|
update t5 set c1 = c1 + 1000 where c1 > 10;
|
|
rollback;
|
|
end if;
|
|
|
|
start transaction;
|
|
insert ignore into t5 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
|
|
repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
|
|
repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
|
|
now(),(100.55+n+inner_loop));
|
|
|
|
delete from t5 where c1 between 100 and 110;
|
|
|
|
update t5 set c1 = c1+1 where c1>110;
|
|
|
|
savepoint a;
|
|
|
|
insert ignore into t5 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
|
|
repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
|
|
repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
|
|
now(),(100.55+n+inner_loop));
|
|
savepoint b;
|
|
|
|
insert into t5 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
|
|
repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
|
|
repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
|
|
now(),(100.55+n+inner_loop));
|
|
savepoint c;
|
|
rollback to b;
|
|
rollback to a;
|
|
commit;
|
|
commit;
|
|
rollback;
|
|
set n = n - 1;
|
|
end while;
|
|
end|
|
|
delimiter ;|
|
|
|
|
# Create two client for concurrent execution
|
|
connect (con1,localhost,root,,);
|
|
--send call populate_tables();
|
|
|
|
connect (con2,localhost,root,,);
|
|
--send call populate_tables();
|
|
|
|
connection con1;
|
|
--reap
|
|
# 20 rows expected
|
|
select c1 from t5;
|
|
|
|
connection con2;
|
|
--reap
|
|
# 20 rows expected
|
|
select c1 from t5;
|
|
|
|
connection con1;
|
|
|
|
set autocommit = 0;
|
|
# Check duplicate key constraint + insert ignore
|
|
--error ER_DUP_ENTRY
|
|
insert into t5 values (20,1,'a','a','a','a','a',now(),100.55);
|
|
insert ignore into t5 values (20,1,'a','a','a','a','a',now(),100.55);
|
|
|
|
# check rollback due to duplicate value in second record of insert
|
|
--error ER_DUP_ENTRY
|
|
insert into t5 values (1,1,'a','a','a','a','a',now(),100.55),
|
|
(20,1,'a','a','a','a','a',now(),100.55);
|
|
|
|
set autocommit = 1;
|
|
|
|
select c1,c2 from t5 where c1 in (20,1);
|
|
|
|
#replace statement
|
|
replace into t5 values (20,1,'a','a','a','a','a',now(),100.55);
|
|
# verify row is replaced from (20,20) to (20,1)
|
|
select c1,c2,c3,c4,c5,c6,c7,c9 from t5 where c1 = 20;
|
|
|
|
# Update ignore. statement is ignored as 20 value exists
|
|
update ignore t5 set c1 = 20 where c1 = 140 ;
|
|
# see record 140 is present as last update ignored
|
|
select count(*) from t5 where c1 = 140;
|
|
|
|
--replace_result $MYSQLTEST_VARDIR VARDIR
|
|
--disable_cursor_protocol
|
|
--disable_ps2_protocol
|
|
eval select * into outfile "$MYSQLTEST_VARDIR/tmp/t5.outfile" from t5;
|
|
--enable_ps2_protocol
|
|
--enable_cursor_protocol
|
|
|
|
|
|
create temporary table temp_1 engine = innodb as select * from t5 where 1=2;
|
|
select count(*) from temp_1;
|
|
|
|
--replace_result $MYSQLTEST_VARDIR VARDIR
|
|
eval load data infile '$MYSQLTEST_VARDIR/tmp/t5.outfile' into table temp_1;
|
|
select count(*) from temp_1;
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/t5.outfile
|
|
|
|
# Alter table to add column and primary key
|
|
alter table temp_1 add column c10 int default 99 ,
|
|
add column c11 varchar(100) default 'test';
|
|
alter table temp_1 add primary key (c1);
|
|
insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55);
|
|
select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
|
|
select count(*) from temp_1 where c10 = 99 and c11 like 'test';
|
|
# insert on duplicate key update
|
|
insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55)
|
|
on duplicate key update c1=-2,c2=-2;
|
|
select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
|
|
|
|
disconnect con1;
|
|
disconnect con2;
|
|
connection default;
|
|
drop procedure populate_tables;
|
|
|
|
# case 2 - with prepare and execute
|
|
let $prep_loop= 5;
|
|
create temporary table prep_1(c1 int not null,
|
|
c2 int not null,
|
|
c3 char(255) not null,
|
|
c4 text(6000) not null,
|
|
c5 blob(6000) not null,
|
|
c6 varchar(2000) not null,
|
|
c7 varchar(2000) not null,
|
|
c8 datetime,
|
|
c9 decimal(6,3),
|
|
index (c3,c4(50),c5(50)),
|
|
index (c2))
|
|
engine=innodb;
|
|
PREPARE stm FROM "insert into prep_1 values(?,?,repeat(concat(' tc3_',?),30),repeat(concat(' tc4_',?),800),repeat(concat(' tc_',?),800),repeat(concat(' tc6_',?),245),repeat(concat(' tc7_',?),245),now(),(100.55+?))";
|
|
set @var = 5;
|
|
set @var_static = 5;
|
|
while ($prep_loop>0)
|
|
{
|
|
eval EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
|
|
eval EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
|
|
dec $prep_loop;
|
|
set @var = @var - 1;
|
|
}
|
|
select c1,left(c3,15) from prep_1 order by c1 ;
|
|
select count(*) from prep_1;
|
|
|
|
PREPARE stm_1 FROM "UPDATE prep_1 SET c1 = c1 + 1";
|
|
EXECUTE stm_1;
|
|
EXECUTE stm_1;
|
|
select c1,left(c3,15) from prep_1 order by c1 ;
|
|
select count(*) from prep_1;
|
|
|
|
PREPARE stm_2 FROM "DELETE FROM prep_1 ORDER BY c1 LIMIT 1";
|
|
EXECUTE stm_2;
|
|
EXECUTE stm_2;
|
|
select c1,left(c3,15) from prep_1 order by c1 ;
|
|
select count(*) from prep_1;
|
|
|
|
drop prepare stm;
|
|
drop prepare stm_1;
|
|
drop prepare stm_2;
|