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