1
# This test can't be run with running server (--extern) as this uses
2
# load_file() on a file in the tree.
9
drop table if exists t1,t2,t3,t4,t5,t6,t7;
14
# Check syntax for creating BLOB/TEXT
17
CREATE TABLE t1 (a blob, b text, c blob, d text, e text);
19
# PS doesn't give errors on prepare yet
20
CREATE TABLE t2 (a varchar(255), b blob, c blob);
21
CREATE TABLE t4 (c varchar(16383) not null);
23
create table t3 (a int, b int);
24
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
26
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
28
drop table t1,t2,t3,t4;
31
# Check errors with blob
34
--error ER_BLOB_CANT_HAVE_DEFAULT
35
CREATE TABLE t1 (a blob default "hello");
36
CREATE TABLE t2 (a varchar(256));
38
--error ER_TOO_BIG_FIELDLENGTH
39
CREATE TABLE t1 (a varchar(70000) default "hello");
40
--error ER_BLOB_CANT_HAVE_DEFAULT
41
CREATE TABLE t2 (a blob default "hello");
43
# Safety to be able to continue with other tests if above fails
45
drop table if exists t1,t2;
49
# test of full join with blob
52
create table t1 (nr int not null auto_increment,b blob,str char(10), primary key (nr));
53
insert into t1 values (null,"a","A");
54
insert into t1 values (null,"bbb","BBB");
55
insert into t1 values (null,"ccc","CCC");
56
select last_insert_id();
58
select * from t1 CROSS JOIN t1 as t2;
63
# Test of changing TEXT column
66
create table t1 (a text);
67
insert into t1 values ('where');
68
update t1 set a='Where';
73
# test of blob, text, and char
75
create table t1 (t text,c varchar(10),b blob, d blob);
76
insert into t1 values (NULL,NULL,NULL,NULL);
77
insert into t1 values ("","","","");
78
insert into t1 values ("hello","hello","hello","hello");
79
insert into t1 values ("HELLO","HELLO","HELLO","HELLO");
80
insert into t1 values ("HELLO MY","HELLO MY","HELLO MY","HELLO MY");
81
insert into t1 values ("a","a","a","a");
82
insert into t1 values (1,1,1,1);
83
insert into t1 values (NULL,NULL,NULL,NULL);
84
update t1 set c="",b=null where c="1";
86
select t from t1 where t like "hello";
87
select c from t1 where c like "hello";
88
select b from t1 where b like "hello";
89
select d from t1 where d like "hello";
90
select c from t1 having c like "hello";
91
select d from t1 having d like "hello";
92
select t from t1 where t like "%HELLO%";
93
select c from t1 where c like "%HELLO%";
94
select b from t1 where b like "%HELLO%";
95
select d from t1 where d like "%HELLO%";
96
select c from t1 having c like "%HELLO%";
97
select d from t1 having d like "%HELLO%";
98
select d from t1 having d like "%HE%LLO%";
99
select t from t1 order by t;
100
select c from t1 order by c;
101
select b from t1 order by b;
102
select d from t1 order by d;
103
select distinct t from t1;
104
select distinct b from t1;
105
select distinct t from t1 order by t;
106
select distinct b from t1 order by b;
107
select t from t1 group by t;
108
select b from t1 group by b;
109
select distinct t from t1;
110
select distinct b from t1;
111
select distinct t from t1 order by t;
112
select distinct b from t1 order by b;
113
select distinct c from t1;
114
select distinct d from t1;
115
select distinct c from t1 order by c;
116
select distinct d from t1 order by d;
117
select c from t1 group by c;
118
select d from t1 group by d;
119
select distinct * from t1;
120
select t,count(*) from t1 group by t;
121
select b,count(*) from t1 group by b;
122
select c,count(*) from t1 group by c;
123
select d,count(*) from t1 group by d;
126
--error ER_TOO_LONG_KEY
127
create table t1 (a text, unique (a(2100))); # should give an error
128
create table t1 (a text, key (a(2100))); # key is auto-truncated
129
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
130
show create table t1;
134
# Test of join with blobs and min
138
t1_id bigint NOT NULL auto_increment,
139
_field_72 varchar(128) DEFAULT '' NOT NULL,
140
_field_95 varchar(32),
141
_field_115 int DEFAULT '0' NOT NULL,
142
_field_122 int DEFAULT '0' NOT NULL,
146
UNIQUE _field_72 (_field_72),
147
KEY _field_115 (_field_115),
148
KEY _field_122 (_field_122)
152
INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
153
INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
154
INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
158
seq_0_id bigint DEFAULT '0' NOT NULL,
159
seq_1_id bigint DEFAULT '0' NOT NULL,
160
PRIMARY KEY (seq_0_id,seq_1_id)
164
INSERT INTO t2 VALUES (1,1);
165
INSERT INTO t2 VALUES (2,1);
166
INSERT INTO t2 VALUES (2,2);
169
t3_id bigint NOT NULL auto_increment,
170
_field_131 varchar(128),
171
_field_133 int DEFAULT '0' NOT NULL,
176
_field_142 int DEFAULT '0' NOT NULL,
177
_field_145 int DEFAULT '0' NOT NULL,
178
_field_148 int DEFAULT '0' NOT NULL,
180
KEY _field_133 (_field_133),
181
KEY _field_135 (_field_135),
182
KEY _field_139 (_field_139),
183
KEY _field_142 (_field_142),
184
KEY _field_145 (_field_145),
185
KEY _field_148 (_field_148)
189
INSERT INTO t3 VALUES (1,'test job 1',0,NULL,0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
190
INSERT INTO t3 VALUES (2,'test job 2',0,NULL,0,'1999-02-26 21:08:04','',0,0,0);
194
seq_0_id bigint DEFAULT '0' NOT NULL,
195
seq_1_id bigint DEFAULT '0' NOT NULL,
196
PRIMARY KEY (seq_0_id,seq_1_id)
200
INSERT INTO t4 VALUES (1,1);
201
INSERT INTO t4 VALUES (2,1);
204
t5_id bigint NOT NULL auto_increment,
206
_field_156 varchar(128) DEFAULT '' NOT NULL,
207
_field_157 varchar(128) DEFAULT '' NOT NULL,
208
_field_158 varchar(128) DEFAULT '' NOT NULL,
209
_field_159 varchar(128) DEFAULT '' NOT NULL,
210
_field_160 varchar(128) DEFAULT '' NOT NULL,
211
_field_161 varchar(128) DEFAULT '' NOT NULL,
213
KEY _field_156 (_field_156),
214
KEY _field_157 (_field_157),
215
KEY _field_158 (_field_158),
216
KEY _field_159 (_field_159),
217
KEY _field_160 (_field_160),
218
KEY _field_161 (_field_161)
222
INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
223
INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
226
seq_0_id bigint DEFAULT '0' NOT NULL,
227
seq_1_id bigint DEFAULT '0' NOT NULL,
228
PRIMARY KEY (seq_0_id,seq_1_id)
231
INSERT INTO t6 VALUES (1,1);
232
INSERT INTO t6 VALUES (1,2);
233
INSERT INTO t6 VALUES (2,2);
236
t7_id bigint NOT NULL auto_increment,
238
_field_165 varchar(32),
239
_field_166 int DEFAULT '0' NOT NULL,
241
KEY _field_166 (_field_166)
245
INSERT INTO t7 VALUES (1,0,'High',1);
246
INSERT INTO t7 VALUES (2,0,'Medium',2);
247
INSERT INTO t7 VALUES (3,0,'Low',3);
249
select replace(t3._field_140, "\r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "\r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;
251
drop table t1,t2,t3,t4,t5,t6,t7;
254
# Test of reverse with empty blob
257
create table t1 (a blob);
258
insert into t1 values ("empty"),("");
259
select a,reverse(a) from t1;
263
# Test of BLOB:s with NULL keys.
266
create table t1 (a blob, key (a(10)));
267
insert into t1 values ("bye"),("hello"),("hello"),("hello word");
268
select * from t1 where a like "hello%";
272
# Test of found bug in group on text key
276
f1 int DEFAULT '0' NOT NULL,
277
f2 varchar(16) DEFAULT '' NOT NULL,
279
KEY index_name (f1,f2,f5(16))
281
INSERT INTO t1 VALUES (0,'traktor','1111111111111');
282
INSERT INTO t1 VALUES (1,'traktor','1111111111111111111111111');
283
select count(*) from t1 where f2='traktor';
287
# Test of found bug when blob is first key part
290
create table t1 (foobar tinyblob not null, boggle int not null, key (foobar(32), boggle));
291
insert into t1 values ('fish', 10),('bear', 20);
292
select foobar, boggle from t1 where foobar = 'fish';
293
select foobar, boggle from t1 where foobar = 'fish' and boggle = 10;
297
# Test blob's with end space (Bug #1651)
298
# This is a bit changed since we now have true varchar
301
#FIXME: The following test needs to be fixed for Drizzle
302
#create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20)));
303
#--error ER_DUP_ENTRY
304
#insert into t1 (txt) values ('Chevy'), ('Chevy ');
305
#--error ER_DUP_ENTRY
306
#insert into t1 (txt) values ('Chevy'), ('CHEVY');
307
#alter table t1 drop index txt_index, add index txt_index (txt(20));
308
#insert into t1 (txt) values ('Chevy ');
309
#select * from t1 where txt='Chevy';
310
#select * from t1 where txt='Chevy ';
311
#select * from t1 where txt='Chevy ' or txt='Chevy';
312
#select * from t1 where txt='Chevy' or txt='Chevy ';
313
#select * from t1 where id='1' or id='2';
314
#insert into t1 (txt) values('Ford');
315
#select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
316
#select * from t1 where txt='Chevy' or txt='Chevy ';
317
#select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
318
#select * from t1 where txt in ('Chevy ','Chevy');
319
#select * from t1 where txt in ('Chevy');
320
#select * from t1 where txt between 'Chevy' and 'Chevy';
321
#select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
322
#select * from t1 where txt between 'Chevy' and 'Chevy ';
323
#select * from t1 where txt < 'Chevy ';
324
#select * from t1 where txt <= 'Chevy';
325
#select * from t1 where txt > 'Chevy';
326
#select * from t1 where txt >= 'Chevy';
329
create table t1 (id integer primary key auto_increment, txt text, index txt_index (txt (20)));
330
insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL);
331
select * from t1 where txt='Chevy' or txt is NULL;
333
explain select * from t1 where txt='Chevy' or txt is NULL;
334
select * from t1 where txt='Chevy ';
335
select * from t1 where txt='Chevy ' or txt='Chevy';
336
select * from t1 where txt='Chevy' or txt='Chevy ';
337
select * from t1 where id='1' or id='2';
338
insert into t1 (txt) values('Ford');
339
select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
340
select * from t1 where txt='Chevy' or txt='Chevy ';
341
select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
342
select * from t1 where txt in ('Chevy ','Chevy');
343
select * from t1 where txt in ('Chevy');
344
select * from t1 where txt between 'Chevy' and 'Chevy';
345
select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
346
select * from t1 where txt between 'Chevy' and 'Chevy ';
347
select * from t1 where txt < 'Chevy ';
348
select * from t1 where txt < 'Chevy ' or txt is NULL;
349
select * from t1 where txt <= 'Chevy';
350
select * from t1 where txt > 'Chevy';
351
select * from t1 where txt >= 'Chevy';
352
alter table t1 modify column txt blob;
354
explain select * from t1 where txt='Chevy' or txt is NULL;
355
select * from t1 where txt='Chevy' or txt is NULL;
357
explain select * from t1 where txt='Chevy' or txt is NULL order by txt;
358
select * from t1 where txt='Chevy' or txt is NULL order by txt;
361
CREATE TABLE t1 ( i int NOT NULL default '0', c text NOT NULL, d varchar(1) NOT NULL DEFAULT ' ', PRIMARY KEY (i), KEY (c(1),d));
362
INSERT t1 (i, c) VALUES (1,''),(2,''),(3,'asdfh'),(4,'');
363
select max(i) from t1 where c = '';
369
# Bug#11657: Creation of secondary index fails
371
create table t1 (a int, b int, c tinyblob, d int, e int);
372
alter table t1 add primary key (a,b,c(255),d);
373
alter table t1 add key (a,b,d,e);
374
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
375
show create table t1;
379
# Test that blobs are sorted according to length
382
CREATE table t1 (a blob);
383
insert into t1 values ('b'),('a\0'),('a'),('a '),('aa'),(NULL);
384
select hex(a) from t1 order by a;
385
select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
386
alter table t1 modify a blob;
387
select hex(a) from t1 order by a;
388
select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
389
alter table t1 modify a char(5);
390
select hex(a) from t1 order by a;
391
select hex(concat(a,'\0')) as b from t1 order by concat(a,'\0');
395
# Bug #19489: Inconsistent support for DEFAULT in TEXT columns
397
create table t1 (a text default '');
398
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
399
show create table t1;
400
insert into t1 values (default);
403
create table t1 (a text default '');
407
# Bug #32282: TEXT silently truncates when value is exactly 65536 bytes
410
CREATE TABLE t (c TEXT);
411
INSERT INTO t (c) VALUES (REPEAT('1',65537));
412
INSERT INTO t (c) VALUES (REPEAT('2',65536));
413
INSERT INTO t (c) VALUES (REPEAT('3',65535));
414
SELECT LENGTH(c), CHAR_LENGTH(c) FROM t;
417
--echo End of 5.0 tests
added
removed
plugin/haildb/tests/t/type_blob.test
