Create 'main' test directory and move 't' and 'r' there

1 files changed, 756 insertions, 0 deletions

diff --git a/mysql-test/main/ctype_collate.result b/mysql-test/main/ctype_collate.result
new file mode 100644
index 00000000000..5e8c5adac8f
--- /dev/null
+++ b/mysql-test/main/ctype_collate.result
@@ -0,0 +1,756 @@
+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_general_ci NOT NULL
+);
+ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1'
+CREATE TABLE t2 (
+latin1_f CHAR(32) CHARACTER SET latin1 COLLATE some_non_existing_col NOT NULL
+);
+ERROR HY000: Unknown collation: 'some_non_existing_col'
+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_swedish_ci;
+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_german2_ci;
+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_general_ci;
+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_general_ci;
+ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1'
+SELECT latin1_f COLLATE latin1_swedish_ci 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_german2_ci 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_general_ci 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_general_ci  AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
+ERROR 42000: COLLATION 'koi8r_general_ci' 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_swedish_ci;
+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_german2_ci;
+latin1_f	count(*)
+A	4
+AD	2
+AE	4
+AF	2
+B	2
+SS	3
+U	2
+UE	4
+Y	2
+Z	2
+SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_general_ci;
+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_general_ci;
+ERROR 42000: COLLATION 'koi8r_general_ci' 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_swedish_ci FROM t1;
+latin1_f COLLATE latin1_swedish_ci
+A
+AD
+AE
+AF
+Ä
+Å
+B
+U
+UE
+Ü
+SS
+ß
+Z
+SELECT DISTINCT latin1_f COLLATE latin1_german2_ci FROM t1;
+latin1_f COLLATE latin1_german2_ci
+A
+AD
+AE
+AF
+B
+U
+UE
+SS
+Y
+Z
+SELECT DISTINCT latin1_f COLLATE latin1_general_ci FROM t1;
+latin1_f COLLATE latin1_general_ci
+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;
+ERROR HY000: Unknown collation: 'koi8r'
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `latin1_f` char(32) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW FIELDS FROM  t1;
+Field	Type	Null	Key	Default	Extra
+latin1_f	char(32)	NO		NULL	
+ALTER TABLE t1 CHANGE latin1_f 
+latin1_f CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `latin1_f` char(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW FIELDS FROM  t1;
+Field	Type	Null	Key	Default	Extra
+latin1_f	char(32)	YES		NULL	
+ALTER TABLE t1 CHARACTER SET latin1 COLLATE latin1_bin;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `latin1_f` char(32) COLLATE latin1_bin DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin
+SHOW FIELDS FROM  t1;
+Field	Type	Null	Key	Default	Extra
+latin1_f	char(32)	YES		NULL	
+SET CHARACTER SET 'latin1';
+SHOW VARIABLES LIKE 'character_set_client';
+Variable_name	Value
+character_set_client	latin1
+SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
+charset('a')	collation('a')	coercibility('a')	'a'='A'
+latin1	latin1_swedish_ci	4	1
+explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select charset('a') AS `charset('a')`,collation('a') AS `collation('a')`,coercibility('a') AS `coercibility('a')`,'a' = 'A' AS `'a'='A'`
+SET CHARACTER SET koi8r;
+SHOW VARIABLES LIKE 'collation_client';
+Variable_name	Value
+SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
+charset('a')	collation('a')	coercibility('a')	'a'='A'
+latin1	latin1_swedish_ci	4	1
+SET CHARACTER SET 'DEFAULT';
+ERROR 42000: Unknown character set: 'DEFAULT'
+DROP TABLE t1;
+CREATE TABLE t1 
+(s1 CHAR(5) COLLATE latin1_german1_ci,
+s2 CHAR(5) COLLATE latin1_swedish_ci);
+SELECT * FROM t1 WHERE s1 = s2;
+ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
+DROP TABLE t1;
+CREATE TABLE t1
+(s1 CHAR(5) COLLATE latin1_german1_ci,
+s2 CHAR(5) COLLATE latin1_swedish_ci,
+s3 CHAR(5) COLLATE latin1_bin);
+INSERT INTO t1 VALUES ('a','A','A');
+SELECT * FROM t1 WHERE s1 = s2;
+ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
+SELECT * FROM t1 WHERE s1 = s3;
+s1	s2	s3
+SELECT * FROM t1 WHERE s2 = s3;
+s1	s2	s3
+a	A	A
+DROP TABLE t1;
+create table t1 (a varchar(1) character set latin1 collate latin1_general_ci);
+insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c');
+select * from t1 where a > 'B' collate latin1_bin;
+a
+a
+b
+C
+c
+select * from t1 where a <> 'B' collate latin1_bin;
+a
+A
+a
+b
+C
+c
+create index i on t1 (a);
+select * from t1 where a > 'B' collate latin1_bin;
+a
+a
+b
+C
+c
+select * from t1 where a <> 'B' collate latin1_bin;
+a
+A
+a
+b
+C
+c
+drop table t1;
+SET NAMES latin1;
+CREATE TABLE t1 
+(s1 char(10) COLLATE latin1_german1_ci,
+s2 char(10) COLLATE latin1_swedish_ci,
+KEY(s1),
+KEY(s2));
+INSERT INTO t1 VALUES ('a','a');
+INSERT INTO t1 VALUES ('b','b');
+INSERT INTO t1 VALUES ('c','c');
+INSERT INTO t1 VALUES ('d','d');
+INSERT INTO t1 VALUES ('e','e');
+INSERT INTO t1 VALUES ('f','f');
+INSERT INTO t1 VALUES ('g','g');
+INSERT INTO t1 VALUES ('h','h');
+INSERT INTO t1 VALUES ('i','i');
+INSERT INTO t1 VALUES ('j','j');
+EXPLAIN SELECT * FROM t1 WHERE s1='a';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	s1	s1	11	const	1	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE s2='a';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	s2	s2	11	const	1	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	s1	s1	11	const	1	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	s2	NULL	NULL	NULL	10	Using where
+EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	s1	s1	11	NULL	2	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	s2	NULL	NULL	NULL	10	Using where
+EXPLAIN SELECT * FROM t1 WHERE s1 IN  ('a','b' COLLATE latin1_german1_ci);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	s1	s1	11	NULL	2	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE s2 IN  ('a','b' COLLATE latin1_german1_ci);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	s2	NULL	NULL	NULL	10	Using where
+EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	s1	s1	11	NULL	1	Using index condition
+EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	s2	NULL	NULL	NULL	10	Using where
+DROP TABLE t1;
+create table t1(f1 varchar(10) character set latin2 collate latin2_hungarian_ci, key(f1));
+insert into t1 set f1=0x3F3F9DC73F;
+insert into t1 set f1=0x3F3F1E563F;
+insert into t1 set f1=0x3F3F;
+check table t1 extended;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+drop table t1;
+create table t1 (a varchar(2) character set latin7 collate latin7_general_ci,key(a));
+insert into t1 set a=0x4c20;
+insert into t1 set a=0x6c;
+insert into t1 set a=0x4c98;
+check table t1 extended;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+drop table t1;
+select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci);
+least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci)
+a
+create table t1
+select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` varchar(1) CHARACTER SET latin5 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+select case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate
+latin5_turkish_ci then 2 else 3 end;
+case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate
+latin5_turkish_ci then 2 else 3 end
+3
+select concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci);
+concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci)
+abc
+#
+# Bug#11765016 57926: ILLEGAL MIX OF COLLATIONS FOR OPERATION 'UNION' .. USING CONCAT/FUNCTION/
+# Not a bug: only adding coverage tests
+#
+SET NAMES latin1 COLLATE latin1_german2_ci;
+CREATE DATABASE test1 DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
+USE test1;
+#
+# Using "COLLATE latin1_swedish_ci" as the default collation for latin1
+#
+CREATE FUNCTION `getText`() RETURNS varchar(20) CHARSET latin1
+BEGIN
+RETURN "Testtext";
+END;//
+SELECT getText(), CHARSET(getText()), COLLATION(getText()), COERCIBILITY(getText());
+getText()	CHARSET(getText())	COLLATION(getText())	COERCIBILITY(getText())
+Testtext	latin1	latin1_swedish_ci	4
+CREATE TABLE t1 AS SELECT ' - ' AS a UNION SELECT getText();
+ERROR HY000: Illegal mix of collations for operation 'UNION'
+DROP FUNCTION getText;
+#
+# Using "CHARACTER SET latin1 COLLATE latin1_german2_ci" as the database defaults
+#
+CREATE FUNCTION `getText`() RETURNS varchar(20)
+BEGIN
+RETURN "Testtext";
+END;//
+SELECT getText(), CHARSET(getText()), COLLATION(getText()), COERCIBILITY(getText());
+getText()	CHARSET(getText())	COLLATION(getText())	COERCIBILITY(getText())
+Testtext	latin1	latin1_german2_ci	4
+CREATE TABLE t1 AS SELECT ' - ' AS a UNION SELECT getText();
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(20) COLLATE latin1_german2_ci DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
+DROP TABLE t1;
+DROP FUNCTION getText;
+#
+# Using explicit "CHARACTER SET latin1 COLLATE latin1_german2_ci"
+#
+CREATE FUNCTION `getText`() RETURNS varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci
+BEGIN
+RETURN "Testtext";
+END;//
+SELECT getText(), CHARSET(getText()), COLLATION(getText()), COERCIBILITY(getText());
+getText()	CHARSET(getText())	COLLATION(getText())	COERCIBILITY(getText())
+Testtext	latin1	latin1_german2_ci	4
+CREATE TABLE t1 AS SELECT ' - ' AS a UNION SELECT getText();
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(20) COLLATE latin1_german2_ci DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
+DROP TABLE t1;
+DROP FUNCTION getText;
+DROP DATABASE test1;
+USE test;
+SET NAMES latin1;
+#
+# MDEV-11320, MySQL BUG#81810: Inconsistent sort order for blob/text between InnoDB and filesort
+#
+CREATE TABLE t1 (
+b LONGTEXT CHARACTER SET "latin1" COLLATE "latin1_bin",
+KEY b (b(32))
+);
+INSERT INTO t1 (b) VALUES ('a'), (_binary 0x1), (_binary 0x0), ('');
+drop table t1;
+CREATE TABLE t1 (
+b LONGTEXT CHARACTER SET "latin1" COLLATE "latin1_bin",
+PRIMARY KEY b (b(32))
+);
+INSERT INTO t1 (b) VALUES ('a'), (_binary 0x1), (_binary 0x0), ('');
+explain 
+select hex(b) from t1 force index (PRIMARY) where b<'zzz';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	34	NULL	4	Using where
+select hex(b) from t1 force index (PRIMARY) where b<'zzz';
+hex(b)
+00
+01
+
+61
+explain
+select hex(b) from t1 where b<'zzz' order by b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	Using where; Using filesort
+select hex(b) from t1 where b<'zzz' order by b;
+hex(b)
+00
+01
+
+61
+drop table t1;