Test collation

create-environment.sql

-- ------------------------------
-- Create database (latin1)
-- ------------------------------
DROP DATABASE IF EXISTS `database_latin1`;
CREATE DATABASE `database_latin1` CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `database_latin1`;

-- ------------------------------
-- Core table
-- ------------------------------
DROP TABLE IF EXISTS `table_core_utf8mb4_unicode`;
DROP TABLE IF EXISTS `table_core_utf8mb4_general`;
DROP TABLE IF EXISTS `table_core_utf8_unicode`;
DROP TABLE IF EXISTS `table_core_utf8_general`;
DROP TABLE IF EXISTS `table_core_utf8mb4_unicode_utf8binindex`;
DROP TABLE IF EXISTS `table_core_utf8mb4_general_utf8binindex`;
DROP TABLE IF EXISTS `table_core_utf8_unicode_utf8binindex`;
DROP TABLE IF EXISTS `table_core_utf8_general_utf8binindex`;
DROP TABLE IF EXISTS `table_core_utf8mb4_unicode_utf8mb4binindex`;
DROP TABLE IF EXISTS `table_core_utf8mb4_general_utf8mb4binindex`;
DROP TABLE IF EXISTS `table_core_utf8_unicode_utf8mb4binindex`;
DROP TABLE IF EXISTS `table_core_utf8_general_utf8mb4binindex`;

