Bug #90690 Unable to join TABLE_CONSTRAINTS and REFERENTIAL_CONSTRAINTS
Submitted: 30 Apr 2018 11:51 Modified: 7 Jun 2018 15:53
Reporter: Laredo Tirnanic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.11 MySQL Community Server - GPL OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[30 Apr 2018 11:51] Laredo Tirnanic
Description:
INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS can't be joined on Constraints_SCHEMA due to the fact that they have different collations.

show create table TABLE_CONSTRAINTS;
CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `TABLE_CONSTRAINTS` AS (select (`cat`.`name` collate utf8_tolower_ci) AS `CONSTRAINT_CATALOG`,(`sch`.`name` collate utf8_tolower_ci) AS `CONSTRAINT_SCHEMA`,convert(`idx`.`name` using utf8) AS `CONSTRAINT_NAME`,(`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,if((`idx`.`type` = ''PRIMARY''),''PRIMARY KEY'',`idx`.`type`) AS `CONSTRAINT_TYPE` from (((`mysql`.`indexes` `idx` join `mysql`.`tables` `tbl` on((`idx`.`table_id` = `tbl`.`id`))) join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on(((`cat`.`id` = `sch`.`catalog_id`) and (`idx`.`type` in (''PRIMARY'',''UNIQUE''))))) where (can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`,`idx`.`hidden`))) union (select (`cat`.`name` collate utf8_tolower_ci) AS `CONSTRAINT_CATALOG`,(`sch`.`name` collate utf8_tolower_ci) AS `CONSTRAINT_SCHEMA`,convert(`fk`.`name` using utf8) AS `CONSTRAINT_NAME`,(`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,''FOREIGN KEY'' AS `CONSTRAINT_TYPE` from (((`mysql`.`foreign_keys` `fk` join `mysql`.`tables` `tbl` on((`fk`.`table_id` = `tbl`.`id`))) join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) where (can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`)))
See (`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`

show create table REFERENTIAL_CONSTRAINTS
CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `REFERENTIAL_CONSTRAINTS` AS select `cat`.`name` AS `CONSTRAINT_CATALOG`,`sch`.`name` AS `CONSTRAINT_SCHEMA`,`fk`.`name` AS `CONSTRAINT_NAME`,`fk`.`referenced_table_catalog` AS `UNIQUE_CONSTRAINT_CATALOG`,`fk`.`referenced_table_schema` AS `UNIQUE_CONSTRAINT_SCHEMA`,`fk`.`unique_constraint_name` AS `UNIQUE_CONSTRAINT_NAME`,`fk`.`match_option` AS `MATCH_OPTION`,`fk`.`update_rule` AS `UPDATE_RULE`,`fk`.`delete_rule` AS `DELETE_RULE`,`tbl`.`name` AS `TABLE_NAME`,`fk`.`referenced_table_name` AS `REFERENCED_TABLE_NAME` from (((`mysql`.`foreign_keys` `fk` join `mysql`.`tables` `tbl` on((`fk`.`table_id` = `tbl`.`id`))) join `mysql`.`schemata` `sch` on((`fk`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) where (can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`))
See `sch`.`name` AS `CONSTRAINT_SCHEMA`

The schema provider I'm running generates the following query:

SELECT DISTINCT
cons.constraint_schema,
cons.constraint_name, 
keycolumns.table_name, 
column_name, 
ordinal_position, 
refs.unique_constraint_name, 
cons2.TABLE_SCHEMA AS fk_schema,
cons2.table_name AS fk_table,
refs.delete_rule AS delete_rule,
refs.update_rule AS update_rule
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS cons
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS keycolumns
        ON (cons.constraint_catalog = keycolumns.constraint_catalog
            OR cons.constraint_catalog IS NULL) AND
        cons.constraint_schema = keycolumns.constraint_schema AND
        cons.constraint_name = keycolumns.constraint_name AND
        cons.table_name = keycolumns.table_name
    LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS refs
        ON (cons.constraint_catalog = refs.constraint_catalog
            OR cons.constraint_catalog IS NULL) AND
        cons.constraint_schema = refs.constraint_schema AND
        cons.constraint_name = refs.constraint_name AND
        cons.table_name = refs.table_name
    LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS cons2
        ON (cons2.constraint_catalog = refs.constraint_catalog
            OR cons2.constraint_catalog IS NULL) AND
        cons2.constraint_schema = refs.constraint_schema AND
        cons2.constraint_name = refs.unique_constraint_name AND
        cons2.table_name = refs.referenced_table_name
WHERE 
    (keycolumns.table_name = @tableName OR @tableName IS NULL) AND 
    (cons.constraint_schema = @schemaOwner OR @schemaOwner IS NULL) AND 
    cons.constraint_type = @constraint_type
ORDER BY
    cons.constraint_schema, keycolumns.table_name, cons.constraint_name, ordinal_position

This gives me the error:
Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_tolower_ci,IMPLICIT) for operation '='

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.

How to repeat:
SELECT DISTINCT
cons.constraint_schema,
cons.constraint_name, 
keycolumns.table_name, 
column_name, 
ordinal_position, 
refs.unique_constraint_name, 
cons2.TABLE_SCHEMA AS fk_schema,
cons2.table_name AS fk_table,
refs.delete_rule AS delete_rule,
refs.update_rule AS update_rule
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS cons
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS keycolumns
        ON (cons.constraint_catalog = keycolumns.constraint_catalog
            OR cons.constraint_catalog IS NULL) AND
        cons.constraint_schema = keycolumns.constraint_schema AND
        cons.constraint_name = keycolumns.constraint_name AND
        cons.table_name = keycolumns.table_name
    LEFT OUTER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS refs
        ON (cons.constraint_catalog = refs.constraint_catalog
            OR cons.constraint_catalog IS NULL) AND
        cons.constraint_schema = refs.constraint_schema AND
        cons.constraint_name = refs.constraint_name AND
        cons.table_name = refs.table_name
    LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS cons2
        ON (cons2.constraint_catalog = refs.constraint_catalog
            OR cons2.constraint_catalog IS NULL) AND
        cons2.constraint_schema = refs.constraint_schema AND
        cons2.constraint_name = refs.unique_constraint_name AND
        cons2.table_name = refs.referenced_table_name
WHERE 
    (keycolumns.table_name = @tableName OR @tableName IS NULL) AND 
    (cons.constraint_schema = @schemaOwner OR @schemaOwner IS NULL) AND 
    cons.constraint_type = @constraint_type
ORDER BY
    cons.constraint_schema, keycolumns.table_name, cons.constraint_name, ordinal_position
[30 Apr 2018 11:58] Laredo Tirnanic
Added MySQL version
[30 Apr 2018 12:08] Miguel Solorzano
Thank you for the bug report. Verified as described.
[7 Jun 2018 14:15] Paul Dubois
Posted by developer:
 
Fixed in 8.0.13.

Joining the INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS and
TABLE_CONSTRAINTS tables on the CONSTRAINT_NAME failed because the
column collations differed.
[7 Jun 2018 15:53] Laredo Tirnanic
Thanks