1 files changed, 212 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/temp_table_savepoint.test b/mysql-test/suite/innodb/t/temp_table_savepoint.test
new file mode 100644
index 00000000..ffac771d
--- /dev/null
+++ b/mysql-test/suite/innodb/t/temp_table_savepoint.test
@@ -0,0 +1,212 @@
+--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
+eval select * into outfile "$MYSQLTEST_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;
+
+--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;