1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
#
# Test latin_de character set
#
drop table if exists t1;
create table t1 (a char (20) not null, b int not null auto_increment, index (a,b));
insert into t1 (a) values (''),('ac'),('ae'),('ad'),('c'),('aeb');
insert into t1 (a) values ('c'),('uc'),('ue'),('ud'),(''),('ueb'),('uf');
insert into t1 (a) values (''),('oc'),('a'),('oe'),('od'),('c'),('oeb');
insert into t1 (a) values ('s'),('ss'),(''),('b'),('ssa'),('ssc'),('a');
insert into t1 (a) values ('e'),('u'),('o'),(''),('a'),('aeae');
insert into t1 (a) values ('q'),('a'),('u'),('o'),(''),(''),('a');
select a,b from t1 order by a,b;
select a,b from t1 order by upper(a),b;
select a from t1 order by a desc;
check table t1;
select * from t1 where a like "%";
select * from t1 where a like binary "%%";
select * from t1 where a like "%%";
select * from t1 where a like "%U%";
select * from t1 where a like "%ss%";
drop table t1;
# The following should all be true
select strcmp('','ae'),strcmp('ae',''),strcmp('aeq','q'),strcmp('q','aeq');
select strcmp('ss',''),strcmp('','ss'),strcmp('s','sss'),strcmp('q','ssq');
# The following should all return -1
select strcmp('','af'),strcmp('a',''),strcmp('','aeq'),strcmp('','aeaeq');
select strcmp('ss','a'),strcmp('','ssa'),strcmp('sa','sssb'),strcmp('s','');
select strcmp('','o'),strcmp('','u'),strcmp('','oeb');
# The following should all return 1
select strcmp('af',''),strcmp('','a'),strcmp('aeq',''),strcmp('aeaeq','');
select strcmp('a','ss'),strcmp('ssa',''),strcmp('sssb','sa'),strcmp('','s');
select strcmp('u','a'),strcmp('u','');
#
# overlapping combo's
#
select strcmp('s', 'a'), strcmp('a', 'x');
#
# Some other simple tests with the current character set
#
create table t1 (a varchar(10), key(a), fulltext (a));
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
select * from t1 where a like "abc%";
select * from t1 where a like "test%";
select * from t1 where a like "te_t";
select * from t1 where match a against ("te*" in boolean mode)+0;
drop table t1;
#
# Test bug report #152 (problem with index on latin1_de)
#
create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word));
insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
update t1 set word2=word;
select word, word=0xdf as t from t1 having t > 0;
select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0;
select * from t1 where word=0xDF;
select * from t1 where word=CAST(0xDF as CHAR);
select * from t1 where word2=0xDF;
select * from t1 where word2=CAST(0xDF as CHAR);
select * from t1 where word='ae';
select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR);
select * from t1 where word between 0xDF and 0xDF;
select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR);
select * from t1 where word like 'ae';
select * from t1 where word like 'AE';
select * from t1 where word like 0xDF;
select * from t1 where word like CAST(0xDF as CHAR);
drop table t1;
#
# Bug #5447 Select does not find records
#
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
autor varchar(80) NOT NULL default '',
PRIMARY KEY (autor)
);
INSERT INTO t1 VALUES ('Powell, B.'),('Powell, Bud.'),('Powell, L. H.'),('Power, H.'),
('Poynter, M. A. L. Lane'),('Poynting, J. H. und J. J. Thomson.'),('Pozzi, S(amuel-Jean).'),
('Pozzi, Samuel-Jean.'),('Pozzo, A.'),('Pozzoli, Serge.');
SELECT * FROM t1 WHERE autor LIKE 'Poz%' ORDER BY autor;
DROP TABLE t1;
|
