362 lines
12 KiB
Plaintext
362 lines
12 KiB
Plaintext
# TestLocalTemporaryTableInsert
|
|
drop table if exists tmp1;
|
|
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
|
|
insert into tmp1 (u, v) values(11, 101);
|
|
insert into tmp1 (u, v) values(12, 102);
|
|
insert into tmp1 values(3, 13, 102);
|
|
select * from tmp1 where id=1;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where id=3;
|
|
select * from tmp1 where id=99;
|
|
-- error 1062
|
|
insert into tmp1 values(1, 999, 9999);
|
|
select * from tmp1 where id=1;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where id=3;
|
|
select * from tmp1 where id=99;
|
|
-- error 1062
|
|
insert into tmp1 values(99, 11, 999);
|
|
select * from tmp1 where id=1;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where id=3;
|
|
select * from tmp1 where id=99;
|
|
begin;
|
|
-- error 1062
|
|
insert into tmp1 values(1, 999, 9999);
|
|
select * from tmp1 where id=1;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where id=3;
|
|
select * from tmp1 where id=99;
|
|
-- error 1062
|
|
insert into tmp1 values(99, 11, 9999);
|
|
select * from tmp1 where id=1;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where id=3;
|
|
select * from tmp1 where id=99;
|
|
insert into tmp1 values(4, 14, 104);
|
|
select * from tmp1 where id=4;
|
|
-- error 1062
|
|
insert into tmp1 values(4, 999, 9999);
|
|
-- error 1062
|
|
insert into tmp1 values(99, 14, 9999);
|
|
select * from tmp1 where id=1;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where id=3;
|
|
select * from tmp1 where id=99;
|
|
commit;
|
|
select * from tmp1 where id=1;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where id=3;
|
|
select * from tmp1 where id=99;
|
|
select * from tmp1 where id=4;
|
|
begin;
|
|
insert into tmp1 values(5, 15, 105);
|
|
select * from tmp1 where id=5;
|
|
rollback;
|
|
select * from tmp1 where id=5;
|
|
|
|
# TestTemporaryTableSize
|
|
drop table if exists t, tl;
|
|
create global temporary table t (c1 int, c2 mediumtext) on commit delete rows;
|
|
create temporary table tl (c1 int, c2 mediumtext);
|
|
select @@global.tidb_tmp_table_max_size;
|
|
--enable_warnings
|
|
set @@global.tidb_tmp_table_max_size = 123;
|
|
--disable_warnings
|
|
set @@session.tidb_tmp_table_max_size = 2097152;
|
|
set @@session.tidb_tmp_table_max_size = 1048576;
|
|
connect (conn1, localhost, root,,session__temporary_table);
|
|
connection conn1;
|
|
select @@global.tidb_tmp_table_max_size;
|
|
disconnect conn1;
|
|
begin;
|
|
insert into t values (1, repeat('x', 512*1024));
|
|
insert into t values (1, repeat('x', 512*1024));
|
|
-- error 1114
|
|
insert into t values (1, repeat('x', 512*1024));
|
|
rollback;
|
|
begin;
|
|
insert into tl values (1, repeat('x', 512*1024));
|
|
insert into tl values (1, repeat('x', 512*1024));
|
|
-- error 1114
|
|
insert into tl values (1, repeat('x', 512*1024));
|
|
rollback;
|
|
insert into tl values (1, repeat('x', 512*1024));
|
|
begin;
|
|
insert into tl values (1, repeat('x', 512*1024));
|
|
-- error 1114
|
|
insert into tl values (1, repeat('x', 512*1024));
|
|
rollback;
|
|
|
|
# TestGlobalTemporaryTable
|
|
drop table if exists g_tmp;
|
|
create global temporary table g_tmp (a int primary key, b int, c int, index i_b(b)) on commit delete rows;
|
|
begin;
|
|
insert into g_tmp values (3, 3, 3);
|
|
insert into g_tmp values (4, 7, 9);
|
|
select * from g_tmp;
|
|
select b from g_tmp where b > 3;
|
|
select c from g_tmp where b = 3;
|
|
select * from g_tmp where a = 3;
|
|
select * from g_tmp where a in (2,3,4);
|
|
commit;
|
|
select * from g_tmp;
|
|
|
|
# TestLocalTemporaryTableInsertOnDuplicateKeyUpdate
|
|
drop table if exists tmp1;
|
|
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
|
|
insert into tmp1 values(1, 11, 101);
|
|
insert into tmp1 values(2, 12, 102);
|
|
--enable_warnings
|
|
insert ignore into tmp1 values(1, 100, 1000) on duplicate key update u=12;
|
|
--disable_warnings
|
|
select * from tmp1 where id=1;
|
|
--enable_warnings
|
|
insert into tmp1 values(2, 100, 1000) on duplicate key update v=202;
|
|
--disable_warnings
|
|
select * from tmp1 where id=2;
|
|
--enable_warnings
|
|
insert into tmp1 values(3, 13, 103) on duplicate key update v=203;
|
|
--disable_warnings
|
|
select * from tmp1 where id=3;
|
|
begin;
|
|
--enable_warnings
|
|
insert ignore into tmp1 values(1, 100, 1000) on duplicate key update u=12;
|
|
--disable_warnings
|
|
select * from tmp1 where id=1;
|
|
--enable_warnings
|
|
insert into tmp1 values(2, 100, 1000) on duplicate key update v=302;
|
|
--disable_warnings
|
|
select * from tmp1 where id=2;
|
|
--enable_warnings
|
|
insert into tmp1 values(4, 14, 104) on duplicate key update v=204;
|
|
--disable_warnings
|
|
select * from tmp1 where id=4;
|
|
rollback;
|
|
select * from tmp1;
|
|
begin;
|
|
--enable_warnings
|
|
insert ignore into tmp1 values(1, 100, 1000) on duplicate key update u=12;
|
|
--disable_warnings
|
|
insert into tmp1 values(2, 100, 1000) on duplicate key update v=302;
|
|
insert into tmp1 values(4, 14, 104) on duplicate key update v=204;
|
|
commit;
|
|
select * from tmp1;
|
|
|
|
# TestLocalTemporaryTableReplace
|
|
drop table if exists tmp1;
|
|
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
|
|
insert into tmp1 values(1, 11, 101);
|
|
insert into tmp1 values(2, 12, 102);
|
|
insert into tmp1 values(3, 13, 103);
|
|
# out of transaction
|
|
replace into tmp1 values(1, 12, 1000);
|
|
select * from tmp1;
|
|
replace into tmp1 values(4, 14, 104);
|
|
select * from tmp1 where id=4;
|
|
# in transaction and rollback
|
|
begin;
|
|
replace into tmp1 values(1, 13, 999);
|
|
select * from tmp1;
|
|
replace into tmp1 values(5, 15, 105);
|
|
select * from tmp1 where id=5;
|
|
rollback;
|
|
# in transaction and commit
|
|
select * from tmp1;
|
|
begin;
|
|
replace into tmp1 values(1, 13, 999);
|
|
replace into tmp1 values(5, 15, 105);
|
|
commit;
|
|
select * from tmp1;
|
|
|
|
# TestLocalTemporaryTablePointGet
|
|
drop table if exists tmp1;
|
|
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
|
|
insert into tmp1 values(1, 11, 101);
|
|
insert into tmp1 values(2, 12, 102);
|
|
insert into tmp1 values(4, 14, 104);
|
|
# check point get out transaction
|
|
select * from tmp1 where id=1;
|
|
select * from tmp1 where u=11;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where u=12;
|
|
# check point get in transaction
|
|
begin;
|
|
select * from tmp1 where id=1;
|
|
select * from tmp1 where u=11;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where u=12;
|
|
insert into tmp1 values(3, 13, 103);
|
|
select * from tmp1 where id=3;
|
|
select * from tmp1 where u=13;
|
|
update tmp1 set v=999 where id=2;
|
|
select * from tmp1 where id=2;
|
|
delete from tmp1 where id=4;
|
|
select * from tmp1 where id=4;
|
|
select * from tmp1 where u=14;
|
|
commit;
|
|
# check point get after transaction
|
|
select * from tmp1 where id=3;
|
|
select * from tmp1 where u=13;
|
|
select * from tmp1 where id=2;
|
|
select * from tmp1 where id=4;
|
|
select * from tmp1 where u=14;
|
|
|
|
# TestLocalTemporaryTableBatchPointGet
|
|
drop table if exists tmp1;
|
|
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
|
|
insert into tmp1 values(1, 11, 101);
|
|
insert into tmp1 values(2, 12, 102);
|
|
insert into tmp1 values(3, 13, 103);
|
|
insert into tmp1 values(4, 14, 104);
|
|
# check point get out transaction
|
|
select * from tmp1 where id in (1, 3);
|
|
select * from tmp1 where u in (11, 13);
|
|
select * from tmp1 where id in (1, 3, 5);
|
|
select * from tmp1 where u in (11, 13, 15);
|
|
# check point get in transaction
|
|
begin;
|
|
select * from tmp1 where id in (1, 3);
|
|
select * from tmp1 where u in (11, 13);
|
|
select * from tmp1 where id in (1, 3, 5);
|
|
select * from tmp1 where u in (11, 13, 15);
|
|
insert into tmp1 values(6, 16, 106);
|
|
select * from tmp1 where id in (1, 6);
|
|
select * from tmp1 where u in (11, 16);
|
|
update tmp1 set v=999 where id=3;
|
|
select * from tmp1 where id in (1, 3);
|
|
select * from tmp1 where u in (11, 13);
|
|
delete from tmp1 where id=4;
|
|
select * from tmp1 where id in (1, 4);
|
|
select * from tmp1 where u in (11, 14);
|
|
commit;
|
|
# check point get after transaction
|
|
select * from tmp1 where id in (1, 3, 6);
|
|
select * from tmp1 where u in (11, 13, 16);
|
|
select * from tmp1 where id in (1, 4);
|
|
select * from tmp1 where u in (11, 14);
|
|
|
|
# TestLocalTemporaryTableScan
|
|
drop table if exists tmp1;
|
|
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
|
|
insert into tmp1 values(1, 101, 1001), (3, 113, 1003), (5, 105, 1005), (7, 117, 1007), (9, 109, 1009),(10, 110, 1010), (12, 112, 1012), (14, 114, 1014), (16, 116, 1016), (18, 118, 1018);
|
|
select * from tmp1 where id>3 order by id;
|
|
--enable_warnings
|
|
select /*+ use_index(tmp1, u) */ * from tmp1 where u>101 order by u;
|
|
select /*+ use_index(tmp1, u) */ id,u from tmp1 where u>101 order by id;
|
|
select /*+ use_index_merge(tmp1, primary, u) */ * from tmp1 where id>5 or u>110 order by u;
|
|
--disable_warnings
|
|
begin;
|
|
select * from tmp1 where id>3 order by id;
|
|
--enable_warnings
|
|
select /*+ use_index(tmp1, u) */ * from tmp1 where u>101 order by u;
|
|
select /*+ use_index(tmp1, u) */ id,u from tmp1 where u>101 order by id;
|
|
select /*+ use_index_merge(tmp1, primary, u) */ * from tmp1 where id>5 or u>110 order by u;
|
|
--disable_warnings
|
|
insert into tmp1 values(2, 100, 1002);
|
|
insert into tmp1 values(4, 104, 1004);
|
|
insert into tmp1 values(11, 111, 1011);
|
|
update tmp1 set v=9999 where id=7;
|
|
update tmp1 set u=132 where id=12;
|
|
delete from tmp1 where id=16;
|
|
rollback;
|
|
select * from tmp1 where id>3 order by id;
|
|
--enable_warnings
|
|
select /*+ use_index(tmp1, u) */ * from tmp1 where u>101 order by u;
|
|
select /*+ use_index(tmp1, u) */ id,u from tmp1 where u>101 order by id;
|
|
select /*+ use_index_merge(tmp1, primary, u) */ * from tmp1 where id>5 or u>110 order by u;
|
|
--disable_warnings
|
|
begin;
|
|
insert into tmp1 values(2, 100, 1002);
|
|
insert into tmp1 values(4, 104, 1004);
|
|
insert into tmp1 values(11, 111, 1011);
|
|
update tmp1 set v=9999 where id=7;
|
|
update tmp1 set u=132 where id=12;
|
|
delete from tmp1 where id=16;
|
|
select * from tmp1 where id>3 order by id;
|
|
--enable_warnings
|
|
select /*+ use_index(tmp1, u) */ * from tmp1 where u>101 order by u;
|
|
select /*+ use_index(tmp1, u) */ id,u from tmp1 where u>101 order by id;
|
|
select /*+ use_index_merge(tmp1, primary, u) */ * from tmp1 where id>5 or u>110 order by u;
|
|
--disable_warnings
|
|
commit;
|
|
select * from tmp1 where id>3 order by id;
|
|
--enable_warnings
|
|
select /*+ use_index(tmp1, u) */ * from tmp1 where u>101 order by u;
|
|
select /*+ use_index(tmp1, u) */ id,u from tmp1 where u>101 order by id;
|
|
select /*+ use_index_merge(tmp1, primary, u) */ * from tmp1 where id>5 or u>110 order by u;
|
|
--disable_warnings
|
|
|
|
# TestSameNameObjectWithLocalTemporaryTable
|
|
drop table if exists t1;
|
|
drop sequence if exists s1;
|
|
drop view if exists v1;
|
|
create table t1 (a int);
|
|
show create table t1;
|
|
create view v1 as select 1;
|
|
show create view v1;
|
|
show create table v1;
|
|
create sequence s1;
|
|
show create sequence s1;
|
|
show create table s1;
|
|
create temporary table t1 (ct1 int);
|
|
show create table t1;
|
|
create temporary table v1 (cv1 int);
|
|
show create view v1;
|
|
show create table v1;
|
|
create temporary table s1 (cs1 int);
|
|
show create sequence s1;
|
|
show create table s1;
|
|
drop view v1;
|
|
-- error 1146
|
|
show create view v1;
|
|
show create table v1;
|
|
drop sequence s1;
|
|
-- error 1146
|
|
show create sequence s1;
|
|
show create table s1;
|
|
drop sequence if exists s1;
|
|
drop view if exists v1;
|
|
drop table if exists t1;
|
|
|
|
# TestLocalTemporaryTableInsertIgnore
|
|
drop table if exists tmp1;
|
|
create temporary table tmp1 (id int primary key auto_increment, u int unique, v int);
|
|
insert into tmp1 values(1, 11, 101);
|
|
insert into tmp1 values(2, 12, 102);
|
|
# test outside transaction
|
|
--enable_warnings
|
|
insert ignore into tmp1 values(1, 100, 1000);
|
|
--disable_warnings
|
|
select * from tmp1 where id=1;
|
|
--enable_warnings
|
|
insert ignore into tmp1 values(5, 15, 105);
|
|
--disable_warnings
|
|
select * from tmp1 where id=5;
|
|
# test in transaction and rollback
|
|
begin;
|
|
--enable_warnings
|
|
insert ignore into tmp1 values(1, 100, 1000);
|
|
--disable_warnings
|
|
select * from tmp1 where id=1;
|
|
--enable_warnings
|
|
insert ignore into tmp1 values(3, 13, 103);
|
|
--disable_warnings
|
|
select * from tmp1 where id=3;
|
|
--enable_warnings
|
|
insert ignore into tmp1 values(3, 100, 1000);
|
|
--disable_warnings
|
|
select * from tmp1 where id=3;
|
|
rollback;
|
|
select * from tmp1;
|
|
# test commit
|
|
begin;
|
|
--enable_warnings
|
|
insert ignore into tmp1 values(1, 100, 1000);
|
|
insert ignore into tmp1 values(3, 13, 103);
|
|
insert ignore into tmp1 values(3, 100, 1000);
|
|
--disable_warnings
|
|
commit;
|
|
select * from tmp1;
|
|
|