summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/innodb/r/temp_table.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/innodb/r/temp_table.result')
-rw-r--r--mysql-test/suite/innodb/r/temp_table.result597
1 files changed, 597 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/temp_table.result b/mysql-test/suite/innodb/r/temp_table.result
new file mode 100644
index 00000000..dea47787
--- /dev/null
+++ b/mysql-test/suite/innodb/r/temp_table.result
@@ -0,0 +1,597 @@
+create temporary table t1
+(keyc int, c1 char(100), c2 char(100),
+primary key(keyc), index sec_index(c1)
+) engine = innodb;
+create temporary table t2
+(keyc int, c1 char(100), c2 char(100),
+primary key(keyc), index sec_index(c1)
+) engine = innodb;
+create procedure populate_t1()
+begin
+declare i int default 1;
+while (i <= 200) do
+insert into t1 values (i, 'a', 'b');
+set i = i + 1;
+end while;
+end|
+create procedure populate_t1_small()
+begin
+declare i int default 1;
+while (i <= 20) do
+insert into t1 values (i, 'c', 'd');
+set i = i + 1;
+end while;
+end|
+create procedure populate_t1_small2()
+begin
+declare i int default 30;
+while (i <= 50) do
+insert into t1 values (i, 'e', 'f');
+set i = i + 1;
+end while;
+end|
+begin;
+select count(*) from t1;
+count(*)
+0
+call populate_t1();
+select count(*) from t1;
+count(*)
+200
+select * from t1 limit 10;
+keyc c1 c2
+1 a b
+2 a b
+3 a b
+4 a b
+5 a b
+6 a b
+7 a b
+8 a b
+9 a b
+10 a b
+rollback;
+select count(*) from t1;
+count(*)
+0
+begin;
+call populate_t1();
+select count(*) from t1;
+count(*)
+200
+commit;
+select count(*) from t1;
+count(*)
+200
+truncate table t1;
+select count(*) from t1;
+count(*)
+0
+call populate_t1_small();
+select count(*) from t1;
+count(*)
+20
+rollback;
+select count(*) from t1;
+count(*)
+20
+truncate table t1;
+call populate_t1();
+select count(*) from t1;
+count(*)
+200
+delete from t1 where keyc <= 60;
+select count(*) from t1;
+count(*)
+140
+call populate_t1_small();
+select count(*) from t1;
+count(*)
+160
+select * from t1 limit 10;
+keyc c1 c2
+1 c d
+2 c d
+3 c d
+4 c d
+5 c d
+6 c d
+7 c d
+8 c d
+9 c d
+10 c d
+begin;
+call populate_t1_small2();
+select count(*) from t1;
+count(*)
+181
+select * from t1 where keyc > 30 limit 10;
+keyc c1 c2
+31 e f
+32 e f
+33 e f
+34 e f
+35 e f
+36 e f
+37 e f
+38 e f
+39 e f
+40 e f
+rollback;
+select count(*) from t1;
+count(*)
+160
+select * from t1 where keyc > 30 limit 10;
+keyc c1 c2
+61 a b
+62 a b
+63 a b
+64 a b
+65 a b
+66 a b
+67 a b
+68 a b
+69 a b
+70 a b
+update t1 set keyc = keyc + 2000;
+select * from t1 limit 10;
+keyc c1 c2
+2001 c d
+2002 c d
+2003 c d
+2004 c d
+2005 c d
+2006 c d
+2007 c d
+2008 c d
+2009 c d
+2010 c d
+rollback;
+begin;
+update t1 set keyc = keyc + 2000;
+select * from t1 limit 10;
+keyc c1 c2
+4001 c d
+4002 c d
+4003 c d
+4004 c d
+4005 c d
+4006 c d
+4007 c d
+4008 c d
+4009 c d
+4010 c d
+rollback;
+select * from t1 limit 10;
+keyc c1 c2
+2001 c d
+2002 c d
+2003 c d
+2004 c d
+2005 c d
+2006 c d
+2007 c d
+2008 c d
+2009 c d
+2010 c d
+commit;
+select * from t1 limit 10;
+keyc c1 c2
+2001 c d
+2002 c d
+2003 c d
+2004 c d
+2005 c d
+2006 c d
+2007 c d
+2008 c d
+2009 c d
+2010 c d
+insert into t2 select * from t1 where keyc < 2101;
+select count(*) from t2;
+count(*)
+60
+drop procedure populate_t1;
+drop procedure populate_t1_small;
+drop procedure populate_t1_small2;
+drop temporary table t1,t2;
+create temporary table t1
+(keyc int, c1 char(100), c2 char(100),
+primary key(keyc), index sec_index(c1)
+) engine = innodb key_block_size = 4;
+ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
+create temporary table t1
+(keyc int, c1 char(100), c2 char(100),
+primary key(keyc), index sec_index(c1)
+) engine = innodb;
+set innodb_strict_mode=off;
+create temporary table t2
+(keyc int, c1 char(100), c2 char(100),
+primary key(keyc), index sec_index(c1)
+) engine = innodb key_block_size = 8;
+set innodb_strict_mode=on;
+create procedure populate_t1()
+begin
+declare i int default 1;
+while (i <= 200) do
+insert into t1 values (i, 'a', 'b');
+set i = i + 1;
+end while;
+end|
+create procedure populate_t1_small()
+begin
+declare i int default 1;
+while (i <= 20) do
+insert into t1 values (i, 'c', 'd');
+set i = i + 1;
+end while;
+end|
+create procedure populate_t1_small2()
+begin
+declare i int default 30;
+while (i <= 50) do
+insert into t1 values (i, 'e', 'f');
+set i = i + 1;
+end while;
+end|
+begin;
+select count(*) from t1;
+count(*)
+0
+call populate_t1();
+select count(*) from t1;
+count(*)
+200
+select * from t1 limit 10;
+keyc c1 c2
+1 a b
+2 a b
+3 a b
+4 a b
+5 a b
+6 a b
+7 a b
+8 a b
+9 a b
+10 a b
+rollback;
+select count(*) from t1;
+count(*)
+0
+begin;
+call populate_t1();
+select count(*) from t1;
+count(*)
+200
+commit;
+select count(*) from t1;
+count(*)
+200
+truncate table t1;
+select count(*) from t1;
+count(*)
+0
+call populate_t1_small();
+select count(*) from t1;
+count(*)
+20
+rollback;
+select count(*) from t1;
+count(*)
+20
+truncate table t1;
+call populate_t1();
+select count(*) from t1;
+count(*)
+200
+delete from t1 where keyc <= 60;
+select count(*) from t1;
+count(*)
+140
+call populate_t1_small();
+select count(*) from t1;
+count(*)
+160
+select * from t1 limit 10;
+keyc c1 c2
+1 c d
+2 c d
+3 c d
+4 c d
+5 c d
+6 c d
+7 c d
+8 c d
+9 c d
+10 c d
+begin;
+call populate_t1_small2();
+select count(*) from t1;
+count(*)
+181
+select * from t1 where keyc > 30 limit 10;
+keyc c1 c2
+31 e f
+32 e f
+33 e f
+34 e f
+35 e f
+36 e f
+37 e f
+38 e f
+39 e f
+40 e f
+rollback;
+select count(*) from t1;
+count(*)
+160
+select * from t1 where keyc > 30 limit 10;
+keyc c1 c2
+61 a b
+62 a b
+63 a b
+64 a b
+65 a b
+66 a b
+67 a b
+68 a b
+69 a b
+70 a b
+update t1 set keyc = keyc + 2000;
+select * from t1 limit 10;
+keyc c1 c2
+2001 c d
+2002 c d
+2003 c d
+2004 c d
+2005 c d
+2006 c d
+2007 c d
+2008 c d
+2009 c d
+2010 c d
+rollback;
+begin;
+update t1 set keyc = keyc + 2000;
+select * from t1 limit 10;
+keyc c1 c2
+4001 c d
+4002 c d
+4003 c d
+4004 c d
+4005 c d
+4006 c d
+4007 c d
+4008 c d
+4009 c d
+4010 c d
+rollback;
+select * from t1 limit 10;
+keyc c1 c2
+2001 c d
+2002 c d
+2003 c d
+2004 c d
+2005 c d
+2006 c d
+2007 c d
+2008 c d
+2009 c d
+2010 c d
+commit;
+select * from t1 limit 10;
+keyc c1 c2
+2001 c d
+2002 c d
+2003 c d
+2004 c d
+2005 c d
+2006 c d
+2007 c d
+2008 c d
+2009 c d
+2010 c d
+insert into t2 select * from t1 where keyc < 2101;
+select count(*) from t2;
+count(*)
+60
+drop procedure populate_t1;
+drop procedure populate_t1_small;
+drop procedure populate_t1_small2;
+drop temporary table t1, t2;
+create temporary table t1
+(keyc int, c1 char(100), c2 char(100),
+primary key(keyc), index sec_index(c1)
+) engine = innodb;
+create temporary table t2
+(keyc int, c1 char(100), c2 char(100),
+primary key(keyc), index sec_index(c1)
+) engine = innodb;
+create procedure populate_t1()
+begin
+declare i int default 1;
+while (i <= 200) do
+insert into t1 values (i, 'a', 'b');
+set i = i + 1;
+end while;
+end|
+create procedure populate_t1_small()
+begin
+declare i int default 1;
+while (i <= 20) do
+insert into t1 values (i, 'c', 'd');
+set i = i + 1;
+end while;
+end|
+create procedure populate_t1_small2()
+begin
+declare i int default 30;
+while (i <= 50) do
+insert into t1 values (i, 'e', 'f');
+set i = i + 1;
+end while;
+end|
+begin;
+select count(*) from t1;
+count(*)
+0
+call populate_t1();
+select count(*) from t1;
+count(*)
+200
+select * from t1 limit 10;
+keyc c1 c2
+1 a b
+2 a b
+3 a b
+4 a b
+5 a b
+6 a b
+7 a b
+8 a b
+9 a b
+10 a b
+rollback;
+select count(*) from t1;
+count(*)
+0
+begin;
+call populate_t1();
+select count(*) from t1;
+count(*)
+200
+commit;
+select count(*) from t1;
+count(*)
+200
+truncate table t1;
+select count(*) from t1;
+count(*)
+0
+call populate_t1_small();
+select count(*) from t1;
+count(*)
+20
+rollback;
+select count(*) from t1;
+count(*)
+20
+truncate table t1;
+call populate_t1();
+select count(*) from t1;
+count(*)
+200
+delete from t1 where keyc <= 60;
+select count(*) from t1;
+count(*)
+140
+call populate_t1_small();
+select count(*) from t1;
+count(*)
+160
+select * from t1 limit 10;
+keyc c1 c2
+1 c d
+2 c d
+3 c d
+4 c d
+5 c d
+6 c d
+7 c d
+8 c d
+9 c d
+10 c d
+begin;
+call populate_t1_small2();
+select count(*) from t1;
+count(*)
+181
+select * from t1 where keyc > 30 limit 10;
+keyc c1 c2
+31 e f
+32 e f
+33 e f
+34 e f
+35 e f
+36 e f
+37 e f
+38 e f
+39 e f
+40 e f
+rollback;
+select count(*) from t1;
+count(*)
+160
+select * from t1 where keyc > 30 limit 10;
+keyc c1 c2
+61 a b
+62 a b
+63 a b
+64 a b
+65 a b
+66 a b
+67 a b
+68 a b
+69 a b
+70 a b
+update t1 set keyc = keyc + 2000;
+select * from t1 limit 10;
+keyc c1 c2
+2001 c d
+2002 c d
+2003 c d
+2004 c d
+2005 c d
+2006 c d
+2007 c d
+2008 c d
+2009 c d
+2010 c d
+rollback;
+begin;
+update t1 set keyc = keyc + 2000;
+select * from t1 limit 10;
+keyc c1 c2
+4001 c d
+4002 c d
+4003 c d
+4004 c d
+4005 c d
+4006 c d
+4007 c d
+4008 c d
+4009 c d
+4010 c d
+rollback;
+select * from t1 limit 10;
+keyc c1 c2
+2001 c d
+2002 c d
+2003 c d
+2004 c d
+2005 c d
+2006 c d
+2007 c d
+2008 c d
+2009 c d
+2010 c d
+commit;
+select * from t1 limit 10;
+keyc c1 c2
+2001 c d
+2002 c d
+2003 c d
+2004 c d
+2005 c d
+2006 c d
+2007 c d
+2008 c d
+2009 c d
+2010 c d
+insert into t2 select * from t1 where keyc < 2101;
+select count(*) from t2;
+count(*)
+60
+drop procedure populate_t1;
+drop procedure populate_t1_small;
+drop procedure populate_t1_small2;
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-18 13:32:47 +0000