2
drop table if exists t1, t2;
5
create table t1 (v varchar(30), c char(3), e enum('abc','def','ghi'), t text);
6
copy_file $MYSQL_TEST_DIR/std_data/vchar.frm $MYSQLTEST_VARDIR/master-data/test/vchar.frm;
9
show create table vchar;
10
insert into t1 values ('abc', 'de', 'ghi', 'jkl');
11
insert into t1 values ('abc ', 'de ', 'ghi', 'jkl ');
12
insert into t1 values ('abc ', 'd ', 'ghi', 'jkl ');
13
insert into vchar values ('abc', 'de', 'ghi', 'jkl');
14
insert into vchar values ('abc ', 'de ', 'ghi', 'jkl ');
15
insert into vchar values ('abc ', 'd ', 'ghi', 'jkl ');
16
select length(v),length(c),length(e),length(t) from t1;
17
select length(v),length(c),length(e),length(t) from vchar;
18
alter table vchar add i int;
19
show create table vchar;
20
select length(v),length(c),length(e),length(t) from vchar;
22
create table t1 (v varchar(20));
23
insert into t1 values('a ');
25
select binary v='a' from t1;
26
select binary v='a ' from t1;
27
insert into t1 values('a');
29
alter table t1 add primary key (v);
31
create table t1 (v varbinary(20));
32
insert into t1 values('a');
33
insert into t1 values('a ');
34
alter table t1 add primary key (v);
38
# Test with varchar of lengths 254,255,256,258 & 258 to ensure we don't
39
# have any problems with varchar with one or two byte length_bytes
42
create table t1 (v varchar(254), index (v));
43
insert into t1 values ("This is a test ");
44
insert into t1 values ("Some sample data");
45
insert into t1 values (" garbage ");
46
insert into t1 values (" This is a test ");
47
insert into t1 values ("This is a test");
48
insert into t1 values ("Hello world");
49
insert into t1 values ("Foo bar");
50
insert into t1 values ("This is a test");
51
insert into t1 values ("MySQL varchar test");
52
insert into t1 values ("test MySQL varchar");
53
insert into t1 values ("This is a long string to have some random length data included");
54
insert into t1 values ("Short string");
55
insert into t1 values ("VSS");
56
insert into t1 values ("Some samples");
57
insert into t1 values ("Bar foo");
58
insert into t1 values ("Bye");
63
select * from t1 where v like 'This is a test' order by v;
64
select * from t1 where v='This is a test' order by v;
65
select * from t1 where v like 'S%' order by v;
66
explain select * from t1 where v like 'This is a test' order by v;
67
explain select * from t1 where v='This is a test' order by v;
68
explain select * from t1 where v like 'S%' order by v;
69
eval alter table t1 change v v varchar($i);
77
select * from t1 where v like 'This is a test' order by v;
78
select * from t1 where v='This is a test' order by v;
79
select * from t1 where v like 'S%' order by v;
80
explain select * from t1 where v like 'This is a test' order by v;
81
explain select * from t1 where v='This is a test' order by v;
82
explain select * from t1 where v like 'S%' order by v;
83
eval alter table t1 change v v varchar($i);
87
alter table t1 change v v varchar(254), drop key v;
89
# Test with length(varchar) > 256 and key < 256 (to ensure things works with
90
# different kind of packing
92
alter table t1 change v v varchar(300), add key (v(10));
93
select * from t1 where v like 'This is a test' order by v;
94
select * from t1 where v='This is a test' order by v;
95
select * from t1 where v like 'S%' order by v;
96
explain select * from t1 where v like 'This is a test' order by v;
97
explain select * from t1 where v='This is a test' order by v;
98
explain select * from t1 where v like 'S%' order by v;
102
# bug#9339 - meaningless Field_varstring::get_key_image
104
create table t1 (pkcol varchar(16), othercol varchar(16), primary key (pkcol));
105
insert into t1 values ('test', 'something');
106
update t1 set othercol='somethingelse' where pkcol='test';
111
# Bug #9489: problems with key handling
114
create table t1 (a int, b varchar(12));
115
insert into t1 values (1, 'A'), (22, NULL);
116
create table t2 (a int);
117
insert into t2 values (22), (22);
118
select t1.a, t1.b, min(t1.b) from t1 inner join t2 ON t2.a = t1.a
123
# Bug #10543: convert varchar with index to text
125
create table t1 (f1 varchar(65500));
126
create index index1 on t1(f1(10));
127
show create table t1;
128
alter table t1 modify f1 varchar(255);
129
show create table t1;
130
alter table t1 modify f1 tinytext;
131
show create table t1;
135
# BUG#15588: String overrun
139
DROP TABLE IF EXISTS t1;
142
CREATE TABLE t1(f1 VARCHAR(100) DEFAULT 'test');
143
INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
144
DROP TABLE IF EXISTS t1;
146
CREATE TABLE t1(f1 CHAR(100) DEFAULT 'test');
147
INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
148
DROP TABLE IF EXISTS t1;
151
# Bug#14897 "ResultSet.getString("table.column") sometimes doesn't find the
153
# Test that after upgrading an old 4.1 VARCHAR column to 5.0 VARCHAR we preserve
154
# the original column metadata.
157
drop table if exists t1, t2, t3;
162
en varchar(255) character set utf8,
163
cz varchar(255) character set utf8
165
remove_file $MYSQLTEST_VARDIR/master-data/test/t3.frm;
166
copy_file $MYSQL_TEST_DIR/std_data/14897.frm $MYSQLTEST_VARDIR/master-data/test/t3.frm;
168
insert into t3 (id, en, cz) values
169
(1,'en string 1','cz string 1'),
170
(2,'en string 2','cz string 2'),
171
(3,'en string 3','cz string 3');
177
insert into t1 (id, name_id) values (1,1), (2,3), (3,3);
179
create table t2 (id int(11));
180
insert into t2 (id) values (1), (2), (3);
182
# max_length is different for varchar fields in ps-protocol and we can't
183
# replace a single metadata column, disable PS protocol
184
--disable_ps_protocol
186
select t1.*, t2.id, t3.en, t3.cz from t1 left join t2 on t1.id=t2.id
187
left join t3 on t1.id=t3.id order by t3.id;
190
drop table t1, t2, t3;
193
# Bug #11927: Warnings shown for CAST( chr as signed) but not (chr + 0)
195
CREATE TABLE t1 (a CHAR(2));
196
INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t');
197
SELECT a,(a + 0) FROM t1 ORDER BY a;
198
SELECT a,(a DIV 2) FROM t1 ORDER BY a;
199
SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a;