MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
varchar.inc
1 # Initialise
2 --disable_warnings
3 drop table if exists t1,t2,t3;
4 --enable_warnings
5 
6 disable_query_log;
7 select "--- Testing varchar ---";
8 enable_query_log;
9 
10 #
11 # Simple basic test that endspace is saved
12 #
13 
14 create table t1 (v varchar(10), c char(10), t text);
15 insert into t1 values('+ ', '+ ', '+ ');
16 set @a=repeat(' ',20);
17 insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
18 select concat('*',v,'*',c,'*',t,'*') from t1;
19 
20 # Check how columns are copied
21 show create table t1;
22 create table t2 like t1;
23 show create table t2;
24 create table t3 select * from t1;
25 show create table t3;
26 alter table t1 modify c varchar(10);
27 show create table t1;
28 alter table t1 modify v char(10);
29 show create table t1;
30 alter table t1 modify t varchar(10);
31 show create table t1;
32 select concat('*',v,'*',c,'*',t,'*') from t1;
33 drop table t1,t2,t3;
34 
35 #
36 # Testing of keys
37 #
38 create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))) stats_persistent=0;
39 show create table t1;
40 disable_query_log;
41 let $1=10;
42 while ($1)
43 {
44  let $2=27;
45  eval set @space=repeat(' ',10-$1);
46  while ($2)
47  {
48  eval set @char=char(ascii('a')+$2-1);
49  insert into t1 values(concat(@char,@space),concat(@char,@space),concat(@char,@space));
50  dec $2;
51  }
52  dec $1;
53 }
54 enable_query_log;
55 select count(*) from t1;
56 insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
57 select count(*) from t1 where v='a';
58 select count(*) from t1 where c='a';
59 select count(*) from t1 where t='a';
60 select count(*) from t1 where v='a ';
61 select count(*) from t1 where c='a ';
62 select count(*) from t1 where t='a ';
63 select count(*) from t1 where v between 'a' and 'a ';
64 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
65 select count(*) from t1 where v like 'a%';
66 select count(*) from t1 where c like 'a%';
67 select count(*) from t1 where t like 'a%';
68 select count(*) from t1 where v like 'a %';
69 # Test results differ for BDB, see comments in bdb.test
70 # and they are also different from MySAM test results.
71 --replace_column 9 #
72 explain select count(*) from t1 where v='a ';
73 --replace_column 9 #
74 explain select count(*) from t1 where c='a ';
75 --replace_column 9 #
76 explain select count(*) from t1 where t='a ';
77 --replace_column 9 #
78 explain select count(*) from t1 where v like 'a%';
79 --replace_column 9 #
80 explain select count(*) from t1 where v between 'a' and 'a ';
81 --replace_column 9 #
82 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
83 
84 --error ER_DUP_ENTRY
85 alter table t1 add unique(v);
86 alter table t1 add key(v);
87 select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
88 --replace_column 6 # 9 #
89 explain select * from t1 where v='a';
90 
91 # GROUP BY
92 
93 select v,count(*) from t1 group by v limit 10;
94 select v,count(t) from t1 group by v limit 10;
95 select v,count(c) from t1 group by v limit 10;
96 select sql_big_result v,count(t) from t1 group by v limit 10;
97 select sql_big_result v,count(c) from t1 group by v limit 10;
98 select c,count(*) from t1 group by c limit 10;
99 select c,count(t) from t1 group by c limit 10;
100 select sql_big_result c,count(t) from t1 group by c limit 10;
101 select t,count(*) from t1 group by t limit 10;
102 select t,count(t) from t1 group by t limit 10;
103 select sql_big_result t,count(t) from t1 group by t limit 10;
104 
105 #
106 # Test varchar > 255 bytes
107 #
108 
109 alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);
110 show create table t1;
111 select count(*) from t1 where v='a';
112 select count(*) from t1 where v='a ';
113 select count(*) from t1 where v between 'a' and 'a ';
114 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
115 select count(*) from t1 where v like 'a%';
116 select count(*) from t1 where v like 'a %';
117 --replace_column 9 #
118 explain select count(*) from t1 where v='a ';
119 --replace_column 9 #
120 explain select count(*) from t1 where v like 'a%';
121 --replace_column 9 #
122 explain select count(*) from t1 where v between 'a' and 'a ';
123 --replace_column 9 #
124 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
125 --replace_column 9 #
126 explain select * from t1 where v='a';
127 
128 # GROUP BY
129 
130 select v,count(*) from t1 group by v limit 10;
131 select v,count(t) from t1 group by v limit 10;
132 select sql_big_result v,count(t) from t1 group by v limit 10;
133 
134 #
135 # Test varchar > 255 bytes, key < 255
136 #
137 
138 alter table t1 drop key v, add key v (v(30));
139 show create table t1;
140 select count(*) from t1 where v='a';
141 select count(*) from t1 where v='a ';
142 select count(*) from t1 where v between 'a' and 'a ';
143 select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
144 select count(*) from t1 where v like 'a%';
145 select count(*) from t1 where v like 'a %';
146 --replace_column 9 #
147 explain select count(*) from t1 where v='a ';
148 --replace_column 9 #
149 explain select count(*) from t1 where v like 'a%';
150 --replace_column 9 #
151 explain select count(*) from t1 where v between 'a' and 'a ';
152 --replace_column 9 #
153 explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';
154 --replace_column 9 #
155 explain select * from t1 where v='a';
156 
157 # GROUP BY
158 
159 select v,count(*) from t1 group by v limit 10;
160 select v,count(t) from t1 group by v limit 10;
161 select sql_big_result v,count(t) from t1 group by v limit 10;
162 
163 #
164 # Test varchar > 512 (special case for GROUP BY becasue of
165 # CONVERT_IF_BIGGER_TO_BLOB define)
166 #
167 
168 alter table t1 modify v varchar(600), drop key v, add key v (v);
169 show create table t1;
170 select v,count(*) from t1 group by v limit 10;
171 select v,count(t) from t1 group by v limit 10;
172 select sql_big_result v,count(t) from t1 group by v limit 10;
173 
174 drop table t1;
175 
176 #
177 # Test unique keys
178 #
179 
180 create table t1 (a char(10), unique (a));
181 insert into t1 values ('a ');
182 --error ER_DUP_ENTRY
183 insert into t1 values ('a ');
184 
185 alter table t1 modify a varchar(10);
186 --error ER_DUP_ENTRY
187 insert into t1 values ('a '),('a '),('a '),('a ');
188 --error ER_DUP_ENTRY
189 insert into t1 values ('a ');
190 --error ER_DUP_ENTRY
191 insert into t1 values ('a ');
192 --error ER_DUP_ENTRY
193 insert into t1 values ('a ');
194 update t1 set a='a ' where a like 'a%';
195 select concat(a,'.') from t1;
196 update t1 set a='abc ' where a like 'a ';
197 select concat(a,'.') from t1;
198 update t1 set a='a ' where a like 'a %';
199 select concat(a,'.') from t1;
200 update t1 set a='a ' where a like 'a ';
201 select concat(a,'.') from t1;
202 drop table t1;
203 
204 #
205 # test show create table
206 #
207 
208 create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));
209 show create table t1;
210 drop table t1;
211 create table t1 (v char(10) character set utf8);
212 show create table t1;
213 drop table t1;
214 
215 create table t1 (v varchar(10), c char(10)) row_format=fixed;
216 show create table t1;
217 insert into t1 values('a','a'),('a ','a ');
218 select concat('*',v,'*',c,'*') from t1;
219 drop table t1;
220 
221 #
222 # Test long varchars
223 #
224 
225 create table t1 (v varchar(65530), key(v(10)));
226 insert into t1 values(repeat('a',65530));
227 select length(v) from t1 where v=repeat('a',65530);
228 drop table t1;
229 
230 #
231 # Bug #9489: problem with hash indexes
232 # Bug #10802: Index is not used if table using BDB engine on HP-UX
233 #
234 
235 create table t1(a int, b varchar(12), key ba(b, a));
236 insert into t1 values (1, 'A'), (20, NULL);
237 explain select * from t1 where a=20 and b is null;
238 select * from t1 where a=20 and b is null;
239 drop table t1;