Since 10.3 (I believe), the following query returns EMPTY set:
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS r JOIN information_schema.KEY_COLUMN_USAGE k USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
But if I expand the USING into 'equals', it returns proper results:
SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, r.DELETE_RULE, r.UPDATE_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
Tried with "root" and local user.
Before, it was always returning results with USING.
I would expect that USING works exactly the same with 'equals' ?
Attachments
Issue Links
is duplicated by
MDEV-21201No records produced in information_schema query, depending on projection
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
"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
Please let me know if you think this can affect other queries outside the "information_schema".
I haven't detected any, myself.