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	type	possible_keys	key	key_len	ref	rows	Extra
481	SIMPLE	t1	index	NULL	key1	34	NULL	3	Using index
49alter table t1 modify text1 char(32) binary not null;
50check table t1;
51Table	Op	Msg_type	Msg_text
52test.t1	check	status	OK
53select * from t1 ignore key (key1) where text1='teststring' or
54text1 like 'teststring_%' ORDER BY text1;
55text1
56teststring
57teststring
58select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c;
59c
60|teststring	|
61|teststring|
62select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
63concat('|', text1, '|')
64|teststring|
65select text1, length(text1) from t1 order by text1;
66text1	length(text1)
67nothing	7
68teststring		11
69teststring	10
70select text1, length(text1) from t1 order by binary text1;
71text1	length(text1)
72nothing	7
73teststring	10
74teststring		11
75alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
76insert into t1 values ('teststring ');
77select concat('|', text1, '|') from t1 order by text1;
78concat('|', text1, '|')
79|nothing|
80|teststring|
81|teststring	|
82|teststring |
83select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
84concat('|', text1, '|')
85|teststring|
86|teststring |
87select concat('|', text1, '|') from t1 where text1='teststring';
88concat('|', text1, '|')
89|teststring|
90select concat('|', text1, '|') from t1 where text1='teststring ';
91concat('|', text1, '|')
92|teststring |
93alter table t1 modify text1 text not null, pack_keys=1;
94analyze table t1;
95select concat('|', text1, '|') from t1 where text1='teststring';
96concat('|', text1, '|')
97|teststring|
98|teststring |
99select concat('|', text1, '|') from t1 where text1='teststring ';
100concat('|', text1, '|')
101|teststring|
102|teststring |
103explain select concat('|', text1, '|') from t1 where text1='teststring ';
104id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1051	SIMPLE	t1	ref	key1	key1	22	const	2	Using where
106select concat('|', text1, '|') from t1 where text1 like 'teststring_%';
107concat('|', text1, '|')
108|teststring	|
109|teststring |
110select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c;
111c
112|teststring	|
113|teststring |
114|teststring|
115select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
116concat('|', text1, '|')
117|teststring|
118|teststring |
119select concat('|', text1, '|') from t1 order by text1;
120concat('|', text1, '|')
121|nothing|
122|teststring	|
123|teststring|
124|teststring |
125drop table t1;
126create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0;
127insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
128select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c;
129c
130|teststring	|
131|teststring|
132select concat('|', text1, '|') from t1 where text1='teststring' or text1 >= 'teststring\t';
133concat('|', text1, '|')
134|teststring	|
135|teststring|
136drop table t1;
137create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap;
138insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
139select * from t1 ignore key (key1) where text1='teststring' or
140text1 like 'teststring_%' ORDER BY text1;
141text1
142teststring
143teststring
144select * from t1 where text1='teststring' or text1 like 'teststring_%';
145text1
146teststring
147teststring
148select * from t1 where text1='teststring' or text1 >= 'teststring\t';
149text1
150teststring
151teststring
152select * from t1 order by text1;
153text1
154nothing
155teststring
156teststring
157explain select * from t1 order by text1;
158id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1591	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using filesort
160alter table t1 modify text1 char(32) binary not null;
161select * from t1 order by text1;
162text1
163nothing
164teststring
165teststring
166drop table t1;
167create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) engine=innodb;
168insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
169analyze table t1;
170check table t1;
171Table	Op	Msg_type	Msg_text
172test.t1	check	status	OK
173select * from t1 where text1='teststring' or text1 like 'teststring_%';
174text1
175teststring
176teststring
177select * from t1 where text1='teststring' or text1 > 'teststring\t';
178text1
179teststring
180select * from t1 order by text1;
181text1
182nothing
183teststring
184teststring
185explain select * from t1 order by text1;
186id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1871	SIMPLE	t1	index	NULL	key1	34	NULL	3	Using index
188alter table t1 modify text1 char(32) binary not null;
189select * from t1 order by text1;
190text1
191nothing
192teststring
193teststring
194alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
195insert into t1 values ('teststring ');
196select concat('|', text1, '|') from t1 order by text1;
197concat('|', text1, '|')
198|nothing|
199|teststring|
200|teststring	|
201|teststring |
202alter table t1 modify text1 text not null, pack_keys=1;
203select * from t1 where text1 like 'teststring_%';
204text1
205teststring
206teststring
207select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%';
208text1	length(text1)
209teststring	10
210teststring		11
211teststring 	11
212select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t';
213text1	length(text1)
214teststring	10
215teststring		11
216teststring 	11
217select concat('|', text1, '|') from t1 order by text1;
218concat('|', text1, '|')
219|nothing|
220|teststring	|
221|teststring|
222|teststring |
223drop table t1;
224