#
# Test latin_de character set
#

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, 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

#
# 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
