Nuno added a comment - 2019-07-09 08:26 Please let me know if you think this can affect other queries outside the "information_schema". I haven't detected any, myself.

Alice Sherepa added a comment - 2019-07-09 11:01 - edited Thanks a lot! Reproducible on 10.3, 10.4: CREATE TABLE author ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , name VARCHAR (100) NOT NULL ) ENGINE = InnoDB;   CREATE TABLE book ( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , title VARCHAR (200) NOT NULL , author_id SMALLINT UNSIGNED NOT NULL , CONSTRAINT `fk_b` FOREIGN KEY (author_id) REFERENCES author (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB;   SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME);   SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME; MariaDB [test]> SELECT k.TABLE_NAME,r.DELETE_RULE -> FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k -> USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME); Empty set (0.037 sec)   MariaDB [test]> SELECT k.TABLE_NAME,r.DELETE_RULE -> FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k -> ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME; +------------+-------------+ | TABLE_NAME | DELETE_RULE | +------------+-------------+ | book | CASCADE | +------------+-------------+ 1 row in set (0.033 sec)   MariaDB [test]> analyze format=json SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 35.09, "table": { "table_name": "r", "access_type": "ALL", "r_loops": 1, "r_rows": 1, "r_total_time_ms": 0.0521, "r_filtered": 100, "open_full_table": true, "scanned_databases": "all" }, "block-nl-join": { "table": { "table_name": "k", "access_type": "ALL", "r_loops": 1, "r_rows": 71, "r_total_time_ms": 0.0383, "r_filtered": 100, "open_full_table": true, "scanned_databases": "all" }, "buffer_type": "flat", "buffer_size": "2Kb", "join_type": "BNL", "attached_condition": "k.`CONSTRAINT_CATALOG` = r.`CONSTRAINT_CATALOG` and k.`CONSTRAINT_SCHEMA` = r.`CONSTRAINT_SCHEMA` and k.`CONSTRAINT_NAME` = r.`CONSTRAINT_NAME`", "r_filtered": 0 } } } 1 row in set (0.037 sec)   MariaDB [test]> analyze format=json SELECT k.TABLE_NAME,r.DELETE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k ON r.CONSTRAINT_CATALOG = k.CONSTRAINT_CATALOG AND r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 36.172, "table": { "table_name": "r", "access_type": "ALL", "r_loops": 1, "r_rows": 1, "r_total_time_ms": 0.0514, "r_filtered": 100, "open_full_table": true, "scanned_databases": "all" }, "block-nl-join": { "table": { "table_name": "k", "access_type": "ALL", "r_loops": 1, "r_rows": 71, "r_total_time_ms": 0.0762, "r_filtered": 100, "open_full_table": true, "scanned_databases": "all" }, "buffer_type": "flat", "buffer_size": "22Kb", "join_type": "BNL", "attached_condition": "k.`CONSTRAINT_CATALOG` = r.`CONSTRAINT_CATALOG` and k.`CONSTRAINT_SCHEMA` = r.`CONSTRAINT_SCHEMA` and k.`CONSTRAINT_NAME` = r.`CONSTRAINT_NAME`", "r_filtered": 1.4085 } } } 1 row in set (0.038 sec)

Varun Gupta (Inactive) added a comment - 2020-12-15 08:14 Fixed by MDEV-21201