diff options
Diffstat (limited to 'mysql-test/main/long_unique_update.test')
| -rw-r--r-- | mysql-test/main/long_unique_update.test | 141 |
1 files changed, 141 insertions, 0 deletions
diff --git a/mysql-test/main/long_unique_update.test b/mysql-test/main/long_unique_update.test new file mode 100644 index 00000000..98c3aaef --- /dev/null +++ b/mysql-test/main/long_unique_update.test @@ -0,0 +1,141 @@ +# +# MDEV-371 Unique indexes for blobs +# +--echo #structure of tests; +--echo #1 test of table containing single unique blob column; +--echo #2 test of table containing another unique int/ varchar etc column; +--echo #3 test of table containing multiple unique blob column like unique(a),unique(b); +--echo #4 test of table containing multiple multiple unique blob column like unique(a,b...),unique(c,d....); +--echo #structure of each test; +--echo #test if update works; +--echo #test update for duplicate entry; +--echo #test update for no change keys; +--echo #test update for ignore ; + +--echo #test 1 +create table t1 (a blob unique); +query_vertical show keys from t1; +insert into t1 values(1),(2),(3),(4),(5); +select * from t1; +update t1 set a=11 where a=5; +update t1 set a=a+20 where a=1; +select * from t1; +--error ER_DUP_ENTRY +update t1 set a=3 where a=2; +--error ER_DUP_ENTRY +update t1 set a=4 where a=3; +--echo #no change in blob key +update t1 set a=3 where a=3; +update t1 set a=2 where a=2; +select* from t1; +--echo #IGNORE; +update ignore t1 set a=3 where a=2; +update ignore t1 set a=4 where a=3; +select * from t1; +drop table t1; + +--echo #test 2; +create table t1 (a int primary key, b blob unique , c int unique ); +show keys from t1; +insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7); +select * from t1 limit 3; +update t1 set b=34 where a=1; +update t1 set b=a+c+b+34 where b=2; +update t1 set b=a+10+b where c=3; +select * from t1; +truncate table t1; +insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7); +--error ER_DUP_ENTRY +update t1 set b=4 where a=3; +--error ER_DUP_ENTRY +update t1 set b=a+1 where b=3; +--error ER_DUP_ENTRY +update t1 set b=a+1 where c=3; +--echo #no change in blob key +update t1 set b=3 where a=3; +update t1 set b=2 where b=2; +update t1 set b=5 where c=5; +select* from t1; +--echo #IGNORE; +update ignore t1 set b=3 where a=2; +update ignore t1 set b=4 where b=3; +update ignore t1 set b=5 where c=3; +select * from t1; +drop table t1; + +--echo #test 3; +create table t1 (a blob unique, b blob unique , c blob unique); +show keys from t1; +insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7); +select * from t1 limit 3; +update t1 set b=34 where a=1; +update t1 set b=a+c+b+34 where b=2; +update t1 set b=a+10+b where c=3; +select * from t1; +truncate table t1; +insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7); +--error ER_DUP_ENTRY +update t1 set b=4 where a=3; +--error ER_DUP_ENTRY +update t1 set b=a+1 where b=3; +--error ER_DUP_ENTRY +update t1 set b=a+1 where c=3; +--echo #no change in blob key +update t1 set b=3 where a=3; +update t1 set b=2 where b=2; +update t1 set b=5 where c=5; +select* from t1; +--echo #IGNORE; +update ignore t1 set b=3 where a=2; +update ignore t1 set b=4 where b=3; +update ignore t1 set b=5 where c=3; +update ignore t1 set b=b+3 where a>1 or b>1 or c>1; +select * from t1; +update ignore t1 set b=b+5 where a>1 and b<5 and c<a+b; +select * from t1; +drop table t1; + +--echo #test 4 ultimate test; +create table t1 (a int primary key , b int, c blob , d blob , e varchar(2000), f int , g text, +unique (b,c), unique (b,f),unique(e,g),unique(a,b,c,d,e,f,g)); +desc t1; +show create table t1; +show keys from t1; +insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4), +(5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9); +select * from t1 limit 3; +--echo #key b_c +--error ER_DUP_ENTRY +update t1 set b=2 ,c=2 where a=1; +update t1 set b=b+34, c=c+34 where e=1 and g=1 ; +update t1 set b=35, c=35 where e=1 and g=1 ; +--error ER_DUP_ENTRY +update t1 set b=b+1, c=c+1 where a>0; +update ignore t1 set b=b+1, c=c+1 where a>0; +select * from t1 ; +truncate table t1; +insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4), +(5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9); +--echo #key b_f no hash key +--error ER_DUP_ENTRY +update t1 set b=2 , f=2 where a=1; +update t1 set b=b+33, f=f+33 where e=1 and g=1; +update t1 set b=34, f=34 where e=1 and g=1 ; +--error ER_DUP_ENTRY +update t1 set b=b+1, f=f+1 where a>0; +update ignore t1 set b=b+1, f=f+1 where a>0; +select * from t1 ; +truncate table t1; +insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4), +(5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9); +--echo #key e_g +--error ER_DUP_ENTRY +update t1 set e=2 , g=2 where a=1; +update t1 set e=e+34, g=g+34 where a=1; +update t1 set e=34, g=34 where e=1 and g=1 ; +select * from t1 where a=1; +--error ER_DUP_ENTRY +update t1 set e=e+1, g=g+1 where a>0; +update ignore t1 set e=e+1, g=g+1 where a>0; +select * from t1 ; +drop table t1; |