1# 2# Test problem with characters < ' ' at end of strings (Bug #3152) 3# 4 5-- source include/have_innodb.inc 6--disable_warnings 7drop table if exists t1; 8--enable_warnings 9 10-- source include/endspace.inc 11 12# 13# Test MyISAM tables. 14# 15 16create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)); 17insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 18check table t1; 19select * from t1 ignore key (key1) where text1='teststring' or 20 text1 like 'teststring_%' ORDER BY text1; 21select * from t1 where text1='teststring' or text1 like 'teststring_%'; 22select * from t1 where text1='teststring' or text1 > 'teststring\t'; 23select * from t1 order by text1; 24explain select * from t1 order by text1; 25 26alter table t1 modify text1 char(32) binary not null; 27check table t1; 28select * from t1 ignore key (key1) where text1='teststring' or 29 text1 like 'teststring_%' ORDER BY text1; 30select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c; 31select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; 32select text1, length(text1) from t1 order by text1; 33select text1, length(text1) from t1 order by binary text1; 34 35alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20)); 36insert into t1 values ('teststring '); 37select concat('|', text1, '|') from t1 order by text1; 38select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; 39select concat('|', text1, '|') from t1 where text1='teststring'; 40select concat('|', text1, '|') from t1 where text1='teststring '; 41 42alter table t1 modify text1 text not null, pack_keys=1; 43select concat('|', text1, '|') from t1 where text1='teststring'; 44select concat('|', text1, '|') from t1 where text1='teststring '; 45explain select concat('|', text1, '|') from t1 where text1='teststring '; 46select concat('|', text1, '|') from t1 where text1 like 'teststring_%'; 47select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c; 48select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; 49select concat('|', text1, '|') from t1 order by text1; 50drop table t1; 51 52create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0; 53insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 54select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c; 55select concat('|', text1, '|') from t1 where text1='teststring' or text1 >= 'teststring\t'; 56drop table t1; 57 58# Test HEAP tables (with BTREE keys) 59 60create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap; 61insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 62select * from t1 ignore key (key1) where text1='teststring' or 63 text1 like 'teststring_%' ORDER BY text1; 64select * from t1 where text1='teststring' or text1 like 'teststring_%'; 65select * from t1 where text1='teststring' or text1 >= 'teststring\t'; 66select * from t1 order by text1; 67explain select * from t1 order by text1; 68 69alter table t1 modify text1 char(32) binary not null; 70select * from t1 order by text1; 71drop table t1; 72 73# 74# Test InnoDB tables 75# 76 77create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) engine=innodb; 78insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 79check table t1; 80select * from t1 where text1='teststring' or text1 like 'teststring_%'; 81select * from t1 where text1='teststring' or text1 > 'teststring\t'; 82select * from t1 order by text1; 83explain select * from t1 order by text1; 84 85alter table t1 modify text1 char(32) binary not null; 86select * from t1 order by text1; 87 88alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20)); 89insert into t1 values ('teststring '); 90select concat('|', text1, '|') from t1 order by text1; 91 92alter table t1 modify text1 text not null, pack_keys=1; 93select * from t1 where text1 like 'teststring_%'; 94 95# The following gives wrong result in InnoDB 96--sorted_result 97select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%'; 98--sorted_result 99select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t'; 100select concat('|', text1, '|') from t1 order by text1; 101drop table t1; 102 103# End of 4.1 tests 104