MySQL Bugs: #99949: Unable to join TRIGGERS and REFERENTIAL

Bug #99949	Unable to join TRIGGERS and REFERENTIAL_CONSTRAINTS
Submitted:	22 Jun 2020 10:12	Modified:	7 Jul 2020 9:39
Reporter:	Talel Levi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.19 MySQL Community Server - GPL	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	join, REFERENTIAL_CONSTRAINTS, triggers

Description:
INFORMATION_SCHEMA.TRIGGERS and INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS can't be joined on Constraints_SCHEMA due to the fact that they have different collations.

SHOW FULL COLUMNS FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
CONSTRAINT_NAME Collation = utf8_tolower_ci

SHOW FULL COLUMNS FROM INFORMATION_SCHEMA.TRIGGERS;
TRIGGER_NAME Collation = utf8_general_ci

The query that I use:
SELECT  t.TRIGGER_NAME AS `NAME`
FROM INFORMATION_SCHEMA.TRIGGERS AS t
UNION ALL 
SELECT c.TABLE_NAME AS `NAME`
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS c;

This gives me the error:
Error Code: 1271. Illegal mix of collations for operation 'UNION'

As far as I understand the tables in the INFORMATION_SCHEMA are all special in-memory tables so I'm not sure how to work around this.

I am using MySQL Community Server version 8.0.19

How to repeat:
SELECT  t.TRIGGER_NAME AS `NAME`
FROM INFORMATION_SCHEMA.TRIGGERS AS t
UNION ALL 
SELECT c.TABLE_NAME AS `NAME`
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS c;

Suggested fix:
Update INFORMATION_SCHEMA columns to use the same collation.

Hi Mr. Levi,

Thank you a lot for your bug report.

However, this is not a bug.

Simply, there is no reason why would anybody want to make a UNION with trigger and table names.

Also, you can use simple introducers, as described in our Reference Manual.

Not a bug.

The query provided in bug report seems to run successfully on following releases :
  8.0.18-debug
  8.0.19-debug
  8.0.22-tr-debug

SHOW FULL COLUMNS FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
...
CONSTRAINT_NAME	varchar(64)	utf8_tolower_ci	YES		NULL		select	
TABLE_NAME	varchar(64)	utf8_bin	NO		NULL		select	
...

SHOW FULL COLUMNS FROM INFORMATION_SCHEMA.TRIGGERS;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
...
TRIGGER_NAME	varchar(64)	utf8_general_ci	NO		NULL		select	
...

SELECT  t.TRIGGER_NAME AS `NAME`
FROM INFORMATION_SCHEMA.TRIGGERS AS t
UNION ALL 
SELECT c.CONSTRAINT_NAME AS `NAME`
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS c;
NAME
sys_config_insert_set_user
sys_config_update_set_user
ts_insert
gs_insert

SELECT  t.TRIGGER_NAME AS `NAME`
FROM INFORMATION_SCHEMA.TRIGGERS AS t
UNION ALL 
SELECT c.TABLE_NAME AS `NAME`
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS c;
NAME
sys_config_insert_set_user
sys_config_update_set_user
ts_insert
gs_insert

Perhaps there is some information missing in the bug description ?

Hello Gopal Shankar,

You tested the wrong query. On the second select you are trying to select CONSTRAINT_NAME but it should be TABLE_NAME.

SELECT  t.TRIGGER_NAME AS `NAME`
FROM INFORMATION_SCHEMA.TRIGGERS AS t
UNION ALL 
SELECT c.TABLE_NAME AS `NAME`
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS c;

Anyway, this is a bug in the software MySQL Compare of Redgate. I have contacted their support but they are not going to fix it.

Thanks everyone for the help.

Thank you, Mr. Levi.