COLLATE tests

2 files changed, 601 insertions, 0 deletions

diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result
new file mode 100644
index 00000000000..cf93de4b7c6
--- /dev/null
+++ b/mysql-test/r/ctype_collate.result
@@ -0,0 +1,486 @@
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t1 (
+latin1_f CHAR(32) CHARACTER SET latin1 NOT NULL
+);
+CREATE TABLE t2 (
+latin1_f CHAR(32) CHARACTER SET latin1 COLLATE koi8r NOT NULL
+);
+COLLATION 'koi8r' is not valid for CHARACTER SET 'latin1'
+INSERT INTO t1 (latin1_f) VALUES (_latin1'A');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'a');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'AD');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ad');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'AE');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ae');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'AF');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'af');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Ä');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ä');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Å');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'å');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'B');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'b');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'U');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'u');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'UE');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ue');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Ü');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ü');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'SS');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ss');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ß');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Y');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'y');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Z');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'z');
+SELECT latin1_f FROM t1 ORDER BY latin1_f;
+latin1_f
+A
+a
+AD
+ad
+AE
+ae
+AF
+af
+B
+b
+SS
+ss
+U
+u
+UE
+ue
+Ü
+ü
+Y
+y
+Z
+z
+Å
+å
+Ä
+ä
+ß
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1;
+latin1_f
+A
+a
+AD
+ad
+AE
+ae
+AF
+af
+B
+b
+SS
+ss
+U
+u
+UE
+ue
+Ü
+ü
+Y
+y
+Z
+z
+Å
+å
+Ä
+ä
+ß
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_de;
+latin1_f
+A
+a
+Å
+å
+AD
+ad
+AE
+ae
+Ä
+ä
+AF
+af
+B
+b
+SS
+ss
+ß
+U
+u
+UE
+ue
+Ü
+ü
+Y
+y
+Z
+z
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_ci_as;
+latin1_f
+A
+a
+AD
+ad
+AE
+ae
+AF
+af
+Ä
+ä
+Å
+å
+B
+b
+SS
+ss
+ß
+U
+u
+UE
+ue
+Ü
+ü
+Y
+y
+Z
+z
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_bin;
+latin1_f
+A
+AD
+AE
+AF
+B
+SS
+U
+UE
+Y
+Z
+a
+ad
+ae
+af
+b
+ss
+u
+ue
+y
+z
+Ä
+Å
+Ü
+ß
+ä
+å
+ü
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE koi8r;
+COLLATION 'koi8r' is not valid for CHARACTER SET 'latin1'
+SELECT latin1_f COLLATE latin1       AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+latin1_f_as
+A
+a
+AD
+ad
+AE
+ae
+AF
+af
+B
+b
+SS
+ss
+U
+u
+UE
+ue
+Ü
+ü
+Y
+y
+Z
+z
+Å
+å
+Ä
+ä
+ß
+SELECT latin1_f COLLATE latin1_de    AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+latin1_f_as
+A
+a
+Å
+å
+AD
+ad
+AE
+ae
+Ä
+ä
+AF
+af
+B
+b
+SS
+ss
+ß
+U
+u
+UE
+ue
+Ü
+ü
+Y
+y
+Z
+z
+SELECT latin1_f COLLATE latin1_ci_as AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+latin1_f_as
+A
+a
+AD
+ad
+AE
+ae
+AF
+af
+Ä
+ä
+Å
+å
+B
+b
+SS
+ss
+ß
+U
+u
+UE
+ue
+Ü
+ü
+Y
+y
+Z
+z
+SELECT latin1_f COLLATE latin1_bin   AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+latin1_f_as
+A
+AD
+AE
+AF
+B
+SS
+U
+UE
+Y
+Z
+a
+ad
+ae
+af
+b
+ss
+u
+ue
+y
+z
+Ä
+Å
+Ü
+ß
+ä
+å
+ü
+SELECT latin1_f COLLATE koi8r        AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+COLLATION 'koi8r' is not valid for CHARACTER SET 'latin1'
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f;
+latin1_f	count(*)
+A	2
+AD	2
+AE	2
+AF	2
+B	2
+SS	2
+U	2
+UE	2
+Ü	4
+Z	2
+Å	2
+Ä	2
+ß	1
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1;
+latin1_f	count(*)
+A	2
+AD	2
+AE	2
+AF	2
+B	2
+SS	2
+U	2
+UE	2
+Ü	4
+Z	2
+Å	2
+Ä	2
+ß	1
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_de;
+latin1_f	count(*)
+A	4
+AD	2
+AE	2
+Ä	2
+AF	2
+B	2
+SS	2
+ß	1
+U	2
+UE	2
+Ü	2
+Y	2
+Z	2
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_ci_as;
+latin1_f	count(*)
+A	2
+AD	2
+AE	2
+AF	2
+Ä	2
+Å	2
+B	2
+SS	2
+ß	1
+U	2
+UE	2
+Ü	2
+Y	2
+Z	2
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_bin;
+latin1_f	count(*)
+A	1
+AD	1
+AE	1
+AF	1
+B	1
+SS	1
+U	1
+UE	1
+Y	1
+Z	1
+a	1
+ad	1
+ae	1
+af	1
+b	1
+ss	1
+u	1
+ue	1
+y	1
+z	1
+Ä	1
+Å	1
+Ü	1
+ß	1
+ä	1
+å	1
+ü	1
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE koi8r;
+COLLATION 'koi8r' is not valid for CHARACTER SET 'latin1'
+SELECT DISTINCT latin1_f                      FROM t1;
+latin1_f
+A
+AD
+AE
+AF
+Ä
+Å
+B
+U
+UE
+Ü
+SS
+ß
+Z
+SELECT DISTINCT latin1_f COLLATE latin1       FROM t1;
+latin1_f COLLATE latin1
+A
+AD
+AE
+AF
+Ä
+Å
+B
+U
+UE
+Ü
+SS
+ß
+Z
+SELECT DISTINCT latin1_f COLLATE latin1_de    FROM t1;
+latin1_f COLLATE latin1_de
+A
+AD
+AE
+AF
+Ä
+B
+U
+UE
+Ü
+SS
+ß
+Y
+Z
+SELECT DISTINCT latin1_f COLLATE latin1_ci_as FROM t1;
+latin1_f COLLATE latin1_ci_as
+A
+AD
+AE
+AF
+Ä
+Å
+B
+U
+UE
+Ü
+SS
+ß
+Y
+Z
+SELECT DISTINCT latin1_f COLLATE latin1_bin   FROM t1;
+latin1_f COLLATE latin1_bin
+A
+a
+AD
+ad
+AE
+ae
+AF
+af
+Ä
+ä
+Å
+å
+B
+b
+U
+u
+UE
+ue
+Ü
+ü
+SS
+ss
+ß
+Y
+y
+Z
+z
+SELECT DISTINCT latin1_f COLLATE koi8r        FROM t1;
+COLLATION 'koi8r' is not valid for CHARACTER SET 'latin1'
+DROP TABLE t1;
diff --git a/mysql-test/t/ctype_collate.test b/mysql-test/t/ctype_collate.test
new file mode 100644
index 00000000000..741db4d55e7
--- /dev/null
+++ b/mysql-test/t/ctype_collate.test
@@ -0,0 +1,115 @@
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+--enable_warnings
+
+CREATE TABLE t1 (
+  latin1_f CHAR(32) CHARACTER SET latin1 NOT NULL
+);
+
+--error 1251
+CREATE TABLE t2 (
+  latin1_f CHAR(32) CHARACTER SET latin1 COLLATE koi8r NOT NULL
+);
+
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'A');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'a');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'AD');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ad');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'AE');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ae');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'AF');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'af');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Ä');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ä');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Å');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'å');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'B');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'b');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'U');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'u');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'UE');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ue');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Ü');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ü');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'SS');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ss');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'ß');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Y');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'y');
+
+INSERT INTO t1 (latin1_f) VALUES (_latin1'Z');
+INSERT INTO t1 (latin1_f) VALUES (_latin1'z');
+
+
+-- ORDER BY
+
+SELECT latin1_f FROM t1 ORDER BY latin1_f;
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1;
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_de;
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_ci_as;
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_bin;
+--error 1251
+SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE koi8r;
+
+--SELECT latin1_f COLLATE koi8r FROM t1 ;
+
+-- AS + ORDER BY
+SELECT latin1_f COLLATE latin1       AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+SELECT latin1_f COLLATE latin1_de    AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+SELECT latin1_f COLLATE latin1_ci_as AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+SELECT latin1_f COLLATE latin1_bin   AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+--error 1251
+SELECT latin1_f COLLATE koi8r        AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+
+
+-- GROUP BY
+
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f;
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1;
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_de;
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_ci_as;
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_bin;
+--error 1251
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE koi8r;
+
+
+--  DISTINCT
+
+SELECT DISTINCT latin1_f                      FROM t1;
+SELECT DISTINCT latin1_f COLLATE latin1       FROM t1;
+SELECT DISTINCT latin1_f COLLATE latin1_de    FROM t1;
+SELECT DISTINCT latin1_f COLLATE latin1_ci_as FROM t1;
+SELECT DISTINCT latin1_f COLLATE latin1_bin   FROM t1;
+--error 1251
+SELECT DISTINCT latin1_f COLLATE koi8r        FROM t1;
+
+
+-- Aggregates
+--SELECT MAX(k COLLATE latin1_de)
+--FROM t1
+
+
+-- WHERE
+--SELECT *
+--FROM t1 
+--WHERE (_latin1'Mu"ller' COLLATE latin1_de) = k 
+
+--HAVING
+--SELECT *
+--FROM t1 
+--HAVING (_latin1'Mu"ller' COLLATE latin1_de) = k 
+
+DROP TABLE t1;