1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
|
--source include/have_tokudb.inc
#
#test update multiple
#
#
SET DEFAULT_STORAGE_ENGINE = 'tokudb';
--disable_warnings
DROP TABLE IF EXISTS foo;
--enable_warnings
set session tokudb_prelock_empty=0;
#
# test updates on table that is composed of more than one dictionary
#
create table foo (a int, b int, c int, d int, primary key (a), key (b), key (c) clustering=yes);
insert into foo values (1,100,1000,1),(2,20,200,2),(3,300,30,3),(4,4,4,4);
#
# cases:
# primary key changes,
# have clustering key and it changes, or val changes,
# secondary key changes,
# only val of primary dictionary changes
#
select * from foo;
explain select b,a from foo;
select b,a from foo;
select * from foo where c > 0;
--echo #
--echo # only val of primary dictionary and clustering dictionary changes
--echo #
update foo set d=d+100;
select * from foo;
explain select b,a from foo;
select b,a from foo;
select * from foo where c > 0;
--echo #
--echo # secondary key changes
--echo #
update foo set b=b+1;
select * from foo;
explain select b,a from foo;
select b,a from foo;
select * from foo where c > 0;
--echo #
--echo # clustering key changes
--echo #
update foo set c=c*10;
select * from foo;
explain select b,a from foo;
select b,a from foo;
select * from foo where c > 0;
drop table foo;
--echo #
--echo # test updates on single dictionary
--echo # Two cases: pk changes, pk does not change
--echo #
create table foo (a int, b int, primary key (a));
insert into foo values (1,10),(2,20),(3,30);
select * from foo;
update foo set b=b*10;
select * from foo;
update foo set a=a+10;
select * From foo;
drop table foo;
--echo #
--echo # test pk uniqueness check during updates
--echo # Two cases: have one dict, have more than one dict
--echo #
create table foo (a int, b int, c int, primary key (a));
insert into foo values (1,10,100),(2,20,200),(3,30,300);
--error ER_DUP_ENTRY
update foo set a=3 where a=1;
select * from foo;
alter table foo add key (c) clustering=yes;
--error ER_DUP_ENTRY
update foo set a=3 where a=1;
drop table foo;
--echo #
--echo # test secondary key uniqueness
--echo #
create table foo (a int, b int, c int, primary key (a), unique key (b));
insert into foo values (1,10,100),(2,20,200),(3,30,300);
--error ER_DUP_ENTRY
update foo set b=20 where b=10;
update foo set c=c*100;
select * from foo;
# Final cleanup.
DROP TABLE foo;
|