-- Create tables
-- -- with default collation in varchar index
CREATE TABLE IF NOT EXISTS `table_core_utf8mb4_unicode` (
  `varchar_id` varchar(191) NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `table_core_utf8mb4_general` (
  `varchar_id` varchar(191) NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `table_core_utf8_unicode` (
  `varchar_id` varchar(191) NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `table_core_utf8_general` (
  `varchar_id` varchar(191) NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci;

-- -- with utf8_bin collation in varchar index
CREATE TABLE IF NOT EXISTS `table_core_utf8mb4_unicode_utf8binindex` (
  `varchar_id` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `table_core_utf8mb4_general_utf8binindex` (
  `varchar_id` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `table_core_utf8_unicode_utf8binindex` (
  `varchar_id` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `table_core_utf8_general_utf8binindex` (
  `varchar_id` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci;

-- -- with utf8mb4_bin collation in varchar index
CREATE TABLE IF NOT EXISTS `table_core_utf8mb4_unicode_utf8mb4binindex` (
  `varchar_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `table_core_utf8mb4_general_utf8mb4binindex` (
  `varchar_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `table_core_utf8_unicode_utf8mb4binindex` (
  `varchar_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `table_core_utf8_general_utf8mb4binindex` (
  `varchar_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci;

-- Add Data
INSERT INTO `table_core_utf8mb4_unicode` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');
INSERT INTO `table_core_utf8mb4_general` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');
INSERT INTO `table_core_utf8_unicode` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');
INSERT INTO `table_core_utf8_general` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');

INSERT INTO `table_core_utf8mb4_unicode_utf8binindex` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');
INSERT INTO `table_core_utf8mb4_general_utf8binindex` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');
INSERT INTO `table_core_utf8_unicode_utf8binindex` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');
INSERT INTO `table_core_utf8_general_utf8binindex` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');

INSERT INTO `table_core_utf8mb4_unicode_utf8mb4binindex` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');
INSERT INTO `table_core_utf8mb4_general_utf8mb4binindex` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');
INSERT INTO `table_core_utf8_unicode_utf8mb4binindex` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');
INSERT INTO `table_core_utf8_general_utf8mb4binindex` (`varchar_id`) VALUES ('xxxxxxxxxxxx'), ('yyyyyyyyyyyy');

-- ------------------------------
-- Extension table
-- ------------------------------
-- Drop tables
DROP TABLE IF EXISTS `table_plugin_utf8mb4_unicode`;
DROP TABLE IF EXISTS `table_plugin_utf8mb4_general`;
DROP TABLE IF EXISTS `table_plugin_utf8_unicode`;
DROP TABLE IF EXISTS `table_plugin_utf8_general`;

-- Create tables
CREATE TABLE IF NOT EXISTS `table_plugin_utf8mb4_unicode` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `varchar_id` varchar(191) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `varchar_id` (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `table_plugin_utf8mb4_general` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `varchar_id` varchar(191) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `varchar_id` (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_general_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `table_plugin_utf8_unicode` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `varchar_id` varchar(191) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `varchar_id` (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `table_plugin_utf8_general` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `varchar_id` varchar(191) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `varchar_id` (`varchar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci AUTO_INCREMENT=1;

-- Add Data
INSERT INTO `table_plugin_utf8mb4_unicode` (`id`, `varchar_id`) VALUES (1, 'xxxxxxxxxxxx'), (2, 'yyyyyyyyyyyy'), (3, 'zzzzzzzzzzzzz');
INSERT INTO `table_plugin_utf8mb4_general` (`id`, `varchar_id`) VALUES (1, 'xxxxxxxxxxxx'), (2, 'yyyyyyyyyyyy'), (3, 'zzzzzzzzzzzzz');
INSERT INTO `table_plugin_utf8_unicode` (`id`, `varchar_id`) VALUES (1, 'xxxxxxxxxxxx'), (2, 'yyyyyyyyyyyy'), (3, 'zzzzzzzzzzzzz');
INSERT INTO `table_plugin_utf8_general` (`id`, `varchar_id`) VALUES (1, 'xxxxxxxxxxxx'), (2, 'yyyyyyyyyyyy'), (3, 'zzzzzzzzzzzzz');

tests-and-results.sql

-- ------------------------------
-- Tests
-- ------------------------------
-- -- with default collation in varchar index
SELECT m.* FROM `table_core_utf8mb4_unicode` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_unicode` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;					-- Result: Illegal mix of collations
SELECT m.* FROM `table_core_utf8mb4_unicode` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_unicode` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK

SELECT m.* FROM `table_core_utf8mb4_general` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;					-- Result: Illegal mix of collations
SELECT m.* FROM `table_core_utf8mb4_general` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_general` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_general` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK

SELECT m.* FROM `table_core_utf8_unicode` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK
SELECT m.* FROM `table_core_utf8_unicode` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK
SELECT m.* FROM `table_core_utf8_unicode` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;						-- Result: OK
SELECT m.* FROM `table_core_utf8_unicode` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id; 						-- Result: Illegal mix of collations

SELECT m.* FROM `table_core_utf8_general` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK
SELECT m.* FROM `table_core_utf8_general` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;					-- Result: OK
SELECT m.* FROM `table_core_utf8_general` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;						-- Result: Illegal mix of collations
SELECT m.* FROM `table_core_utf8_general` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id;						-- Result: OK

-- -- with utf8_bin collation in varchar index
SELECT m.* FROM `table_core_utf8mb4_unicode_utf8binindex` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_unicode_utf8binindex` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_unicode_utf8binindex` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_unicode_utf8binindex` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK

SELECT m.* FROM `table_core_utf8mb4_general_utf8binindex` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_general_utf8binindex` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_general_utf8binindex` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_general_utf8binindex` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK

SELECT m.* FROM `table_core_utf8_unicode_utf8binindex` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK
SELECT m.* FROM `table_core_utf8_unicode_utf8binindex` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK
SELECT m.* FROM `table_core_utf8_unicode_utf8binindex` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;			-- Result: OK
SELECT m.* FROM `table_core_utf8_unicode_utf8binindex` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id; 			-- Result: OK

SELECT m.* FROM `table_core_utf8_general_utf8binindex` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK
SELECT m.* FROM `table_core_utf8_general_utf8binindex` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK
SELECT m.* FROM `table_core_utf8_general_utf8binindex` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;			-- Result: OK
SELECT m.* FROM `table_core_utf8_general_utf8binindex` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id;			-- Result: OK

-- -- with utf8mb4_bin collation in varchar index
SELECT m.* FROM `table_core_utf8mb4_unicode_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_unicode_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_unicode_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_unicode_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK

SELECT m.* FROM `table_core_utf8mb4_general_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_general_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_general_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8mb4_general_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK

SELECT m.* FROM `table_core_utf8_unicode_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8_unicode_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8_unicode_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK
SELECT m.* FROM `table_core_utf8_unicode_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id; 		-- Result: OK

SELECT m.* FROM `table_core_utf8_general_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8mb4_unicode` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8_general_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8mb4_general` AS m ON s.varchar_id = m.varchar_id;	-- Result: OK
SELECT m.* FROM `table_core_utf8_general_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8_unicode` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK
SELECT m.* FROM `table_core_utf8_general_utf8mb4binindex` AS s LEFT JOIN `table_plugin_utf8_general` AS m ON s.varchar_id = m.varchar_id;		-- Result: OK