MySQL Bugs: #108088: DatabaseMetaDataUsingInfoSchema#getImportedKeys returns incorrect data

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

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).

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

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".

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

Thank you so much Henning Pöttker. Verified as described.

Duplicated by Bug#113600.