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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
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;
|
