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'); 18-- disable_result_log 19analyze table t1; 20-- enable_result_log 21check table t1; 22select * from t1 ignore key (key1) where text1='teststring' or 23 text1 like 'teststring_%' ORDER BY text1; 24select * from t1 where text1='teststring' or text1 like 'teststring_%'; 25select * from t1 where text1='teststring' or text1 > 'teststring\t'; 26select * from t1 order by text1; 27explain select * from t1 order by text1; 28 29alter table t1 modify text1 char(32) binary not null; 30check table t1; 31select * from t1 ignore key (key1) where text1='teststring' or 32 text1 like 'teststring_%' ORDER BY text1; 33select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c; 34select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; 35select text1, length(text1) from t1 order by text1; 36select text1, length(text1) from t1 order by binary text1; 37 38alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20)); 39insert into t1 values ('teststring '); 40select concat('|', text1, '|') from t1 order by text1; 41select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; 42select concat('|', text1, '|') from t1 where text1='teststring'; 43select concat('|', text1, '|') from t1 where text1='teststring '; 44 45alter table t1 modify text1 text not null, pack_keys=1; 46-- disable_result_log 47analyze table t1; 48-- enable_result_log 49select concat('|', text1, '|') from t1 where text1='teststring'; 50select concat('|', text1, '|') from t1 where text1='teststring '; 51explain select concat('|', text1, '|') from t1 where text1='teststring '; 52select concat('|', text1, '|') from t1 where text1 like 'teststring_%'; 53select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c; 54select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t'; 55select concat('|', text1, '|') from t1 order by text1; 56drop table t1; 57 58create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0; 59insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 60select concat('|', text1, '|') as c from t1 where text1='teststring' or text1 like 'teststring_%' order by c; 61select concat('|', text1, '|') from t1 where text1='teststring' or text1 >= 'teststring\t'; 62drop table t1; 63 64# Test HEAP tables (with BTREE keys) 65 66create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap; 67insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 68select * from t1 ignore key (key1) where text1='teststring' or 69 text1 like 'teststring_%' ORDER BY text1; 70select * from t1 where text1='teststring' or text1 like 'teststring_%'; 71select * from t1 where text1='teststring' or text1 >= 'teststring\t'; 72select * from t1 order by text1; 73explain select * from t1 order by text1; 74 75alter table t1 modify text1 char(32) binary not null; 76select * from t1 order by text1; 77drop table t1; 78 79# 80# Test InnoDB tables 81# 82 83create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) engine=innodb; 84insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); 85-- disable_result_log 86analyze table t1; 87-- enable_result_log 88check table t1; 89select * from t1 where text1='teststring' or text1 like 'teststring_%'; 90select * from t1 where text1='teststring' or text1 > 'teststring\t'; 91select * from t1 order by text1; 92explain select * from t1 order by text1; 93 94alter table t1 modify text1 char(32) binary not null; 95select * from t1 order by text1; 96 97alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20)); 98insert into t1 values ('teststring '); 99select concat('|', text1, '|') from t1 order by text1; 100 101alter table t1 modify text1 text not null, pack_keys=1; 102select * from t1 where text1 like 'teststring_%'; 103 104# The following gives wrong result in InnoDB 105select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%'; 106select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t'; 107select concat('|', text1, '|') from t1 order by text1; 108drop table t1; 109 110# End of 4.1 tests 111