Bug #108088 DatabaseMetaDataUsingInfoSchema#getImportedKeys returns incorrect data
Submitted: 8 Aug 2022 10:27 Modified: 5 Mar 22:00
Reporter: Anastasia Volkova Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:Connector/J 8.0.30 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: metadata

[8 Aug 2022 10:27] Anastasia Volkova
Description:
DatabaseMetaDataUsingInfoSchema#getImportedKeys from JDBC driver returns incorrect data in case two tables with equals names in different schemas. FK names also are equals. But one of these keys case another delete rule.
DatabaseMetaDataUsingInfoSchema#getImportedKeys returns both of them, not one. That means that we have, in this case, two keys in resultSet, not one. And delete/update rules they have different. But we do not know - thst from this two keys is correct.

How to repeat:
Create database lessondb, create:

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

DROP TABLE IF EXISTS `lessons`;
CREATE TABLE `lessons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `lessons_student_id_fk` (`student_id`),
  CONSTRAINT `lessons_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `account` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

create database lessondb_test and switch to it to create:

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

DROP TABLE IF EXISTS `lessons`;
CREATE TABLE `lessons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `lessons_student_id_fk` (`student_id`),
  CONSTRAINT `lessons_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `account` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

Use DatabaseMetaDataUsingInfoSchema#getImportedKeys

Expected result: 
lessondb_test		account	id	lessondb_test		lessons	student_id	1	1	1	lessons_student_id_fk	PRIMARY	7

Actual result:

lessondb_test		account	id	lessondb_test		lessons	student_id	1	1	0	lessons_student_id_fk	PRIMARY	7
lessondb_test		account	id	lessondb_test		lessons	student_id	1	1	1	lessons_student_id_fk	PRIMARY	7

Suggested fix:
The issue is here in this row:

"sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) ");"

If I change it:

"sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_SCHEMA) ");"

It works fine for me (because CONSTRAINT_SCHEMA must be in the JOIN too).
[18 Apr 2023 12:12] MySQL Verification Team
Hello,

Thank you for the bug report.
Could you please provide repeatable test case (sample project, etc. - please make it as private if you prefer) to reproduce this issue at our end?

Regards,
Ashwini Patil
[19 May 2023 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[25 Jan 13:17] Henning Pöttker
Please re-open the bug.

Based on Anastasia's description, I've submitted a PR with the proposed fix and a reproducing test case: https://bugs.mysql.com/bug.php?id=113600
[5 Mar 21:49] Filipe Silva
Thank you so much Henning Pöttker. Verified as described.
[5 Mar 22:00] Filipe Silva
Duplicated by Bug#113600.