1drop table if exists t1;
2select 'a' = 'a', 'a' = 'a ', 'a ' = 'a';
3'a' = 'a'	'a' = 'a '	'a ' = 'a'
41	1	1
5select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a';
6'a\0' = 'a'	'a\0' < 'a'	'a\0' > 'a'
70	1	0
8select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0';
9'a' = 'a\0'	'a' < 'a\0'	'a' > 'a\0'
100	0	1
11select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a ';
12'a\0' = 'a '	'a\0' < 'a '	'a\0' > 'a '
130	1	0
14select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0';
15'a ' = 'a\0'	'a ' < 'a\0'	'a ' > 'a\0'
160	0	1
17select 'a  a' > 'a', 'a  \0' < 'a';
18'a  a' > 'a'	'a  \0' < 'a'
191	1
20select binary 'a  a' > 'a', binary 'a  \0' > 'a', binary 'a\0' > 'a';
21binary 'a  a' > 'a'	binary 'a  \0' > 'a'	binary 'a\0' > 'a'
221	1	1
23create table t1 (text1 varchar(32) not NULL, KEY key1 (text1));
24insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
25analyze table t1;
26check table t1;
27Table	Op	Msg_type	Msg_text
28test.t1	check	status	OK
29select * from t1 ignore key (key1) where text1='teststring' or
30text1 like 'teststring_%' ORDER BY text1;
31text1
32teststring
33teststring
34select * from t1 where text1='teststring' or text1 like 'teststring_%';
35text1
36teststring
37teststring
38select * from t1 where text1='teststring' or text1 > 'teststring\t';
39text1
40teststring
41select * from t1 order by text1;
42text1
43nothing
44teststring
45teststring
46explain select * from t1 order by text1;
47id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
481	SIMPLE	t1	NULL	index	NULL	key1	34	NULL	3	100.00	Using index
49Warnings:
50Note	1003	/* select#1 */ select `test`.`t1`.`text1` AS `text1` from `test`.`t1` order by `test`.`t1`.`text1`
51alter table t1 modify text1 char(32) binary not null;
52check table t1;
53Table	Op	Msg_type	Msg_text
54test.t1	check	status	OK
55select * from t1 ignore key (key1) where text1='teststring' or
56text1 like 'teststring_%' ORDER BY text1;
57text1
58teststring
59teststring
60select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c;
61c
62|teststring	|
63|teststring|
64select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
65concat('|', text1, '|')
66|teststring|
67select text1, length(text1) from t1 order by text1;
68text1	length(text1)
69nothing	7
70teststring		11
71teststring	10
72select text1, length(text1) from t1 order by binary text1;
73text1	length(text1)
74nothing	7
75teststring	10
76teststring		11
77alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
78insert into t1 values ('teststring ');
79select concat('|', text1, '|') from t1 order by text1;
80concat('|', text1, '|')
81|nothing|
82|teststring|
83|teststring	|
84|teststring |
85select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
86concat('|', text1, '|')
87|teststring|
88|teststring |
89select concat('|', text1, '|') from t1 where text1='teststring';
90concat('|', text1, '|')
91|teststring|
92select concat('|', text1, '|') from t1 where text1='teststring ';
93concat('|', text1, '|')
94|teststring |
95alter table t1 modify text1 text not null, pack_keys=1;
96analyze table t1;
97select concat('|', text1, '|') from t1 where text1='teststring';
98concat('|', text1, '|')
99|teststring|
100|teststring |
101select concat('|', text1, '|') from t1 where text1='teststring ';
102concat('|', text1, '|')
103|teststring|
104|teststring |
105explain select concat('|', text1, '|') from t1 where text1='teststring ';
106id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1071	SIMPLE	t1	NULL	ref	key1	key1	22	const	2	100.00	Using where
108Warnings:
109Note	1003	/* select#1 */ select concat('|',`test`.`t1`.`text1`,'|') AS `concat('|', text1, '|')` from `test`.`t1` where (`test`.`t1`.`text1` = 'teststring ')
110select concat('|', text1, '|') from t1 where text1 like 'teststring_%';
111concat('|', text1, '|')
112|teststring	|
113|teststring |
114select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c;
115c
116|teststring	|
117|teststring |
118|teststring|
119select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
120concat('|', text1, '|')
121|teststring|
122|teststring |
123select concat('|', text1, '|') from t1 order by text1;
124concat('|', text1, '|')
125|nothing|
126|teststring	|
127|teststring|
128|teststring |
129drop table t1;
130create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0;
131insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
132select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c;
133c
134|teststring	|
135|teststring|
136select concat('|', text1, '|') from t1 where text1='teststring' or text1 >= 'teststring\t';
137concat('|', text1, '|')
138|teststring	|
139|teststring|
140drop table t1;
141create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap;
142insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
143select * from t1 ignore key (key1) where text1='teststring' or
144text1 like 'teststring_%' ORDER BY text1;
145text1
146teststring
147teststring
148select * from t1 where text1='teststring' or text1 like 'teststring_%';
149text1
150teststring
151teststring
152select * from t1 where text1='teststring' or text1 >= 'teststring\t';
153text1
154teststring
155teststring
156select * from t1 order by text1;
157text1
158nothing
159teststring
160teststring
161explain select * from t1 order by text1;
162id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1631	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
164Warnings:
165Note	1003	/* select#1 */ select `test`.`t1`.`text1` AS `text1` from `test`.`t1` order by `test`.`t1`.`text1`
166alter table t1 modify text1 char(32) binary not null;
167select * from t1 order by text1;
168text1
169nothing
170teststring
171teststring
172drop table t1;
173create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) engine=innodb;
174insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
175analyze table t1;
176check table t1;
177Table	Op	Msg_type	Msg_text
178test.t1	check	status	OK
179select * from t1 where text1='teststring' or text1 like 'teststring_%';
180text1
181teststring
182teststring
183select * from t1 where text1='teststring' or text1 > 'teststring\t';
184text1
185teststring
186select * from t1 order by text1;
187text1
188nothing
189teststring
190teststring
191explain select * from t1 order by text1;
192id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1931	SIMPLE	t1	NULL	index	NULL	key1	34	NULL	3	100.00	Using index
194Warnings:
195Note	1003	/* select#1 */ select `test`.`t1`.`text1` AS `text1` from `test`.`t1` order by `test`.`t1`.`text1`
196alter table t1 modify text1 char(32) binary not null;
197select * from t1 order by text1;
198text1
199nothing
200teststring
201teststring
202alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
203insert into t1 values ('teststring ');
204select concat('|', text1, '|') from t1 order by text1;
205concat('|', text1, '|')
206|nothing|
207|teststring|
208|teststring	|
209|teststring |
210alter table t1 modify text1 text not null, pack_keys=1;
211select * from t1 where text1 like 'teststring_%';
212text1
213teststring
214teststring
215select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%';
216text1	length(text1)
217teststring	10
218teststring		11
219teststring 	11
220select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t';
221text1	length(text1)
222teststring	10
223teststring		11
224teststring 	11
225select concat('|', text1, '|') from t1 order by text1;
226concat('|', text1, '|')
227|nothing|
228|teststring	|
229|teststring|
230|teststring |
231drop table t1;
232