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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
#
# Test latin_de character set
#
# Enable after fix MDEV-27904 and not earlier version 10.6
-- source include/no_view_protocol.inc
create database latin1_german2_ci default character set latin1 collate latin1_german2_ci;
use latin1_german2_ci;
set names latin1;
set @@collation_connection=latin1_german2_ci;
select @@collation_connection;
--disable_warnings
drop table if exists t1;
--enable_warnings
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('sßa','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','sßa'),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)
#
#
# The below checks both binary and character comparisons.
#
create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word));
show create table t1;
insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
update t1 set word2=word;
select word, word=binary 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=binary 0xDF;
select * from t1 where word=CAST(0xDF as CHAR);
select * from t1 where word2=binary 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 binary 0xDF and binary 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 binary 0xDF;
select * from t1 where word like CAST(0xDF as CHAR);
drop table t1;
#
# Bug #5447 Select does not find records
#
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;
#
# Test of special character in german collation
#
CREATE TABLE t1 (
s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci
);
show create table t1;
INSERT INTO t1 VALUES ('Ü');
INSERT INTO t1 VALUES ('ue');
SELECT DISTINCT s1 FROM t1;
SELECT s1,COUNT(*) FROM t1 GROUP BY s1;
SELECT COUNT(DISTINCT s1) FROM t1;
SELECT FIELD('ue',s1), FIELD('Ü',s1), s1='ue', s1='Ü' FROM t1;
DROP TABLE t1;
-- source include/ctype_filesort.inc
-- source include/ctype_german.inc
#
# Bug#7878 with utf8_general_ci, equals (=) has problem with
# accent insensitivity.
# Although originally this problem was found with UTF8 character set,
# '=' behaved wrong for latin1_german2_ci as well.
# Let's check it does not work incorrect anymore.
#
SET NAMES latin1;
CREATE TABLE t1 (
col1 varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 collate latin1_german2_ci;
INSERT INTO t1 VALUES ('ß'),('ss'),('ss');
ALTER TABLE t1 ADD KEY ifword(col1);
SELECT * FROM t1 WHERE col1='ß' ORDER BY col1, BINARY col1;
DROP TABLE t1;
# End of 4.1 tests
#
# Bug#9509
#
create table t1 (s1 char(5) character set latin1 collate latin1_german2_ci);
insert into t1 values (0xf6) /* this is o-umlaut */;
select * from t1 where length(s1)=1 and s1='oe';
drop table t1;
--echo End of 5.1 tests
--echo #
--echo # Start of 5.6 tests
--echo #
--echo #
--echo # WL#3664 WEIGHT_STRING
--echo #
set @@collation_connection=latin1_german2_ci;
--source include/weight_string.inc
--source include/weight_string_euro.inc
--source include/weight_string_l1.inc
select hex(weight_string('Ä'));
select hex(weight_string('ä'));
select hex(weight_string('Ö'));
select hex(weight_string('ö'));
select hex(weight_string('Ü'));
select hex(weight_string('ü'));
select hex(weight_string('S'));
select hex(weight_string('s'));
select hex(weight_string('ß'));
select hex(weight_string('ä' as char(1)));
select hex(weight_string('ö' as char(1)));
select hex(weight_string('ü' as char(1)));
select hex(weight_string('ß' as char(1)));
select hex(weight_string('xä' as char(2)));
select hex(weight_string('xö' as char(2)));
select hex(weight_string('xü' as char(2)));
select hex(weight_string('xß' as char(2)));
--echo #
--echo # End of 5.6 tests
--echo #
drop database latin1_german2_ci;
use test;
|
