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