diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/temp_table_savepoint.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/temp_table_savepoint.result | 242 |
1 files changed, 242 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/temp_table_savepoint.result b/mysql-test/suite/innodb/r/temp_table_savepoint.result new file mode 100644 index 00000000..44c8db04 --- /dev/null +++ b/mysql-test/suite/innodb/r/temp_table_savepoint.result @@ -0,0 +1,242 @@ +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| +connect con1,localhost,root,,; +call populate_tables();; +connect con2,localhost,root,,; +call populate_tables();; +connection con1; +select c1 from t5; +c1 +11 +12 +13 +14 +15 +16 +17 +18 +19 +20 +122 +124 +126 +128 +130 +132 +134 +136 +138 +140 +connection con2; +select c1 from t5; +c1 +11 +12 +13 +14 +15 +16 +17 +18 +19 +20 +122 +124 +126 +128 +130 +132 +134 +136 +138 +140 +connection con1; +set autocommit = 0; +insert into t5 values (20,1,'a','a','a','a','a',now(),100.55); +ERROR 23000: Duplicate entry '20' for key 'PRIMARY' +insert ignore into t5 values (20,1,'a','a','a','a','a',now(),100.55); +Warnings: +Warning 1062 Duplicate entry '20' for key 'PRIMARY' +insert into t5 values (1,1,'a','a','a','a','a',now(),100.55), +(20,1,'a','a','a','a','a',now(),100.55); +ERROR 23000: Duplicate entry '20' for key 'PRIMARY' +set autocommit = 1; +select c1,c2 from t5 where c1 in (20,1); +c1 c2 +20 20 +replace into t5 values (20,1,'a','a','a','a','a',now(),100.55); +select c1,c2,c3,c4,c5,c6,c7,c9 from t5 where c1 = 20; +c1 c2 c3 c4 c5 c6 c7 c9 +20 1 a a a a a 100.550 +update ignore t5 set c1 = 20 where c1 = 140 ; +select count(*) from t5 where c1 = 140; +count(*) +1 +select * into outfile "VARDIR/tmp/t5.outfile" from t5; +create temporary table temp_1 engine = innodb as select * from t5 where 1=2; +select count(*) from temp_1; +count(*) +0 +load data infile 'VARDIR/tmp/t5.outfile' into table temp_1; +select count(*) from temp_1; +count(*) +20 +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; +c1 c2 c3 c4 c5 c6 c7 c9 c10 c11 +-1 -1 a a a a a 100.550 99 test +select count(*) from temp_1 where c10 = 99 and c11 like 'test'; +count(*) +21 +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; +c1 c2 c3 c4 c5 c6 c7 c9 c10 c11 +-2 -2 a a a a a 100.550 99 test +disconnect con1; +disconnect con2; +connection default; +drop procedure populate_tables; +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; +EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var; +EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static; +set @var = @var - 1; +EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var; +EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static; +set @var = @var - 1; +EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var; +EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static; +set @var = @var - 1; +EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var; +EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static; +set @var = @var - 1; +EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var; +EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static; +set @var = @var - 1; +select c1,left(c3,15) from prep_1 order by c1 ; +c1 left(c3,15) +1 tc3_1 tc3_1 tc +2 tc3_2 tc3_2 tc +3 tc3_3 tc3_3 tc +4 tc3_4 tc3_4 tc +5 tc3_5 tc3_5 tc +5 tc3_5 tc3_5 tc +5 tc3_5 tc3_5 tc +5 tc3_5 tc3_5 tc +5 tc3_5 tc3_5 tc +5 tc3_5 tc3_5 tc +select count(*) from prep_1; +count(*) +10 +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 ; +c1 left(c3,15) +3 tc3_1 tc3_1 tc +4 tc3_2 tc3_2 tc +5 tc3_3 tc3_3 tc +6 tc3_4 tc3_4 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +select count(*) from prep_1; +count(*) +10 +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 ; +c1 left(c3,15) +5 tc3_3 tc3_3 tc +6 tc3_4 tc3_4 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +7 tc3_5 tc3_5 tc +select count(*) from prep_1; +count(*) +8 +drop prepare stm; +drop prepare stm_1; +drop prepare stm_2; |