1
drop table if exists t1;
2
select 'a' = 'a', 'a' = 'a ', 'a ' = 'a';
3
'a' = 'a' 'a' = 'a ' 'a ' = 'a'
5
select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a';
6
'a\0' = 'a' 'a\0' < 'a' 'a\0' > 'a'
8
select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0';
9
'a' = 'a\0' 'a' < 'a\0' 'a' > 'a\0'
11
select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a ';
12
'a\0' = 'a ' 'a\0' < 'a ' 'a\0' > 'a '
14
select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0';
15
'a ' = 'a\0' 'a ' < 'a\0' 'a ' > 'a\0'
17
select 'a a' > 'a', 'a \0' < 'a';
18
'a a' > 'a' 'a \0' < 'a'
20
select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a';
21
binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a'
23
create table t1 (text1 varchar(32) not NULL, KEY key1 (text1));
24
insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
26
Table Op Msg_type Msg_text
27
test.t1 check status OK
28
select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
32
select * from t1 where text1='teststring' or text1 like 'teststring_%';
36
select * from t1 where text1='teststring' or text1 > 'teststring\t';
39
select * from t1 order by text1;
44
explain select * from t1 order by text1;
45
id select_type table type possible_keys key key_len ref rows Extra
46
1 SIMPLE t1 index NULL key1 34 NULL 3 Using index
47
alter table t1 modify text1 char(32) binary not null;
49
Table Op Msg_type Msg_text
50
test.t1 check status OK
51
select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
55
select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%';
56
concat('|', text1, '|')
59
select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
60
concat('|', text1, '|')
62
select text1, length(text1) from t1 order by text1;
67
select text1, length(text1) from t1 order by binary text1;
72
alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
73
insert into t1 values ('teststring ');
74
select concat('|', text1, '|') from t1 order by text1;
75
concat('|', text1, '|')
80
select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
81
concat('|', text1, '|')
84
select concat('|', text1, '|') from t1 where text1='teststring';
85
concat('|', text1, '|')
87
select concat('|', text1, '|') from t1 where text1='teststring ';
88
concat('|', text1, '|')
90
alter table t1 modify text1 text not null, pack_keys=1;
91
select concat('|', text1, '|') from t1 where text1='teststring';
92
concat('|', text1, '|')
95
select concat('|', text1, '|') from t1 where text1='teststring ';
96
concat('|', text1, '|')
99
explain select concat('|', text1, '|') from t1 where text1='teststring ';
100
id select_type table type possible_keys key key_len ref rows Extra
101
1 SIMPLE t1 range key1 key1 22 NULL 1 Using where
102
select concat('|', text1, '|') from t1 where text1 like 'teststring_%';
103
concat('|', text1, '|')
106
select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%';
107
concat('|', text1, '|')
111
select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
112
concat('|', text1, '|')
115
select concat('|', text1, '|') from t1 order by text1;
116
concat('|', text1, '|')
122
create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0;
123
insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
124
select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%';
125
concat('|', text1, '|')
128
select concat('|', text1, '|') from t1 where text1='teststring' or text1 >= 'teststring\t';
129
concat('|', text1, '|')
133
create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap;
134
insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
135
select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
139
select * from t1 where text1='teststring' or text1 like 'teststring_%';
143
select * from t1 where text1='teststring' or text1 >= 'teststring\t';
147
select * from t1 order by text1;
152
explain select * from t1 order by text1;
153
id select_type table type possible_keys key key_len ref rows Extra
154
1 SIMPLE t1 index NULL key1 34 NULL 3
155
alter table t1 modify text1 char(32) binary not null;
156
select * from t1 order by text1;
162
create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) engine=innodb;
163
insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
165
Table Op Msg_type Msg_text
166
test.t1 check status OK
167
select * from t1 where text1='teststring' or text1 like 'teststring_%';
171
select * from t1 where text1='teststring' or text1 > 'teststring\t';
174
select * from t1 order by text1;
179
explain select * from t1 order by text1;
180
id select_type table type possible_keys key key_len ref rows Extra
181
1 SIMPLE t1 index NULL key1 34 NULL 3 Using index
182
alter table t1 modify text1 char(32) binary not null;
183
select * from t1 order by text1;
188
alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
189
insert into t1 values ('teststring ');
190
select concat('|', text1, '|') from t1 order by text1;
191
concat('|', text1, '|')
196
alter table t1 modify text1 text not null, pack_keys=1;
197
select * from t1 where text1 like 'teststring_%';
201
select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%';
206
select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t';
211
select concat('|', text1, '|') from t1 order by text1;
212
concat('|', text1, '|')