1set default_storage_engine='tokudb';
2drop table if exists t1, t2;
3create table t1 (v varchar(30), c char(3), e enum('abc','def','ghi'), t text);
4truncate table vchar;
5show create table t1;
6Table	Create Table
7t1	CREATE TABLE `t1` (
8  `v` varchar(30) DEFAULT NULL,
9  `c` char(3) DEFAULT NULL,
10  `e` enum('abc','def','ghi') DEFAULT NULL,
11  `t` text DEFAULT NULL
12) ENGINE=ENGINE DEFAULT CHARSET=latin1
13show create table vchar;
14Table	Create Table
15vchar	CREATE TABLE `vchar` (
16  `v` varchar(30) DEFAULT NULL,
17  `c` char(3) DEFAULT NULL,
18  `e` enum('abc','def','ghi') DEFAULT NULL,
19  `t` text DEFAULT NULL
20) ENGINE=ENGINE DEFAULT CHARSET=latin1
21insert into t1 values ('abc', 'de', 'ghi', 'jkl');
22insert into t1 values ('abc ', 'de ', 'ghi', 'jkl ');
23insert into t1 values ('abc    ', 'd  ', 'ghi', 'jkl    ');
24insert into vchar values ('abc', 'de', 'ghi', 'jkl');
25insert into vchar values ('abc ', 'de ', 'ghi', 'jkl ');
26insert into vchar values ('abc    ', 'd  ', 'ghi', 'jkl    ');
27select length(v),length(c),length(e),length(t) from t1;
28length(v)	length(c)	length(e)	length(t)
293	2	3	3
304	2	3	4
317	1	3	7
32select length(v),length(c),length(e),length(t) from vchar;
33length(v)	length(c)	length(e)	length(t)
343	2	3	3
353	2	3	4
363	1	3	7
37alter table vchar add i int;
38show create table vchar;
39Table	Create Table
40vchar	CREATE TABLE `vchar` (
41  `v` varchar(30) DEFAULT NULL,
42  `c` char(3) DEFAULT NULL,
43  `e` enum('abc','def','ghi') DEFAULT NULL,
44  `t` text DEFAULT NULL,
45  `i` int(11) DEFAULT NULL
46) ENGINE=ENGINE DEFAULT CHARSET=latin1
47select length(v),length(c),length(e),length(t) from vchar;
48length(v)	length(c)	length(e)	length(t)
493	2	3	3
503	2	3	4
513	1	3	7
52drop table t1, vchar;
53create table t1 (v varchar(20));
54insert into t1 values('a ');
55select v='a' from t1;
56v='a'
571
58select binary v='a' from t1;
59binary v='a'
600
61select binary v='a ' from t1;
62binary v='a '
631
64insert into t1 values('a');
65alter table t1 add primary key (v);
66ERROR 23000: Duplicate entry 'a' for key 'PRIMARY'
67drop table t1;
68create table t1 (v varbinary(20));
69insert into t1 values('a');
70insert into t1 values('a ');
71alter table t1 add primary key (v);
72drop table t1;
73create table t1 (v varchar(254), index (v));
74insert into t1 values ("This is a test ");
75insert into t1 values ("Some sample data");
76insert into t1 values (" garbage ");
77insert into t1 values (" This is a test ");
78insert into t1 values ("This is a test");
79insert into t1 values ("Hello world");
80insert into t1 values ("Foo bar");
81insert into t1 values ("This is a test");
82insert into t1 values ("MySQL varchar test");
83insert into t1 values ("test MySQL varchar");
84insert into t1 values ("This is a long string to have some random length data included");
85insert into t1 values ("Short string");
86insert into t1 values ("VSS");
87insert into t1 values ("Some samples");
88insert into t1 values ("Bar foo");
89insert into t1 values ("Bye");
90select * from t1 where v like 'This is a test' order by v;
91v
92This is a test
93This is a test
94select * from t1 where v='This is a test' order by v;
95v
96This is a test
97This is a test
98This is a test
99select * from t1 where v like 'S%' order by v;
100v
101Short string
102Some sample data
103Some samples
104explain select * from t1 where v like 'This is a test' order by v;
105id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1061	SIMPLE	t1	range	v	v	257	NULL	NA	Using where; Using index
107explain select * from t1 where v='This is a test' order by v;
108id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1091	SIMPLE	t1	ref	v	v	257	const	NA	Using where; Using index
110explain select * from t1 where v like 'S%' order by v;
111id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1121	SIMPLE	t1	range	v	v	257	NULL	NA	Using where; Using index
113alter table t1 change v v varchar(255);
114select * from t1 where v like 'This is a test' order by v;
115v
116This is a test
117This is a test
118select * from t1 where v='This is a test' order by v;
119v
120This is a test
121This is a test
122This is a test
123select * from t1 where v like 'S%' order by v;
124v
125Short string
126Some sample data
127Some samples
128explain select * from t1 where v like 'This is a test' order by v;
129id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1301	SIMPLE	t1	range	v	v	258	NULL	NA	Using where; Using index
131explain select * from t1 where v='This is a test' order by v;
132id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1331	SIMPLE	t1	ref	v	v	258	const	NA	Using where; Using index
134explain select * from t1 where v like 'S%' order by v;
135id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1361	SIMPLE	t1	range	v	v	258	NULL	NA	Using where; Using index
137alter table t1 change v v varchar(256);
138select * from t1 where v like 'This is a test' order by v;
139v
140This is a test
141This is a test
142select * from t1 where v='This is a test' order by v;
143v
144This is a test
145This is a test
146This is a test
147select * from t1 where v like 'S%' order by v;
148v
149Short string
150Some sample data
151Some samples
152explain select * from t1 where v like 'This is a test' order by v;
153id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1541	SIMPLE	t1	range	v	v	259	NULL	NA	Using where; Using index
155explain select * from t1 where v='This is a test' order by v;
156id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1571	SIMPLE	t1	ref	v	v	259	const	NA	Using where; Using index
158explain select * from t1 where v like 'S%' order by v;
159id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1601	SIMPLE	t1	range	v	v	259	NULL	NA	Using where; Using index
161alter table t1 change v v varchar(257);
162select * from t1 where v like 'This is a test' order by v;
163v
164This is a test
165This is a test
166select * from t1 where v='This is a test' order by v;
167v
168This is a test
169This is a test
170This is a test
171select * from t1 where v like 'S%' order by v;
172v
173Short string
174Some sample data
175Some samples
176explain select * from t1 where v like 'This is a test' order by v;
177id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1781	SIMPLE	t1	range	v	v	260	NULL	NA	Using where; Using index
179explain select * from t1 where v='This is a test' order by v;
180id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1811	SIMPLE	t1	ref	v	v	260	const	NA	Using where; Using index
182explain select * from t1 where v like 'S%' order by v;
183id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1841	SIMPLE	t1	range	v	v	260	NULL	NA	Using where; Using index
185alter table t1 change v v varchar(258);
186select * from t1 where v like 'This is a test' order by v;
187v
188This is a test
189This is a test
190select * from t1 where v='This is a test' order by v;
191v
192This is a test
193This is a test
194This is a test
195select * from t1 where v like 'S%' order by v;
196v
197Short string
198Some sample data
199Some samples
200explain select * from t1 where v like 'This is a test' order by v;
201id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2021	SIMPLE	t1	range	v	v	261	NULL	NA	Using where; Using index
203explain select * from t1 where v='This is a test' order by v;
204id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2051	SIMPLE	t1	ref	v	v	261	const	NA	Using where; Using index
206explain select * from t1 where v like 'S%' order by v;
207id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2081	SIMPLE	t1	range	v	v	261	NULL	NA	Using where; Using index
209alter table t1 change v v varchar(259);
210select * from t1 where v like 'This is a test' order by v;
211v
212This is a test
213This is a test
214select * from t1 where v='This is a test' order by v;
215v
216This is a test
217This is a test
218This is a test
219select * from t1 where v like 'S%' order by v;
220v
221Short string
222Some sample data
223Some samples
224explain select * from t1 where v like 'This is a test' order by v;
225id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2261	SIMPLE	t1	range	v	v	262	NULL	NA	Using where; Using index
227explain select * from t1 where v='This is a test' order by v;
228id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2291	SIMPLE	t1	ref	v	v	262	const	NA	Using where; Using index
230explain select * from t1 where v like 'S%' order by v;
231id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2321	SIMPLE	t1	range	v	v	262	NULL	NA	Using where; Using index
233alter table t1 change v v varchar(258);
234select * from t1 where v like 'This is a test' order by v;
235v
236This is a test
237This is a test
238select * from t1 where v='This is a test' order by v;
239v
240This is a test
241This is a test
242This is a test
243select * from t1 where v like 'S%' order by v;
244v
245Short string
246Some sample data
247Some samples
248explain select * from t1 where v like 'This is a test' order by v;
249id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2501	SIMPLE	t1	range	v	v	261	NULL	NA	Using where; Using index
251explain select * from t1 where v='This is a test' order by v;
252id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2531	SIMPLE	t1	ref	v	v	261	const	NA	Using where; Using index
254explain select * from t1 where v like 'S%' order by v;
255id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2561	SIMPLE	t1	range	v	v	261	NULL	NA	Using where; Using index
257alter table t1 change v v varchar(257);
258select * from t1 where v like 'This is a test' order by v;
259v
260This is a test
261This is a test
262select * from t1 where v='This is a test' order by v;
263v
264This is a test
265This is a test
266This is a test
267select * from t1 where v like 'S%' order by v;
268v
269Short string
270Some sample data
271Some samples
272explain select * from t1 where v like 'This is a test' order by v;
273id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2741	SIMPLE	t1	range	v	v	260	NULL	NA	Using where; Using index
275explain select * from t1 where v='This is a test' order by v;
276id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2771	SIMPLE	t1	ref	v	v	260	const	NA	Using where; Using index
278explain select * from t1 where v like 'S%' order by v;
279id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2801	SIMPLE	t1	range	v	v	260	NULL	NA	Using where; Using index
281alter table t1 change v v varchar(256);
282select * from t1 where v like 'This is a test' order by v;
283v
284This is a test
285This is a test
286select * from t1 where v='This is a test' order by v;
287v
288This is a test
289This is a test
290This is a test
291select * from t1 where v like 'S%' order by v;
292v
293Short string
294Some sample data
295Some samples
296explain select * from t1 where v like 'This is a test' order by v;
297id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2981	SIMPLE	t1	range	v	v	259	NULL	NA	Using where; Using index
299explain select * from t1 where v='This is a test' order by v;
300id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3011	SIMPLE	t1	ref	v	v	259	const	NA	Using where; Using index
302explain select * from t1 where v like 'S%' order by v;
303id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3041	SIMPLE	t1	range	v	v	259	NULL	NA	Using where; Using index
305alter table t1 change v v varchar(255);
306select * from t1 where v like 'This is a test' order by v;
307v
308This is a test
309This is a test
310select * from t1 where v='This is a test' order by v;
311v
312This is a test
313This is a test
314This is a test
315select * from t1 where v like 'S%' order by v;
316v
317Short string
318Some sample data
319Some samples
320explain select * from t1 where v like 'This is a test' order by v;
321id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3221	SIMPLE	t1	range	v	v	258	NULL	NA	Using where; Using index
323explain select * from t1 where v='This is a test' order by v;
324id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3251	SIMPLE	t1	ref	v	v	258	const	NA	Using where; Using index
326explain select * from t1 where v like 'S%' order by v;
327id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3281	SIMPLE	t1	range	v	v	258	NULL	NA	Using where; Using index
329alter table t1 change v v varchar(254);
330select * from t1 where v like 'This is a test' order by v;
331v
332This is a test
333This is a test
334select * from t1 where v='This is a test' order by v;
335v
336This is a test
337This is a test
338This is a test
339select * from t1 where v like 'S%' order by v;
340v
341Short string
342Some sample data
343Some samples
344explain select * from t1 where v like 'This is a test' order by v;
345id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3461	SIMPLE	t1	range	v	v	257	NULL	NA	Using where; Using index
347explain select * from t1 where v='This is a test' order by v;
348id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3491	SIMPLE	t1	ref	v	v	257	const	NA	Using where; Using index
350explain select * from t1 where v like 'S%' order by v;
351id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3521	SIMPLE	t1	range	v	v	257	NULL	NA	Using where; Using index
353alter table t1 change v v varchar(253);
354alter table t1 change v v varchar(254), drop key v;
355alter table t1 change v v varchar(300), add key (v(10));
356select * from t1 where v like 'This is a test' order by v;
357v
358This is a test
359This is a test
360select * from t1 where v='This is a test' order by v;
361v
362This is a test
363This is a test
364This is a test
365select * from t1 where v like 'S%' order by v;
366v
367Short string
368Some sample data
369Some samples
370explain select * from t1 where v like 'This is a test' order by v;
371id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3721	SIMPLE	t1	range	v	v	13	NULL	NA	Using where; Using filesort
373explain select * from t1 where v='This is a test' order by v;
374id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3751	SIMPLE	t1	ref	v	v	13	const	NA	Using where
376explain select * from t1 where v like 'S%' order by v;
377id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3781	SIMPLE	t1	range	v	v	13	NULL	NA	Using where; Using filesort
379drop table t1;
380create table t1 (pkcol varchar(16), othercol varchar(16), primary key (pkcol));
381insert into t1 values ('test', 'something');
382update t1 set othercol='somethingelse' where pkcol='test';
383select * from t1;
384pkcol	othercol
385test	somethingelse
386drop table t1;
387create table t1 (a int, b varchar(12));
388insert into t1 values (1, 'A'), (22, NULL);
389create table t2 (a int);
390insert into t2 values (22), (22);
391select t1.a, t1.b, min(t1.b) from t1 inner join t2 ON t2.a = t1.a
392group by t1.b, t1.a;
393a	b	min(t1.b)
39422	NULL	NULL
395drop table t1, t2;
396create table t1 (f1 varchar(65500));
397create index index1 on t1(f1(10));
398show create table t1;
399Table	Create Table
400t1	CREATE TABLE `t1` (
401  `f1` varchar(65500) DEFAULT NULL,
402  KEY `index1` (`f1`(10))
403) ENGINE=ENGINE DEFAULT CHARSET=latin1
404alter table t1 modify f1 varchar(255);
405show create table t1;
406Table	Create Table
407t1	CREATE TABLE `t1` (
408  `f1` varchar(255) DEFAULT NULL,
409  KEY `index1` (`f1`(10))
410) ENGINE=ENGINE DEFAULT CHARSET=latin1
411alter table t1 modify f1 tinytext;
412show create table t1;
413Table	Create Table
414t1	CREATE TABLE `t1` (
415  `f1` tinytext DEFAULT NULL,
416  KEY `index1` (`f1`(10))
417) ENGINE=ENGINE DEFAULT CHARSET=latin1
418drop table t1;
419DROP TABLE IF EXISTS t1;
420CREATE TABLE t1(f1 VARCHAR(100) DEFAULT 'test');
421INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
422DROP TABLE IF EXISTS t1;
423CREATE TABLE t1(f1 CHAR(100) DEFAULT 'test');
424INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
425DROP TABLE IF EXISTS t1;
426CREATE TABLE t1 (a CHAR(2));
427INSERT INTO t1 VALUES (10), (50), (30), ('1a'), (60), ('t');
428SELECT a,(a + 0) FROM t1 ORDER BY a;
429a	(a + 0)
43010	10
4311a	1
43230	30
43350	50
43460	60
435t	0
436Warnings:
437Warning	1292	Truncated incorrect DOUBLE value: '1a'
438Warning	1292	Truncated incorrect DOUBLE value: 't '
439SELECT a,(a DIV 2) FROM t1 ORDER BY a;
440a	(a DIV 2)
44110	5
4421a	0
44330	15
44450	25
44560	30
446t	0
447Warnings:
448Warning	1292	Truncated incorrect DECIMAL value: '1a'
449Warning	1918	Encountered illegal value '' when converting to DECIMAL
450Warning	1292	Truncated incorrect DECIMAL value: 't '
451SELECT a,CAST(a AS SIGNED) FROM t1 ORDER BY a;
452a	CAST(a AS SIGNED)
45310	10
4541a	1
45530	30
45650	50
45760	60
458t	0
459Warnings:
460Warning	1292	Truncated incorrect INTEGER value: '1a'
461Warning	1292	Truncated incorrect INTEGER value: 't'
462DROP TABLE t1;
463CREATE TABLE t1 (a VARCHAR(16));
464INSERT INTO t1 VALUES ('5'), ('s'), ('');
465SELECT 5 = a FROM t1;
4665 = a
4671
4680
4690
470Warnings:
471Warning	1292	Truncated incorrect DOUBLE value: 's'
472Warning	1292	Truncated incorrect DOUBLE value: ''
473DROP TABLE t1;
474CREATE TABLE t1 (a CHAR(16));
475INSERT INTO t1 VALUES ('5'), ('s'), ('');
476SELECT 5 = a FROM t1;
4775 = a
4781
4790
4800
481Warnings:
482Warning	1292	Truncated incorrect DOUBLE value: 's               '
483Warning	1292	Truncated incorrect DOUBLE value: '                '
484DROP TABLE t1;
485