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