Merge 10.5 into 10.6

9 files changed, 62 insertions, 62 deletions

diff --git a/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.result b/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.result
index 7c2012945c9..df3bbca0fa2 100644
--- a/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.result
+++ b/mysql-test/suite/compat/maxdb/rpl_mariadb_timestamp.result
@@ -43,12 +43,12 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` timestamp NOT NULL DEFAULT current_timestamp()
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 SET sql_mode=MAXDB;
 SHOW CREATE TABLE t1;
 Table	Create Table
diff --git a/mysql-test/suite/compat/maxdb/type_timestamp.result b/mysql-test/suite/compat/maxdb/type_timestamp.result
index 355a4e28414..7bc0613bc56 100644
--- a/mysql-test/suite/compat/maxdb/type_timestamp.result
+++ b/mysql-test/suite/compat/maxdb/type_timestamp.result
@@ -15,7 +15,7 @@ t1	CREATE TABLE `t1` (
   `mdb_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   `ora_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   `max_timestamp` datetime DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 SET sql_mode=MAXDB;
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -49,5 +49,5 @@ t1	CREATE TABLE `t1` (
   `mdb_timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   `ora_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   `max_timestamp` datetime DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/r/column_compression.result b/mysql-test/suite/compat/oracle/r/column_compression.result
index 7c91e7c9be4..2709fe048d9 100644
--- a/mysql-test/suite/compat/oracle/r/column_compression.result
+++ b/mysql-test/suite/compat/oracle/r/column_compression.result
@@ -76,14 +76,14 @@ CREATE TABLE t1 (a VARCHAR(10) COMPRESSED UNICODE);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL
+  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT NULL
+  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 #
@@ -115,14 +115,14 @@ CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 DEFAULT '');
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT ''
+  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT ''
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR(10) COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100)));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
+  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
 )
 DROP TABLE t1;
 #
@@ -227,14 +227,14 @@ CREATE TABLE t1 (a VARCHAR2(10) COMPRESSED UNICODE);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL
+  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR2(10) COMPRESSED CHARACTER SET utf8);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT NULL
+  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 #
@@ -266,14 +266,14 @@ CREATE TABLE t1 (a VARCHAR2(10) COMPRESSED CHARACTER SET utf8 DEFAULT '');
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT ''
+  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT ''
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a VARCHAR2(10) COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100)));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
+  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
 )
 DROP TABLE t1;
 #
@@ -378,14 +378,14 @@ CREATE TABLE t1 (a TINYTEXT COMPRESSED UNICODE);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" tinytext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL
+  "a" tinytext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT NULL
+  "a" tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 #
@@ -417,14 +417,14 @@ CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 DEFAULT '');
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT ''
+  "a" tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT ''
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a TINYTEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100)));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
+  "a" tinytext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
 )
 DROP TABLE t1;
 #
@@ -529,14 +529,14 @@ CREATE TABLE t1 (a TEXT COMPRESSED UNICODE);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL
+  "a" text /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" text /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT NULL
+  "a" text /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 #
@@ -568,14 +568,14 @@ CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 DEFAULT '');
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" text /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT ''
+  "a" text /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT ''
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a TEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100)));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" text /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
+  "a" text /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
 )
 DROP TABLE t1;
 #
@@ -680,14 +680,14 @@ CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED UNICODE);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" mediumtext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL
+  "a" mediumtext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT NULL
+  "a" mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 #
@@ -719,14 +719,14 @@ CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 DEFAULT '');
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT ''
+  "a" mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT ''
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a MEDIUMTEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100)));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
+  "a" mediumtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
 )
 DROP TABLE t1;
 #
@@ -831,14 +831,14 @@ CREATE TABLE t1 (a LONGTEXT COMPRESSED UNICODE);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" longtext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 DEFAULT NULL
+  "a" longtext /*!100301 COMPRESSED*/ CHARACTER SET ucs2 COLLATE ucs2_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT NULL
+  "a" longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 #
@@ -870,14 +870,14 @@ CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 DEFAULT '');
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT ''
+  "a" longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT ''
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT('a',100)));
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
+  "a" longtext /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (repeat('a',100)) VIRTUAL
 )
 DROP TABLE t1;
 #
@@ -1324,7 +1324,7 @@ CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED);
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 DEFAULT NULL
+  "a" varchar(10) /*!100301 COMPRESSED*/ CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 CREATE TABLE t1 (a NVARCHAR(10) COMPRESSED BINARY COMPRESSED);
diff --git a/mysql-test/suite/compat/oracle/r/func_misc.result b/mysql-test/suite/compat/oracle/r/func_misc.result
index cf0fbed0d5e..28f27873415 100644
--- a/mysql-test/suite/compat/oracle/r/func_misc.result
+++ b/mysql-test/suite/compat/oracle/r/func_misc.result
@@ -98,7 +98,7 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
   "SQLCODE" int(11) NOT NULL,
-  "SQLERRM" varchar(512) CHARACTER SET utf8mb3 NOT NULL
+  "SQLERRM" varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL
 )
 DROP TABLE t1;
 DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result b/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result
index 9aca6fa3542..55da2b743eb 100644
--- a/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result
+++ b/mysql-test/suite/compat/oracle/r/rpl_mariadb_date.result
@@ -33,12 +33,12 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 SET SQL_MODE= ORACLE;
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -64,12 +64,12 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
   `a` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 SET SQL_MODE= ORACLE;
 SHOW CREATE TABLE t1;
 Table	Create Table
diff --git a/mysql-test/suite/compat/oracle/r/sp-param.result b/mysql-test/suite/compat/oracle/r/sp-param.result
index 2f119e62189..3b9be4585d4 100644
--- a/mysql-test/suite/compat/oracle/r/sp-param.result
+++ b/mysql-test/suite/compat/oracle/r/sp-param.result
@@ -5,7 +5,7 @@ SET sql_mode=ORACLE;
 CREATE FUNCTION f1(param CHAR) RETURN CHAR AS BEGIN RETURN param; END;;
 SHOW CREATE FUNCTION f1;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param CHAR) RETURN varchar(2000) CHARSET latin1
+f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param CHAR) RETURN varchar(2000) CHARSET latin1 COLLATE latin1_swedish_ci
 AS BEGIN RETURN param; END	latin1	latin1_swedish_ci	latin1_swedish_ci
 SELECT LENGTH(f1(REPEAT('a',2000)));;
 LENGTH(f1(REPEAT('a',2000)))
@@ -21,7 +21,7 @@ DROP FUNCTION f1;
 CREATE FUNCTION f1(param NCHAR) RETURN NCHAR AS BEGIN RETURN param; END;;
 SHOW CREATE FUNCTION f1;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param NCHAR) RETURN varchar(2000) CHARSET utf8mb3
+f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param NCHAR) RETURN varchar(2000) CHARSET utf8mb3 COLLATE utf8mb3_general_ci
 AS BEGIN RETURN param; END	latin1	latin1_swedish_ci	latin1_swedish_ci
 SELECT LENGTH(f1(REPEAT('a',2000)));;
 LENGTH(f1(REPEAT('a',2000)))
@@ -30,7 +30,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',2000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(2000) CHARACTER SET utf8mb3 DEFAULT NULL
+  "a" varchar(2000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;
@@ -53,7 +53,7 @@ DROP FUNCTION f1;
 CREATE FUNCTION f1(param VARCHAR) RETURN VARCHAR AS BEGIN RETURN param; END;;
 SHOW CREATE FUNCTION f1;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param VARCHAR) RETURN varchar(4000) CHARSET latin1
+f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param VARCHAR) RETURN varchar(4000) CHARSET latin1 COLLATE latin1_swedish_ci
 AS BEGIN RETURN param; END	latin1	latin1_swedish_ci	latin1_swedish_ci
 SELECT LENGTH(f1(REPEAT('a',4000)));;
 LENGTH(f1(REPEAT('a',4000)))
@@ -69,7 +69,7 @@ DROP FUNCTION f1;
 CREATE FUNCTION f1(param VARCHAR2) RETURN VARCHAR2 AS BEGIN RETURN param; END;;
 SHOW CREATE FUNCTION f1;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param VARCHAR2) RETURN varchar(4000) CHARSET latin1
+f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param VARCHAR2) RETURN varchar(4000) CHARSET latin1 COLLATE latin1_swedish_ci
 AS BEGIN RETURN param; END	latin1	latin1_swedish_ci	latin1_swedish_ci
 SELECT LENGTH(f1(REPEAT('a',4000)));;
 LENGTH(f1(REPEAT('a',4000)))
@@ -85,7 +85,7 @@ DROP FUNCTION f1;
 CREATE FUNCTION f1(param NVARCHAR) RETURN NVARCHAR AS BEGIN RETURN param; END;;
 SHOW CREATE FUNCTION f1;
 Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
-f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param NVARCHAR) RETURN varchar(4000) CHARSET utf8mb3
+f1	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT	CREATE DEFINER="root"@"localhost" FUNCTION "f1"(param NVARCHAR) RETURN varchar(4000) CHARSET utf8mb3 COLLATE utf8mb3_general_ci
 AS BEGIN RETURN param; END	latin1	latin1_swedish_ci	latin1_swedish_ci
 SELECT LENGTH(f1(REPEAT('a',4000)));;
 LENGTH(f1(REPEAT('a',4000)))
@@ -94,7 +94,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" varchar(4000) CHARACTER SET utf8mb3 DEFAULT NULL
+  "a" varchar(4000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;
diff --git a/mysql-test/suite/compat/oracle/r/sp-row.result b/mysql-test/suite/compat/oracle/r/sp-row.result
index 7fd986a71c8..3ed11d7f9de 100644
--- a/mysql-test/suite/compat/oracle/r/sp-row.result
+++ b/mysql-test/suite/compat/oracle/r/sp-row.result
@@ -822,9 +822,9 @@ CALL p1();
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "rec1.a" varchar(10) CHARACTER SET utf8mb3 DEFAULT NULL,
-  "rec2.a" varchar(10) CHARACTER SET utf8mb3 DEFAULT NULL,
-  "rec3.a" varchar(10) CHARACTER SET utf8mb3 DEFAULT NULL
+  "rec1.a" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  "rec2.a" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  "rec3.a" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 DROP PROCEDURE p1;
@@ -1914,8 +1914,8 @@ CALL p1();
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "var" char(10) CHARACTER SET utf8mb3 DEFAULT NULL,
-  "rec.var" char(10) CHARACTER SET utf8mb3 DEFAULT NULL
+  "var" char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  "rec.var" char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 DROP PROCEDURE p1;
@@ -1954,8 +1954,8 @@ CALL p1();
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "var" varchar(10) CHARACTER SET utf8mb3 DEFAULT NULL,
-  "rec.var" varchar(10) CHARACTER SET utf8mb3 DEFAULT NULL
+  "var" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  "rec.var" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 DROP PROCEDURE p1;
@@ -2163,8 +2163,8 @@ CALL p1();
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "var" text CHARACTER SET utf8mb3 DEFAULT NULL,
-  "rec.var" text CHARACTER SET utf8mb3 DEFAULT NULL
+  "var" text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  "rec.var" text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 DROP PROCEDURE p1;
@@ -2173,8 +2173,8 @@ CALL p1();
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "var" tinytext CHARACTER SET utf8mb3 DEFAULT NULL,
-  "rec.var" tinytext CHARACTER SET utf8mb3 DEFAULT NULL
+  "var" tinytext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  "rec.var" tinytext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 DROP PROCEDURE p1;
@@ -2183,8 +2183,8 @@ CALL p1();
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "var" mediumtext CHARACTER SET utf8mb3 DEFAULT NULL,
-  "rec.var" mediumtext CHARACTER SET utf8mb3 DEFAULT NULL
+  "var" mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  "rec.var" mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 DROP PROCEDURE p1;
@@ -2193,8 +2193,8 @@ CALL p1();
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "var" longtext CHARACTER SET utf8mb3 DEFAULT NULL,
-  "rec.var" longtext CHARACTER SET utf8mb3 DEFAULT NULL
+  "var" longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  "rec.var" longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL
 )
 DROP TABLE t1;
 DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index 961872d5746..2e7b21b2d75 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -1698,7 +1698,7 @@ t2	CREATE TABLE "t2" (
   "dc209" decimal(20,9) DEFAULT NULL,
   "cc" char(10) DEFAULT NULL,
   "cv" varchar(10) DEFAULT NULL,
-  "cvu" varchar(10) CHARACTER SET utf8mb3 DEFAULT NULL,
+  "cvu" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
   "t1" tinytext DEFAULT NULL,
   "t2" text DEFAULT NULL,
   "t3" mediumtext DEFAULT NULL,
@@ -1860,7 +1860,7 @@ t2	CREATE TABLE "t2" (
   "dc209" decimal(20,9) DEFAULT NULL,
   "cc" char(10) DEFAULT NULL,
   "cv" varchar(10) DEFAULT NULL,
-  "cvu" varchar(10) CHARACTER SET utf8mb3 DEFAULT NULL,
+  "cvu" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
   "t1" tinytext DEFAULT NULL,
   "t2" text DEFAULT NULL,
   "t3" mediumtext DEFAULT NULL,
@@ -2061,9 +2061,9 @@ CALL p1();
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "tables_table_name" varchar(64) CHARACTER SET utf8mb3 DEFAULT NULL,
+  "tables_table_name" varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
   "tables_table_rows" bigint(21) unsigned DEFAULT NULL,
-  "processlist_info" longtext CHARACTER SET utf8mb3 DEFAULT NULL,
+  "processlist_info" longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
   "processlist_info_binary" blob(65535) DEFAULT NULL
 )
 DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/r/type_date.result b/mysql-test/suite/compat/oracle/r/type_date.result
index ea37375c364..c9bda19bc85 100644
--- a/mysql-test/suite/compat/oracle/r/type_date.result
+++ b/mysql-test/suite/compat/oracle/r/type_date.result
@@ -26,7 +26,7 @@ t1	CREATE TABLE `t1` (
   `mdb_date` date DEFAULT NULL,
   `ora_date` datetime DEFAULT NULL,
   `max_date` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 SET sql_mode=ORACLE;
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -60,7 +60,7 @@ t1	CREATE TABLE `t1` (
   `mdb_date` date DEFAULT NULL,
   `ora_date` datetime DEFAULT NULL,
   `max_date` date DEFAULT NULL
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 DROP TABLE t1;
 #
 # ALTER..MODIFY and ALTER..CHANGE understand qualifiers