Bug: incomplete join in CodeIgniter\Database\MySQLi\Connection::_foreignKeyData()

[lukemim]

the join in CodeIgniter\Database\MySQLi\Connection::_foreignKeyData() is joining over CONSTRAINT_NAME only, but not over CONSTRAINT_SCHEMA, so if there are multiple databases (schemas) containing constraints with the same names _foreignKeyData() will return duplicated rows with metadata also from the other databases (schemas).

I guess this is where the bug was created: 12ea7ba

CI 4.1.3

[kenjis]

@lukemim Could you please follow the bug report template?
https://github.com/codeigniter4/CodeIgniter4/blob/develop/.github/ISSUE_TEMPLATE/bug_report.md

[kenjis]

I confirmed the bug.

SELECT
    tc.CONSTRAINT_NAME,
    tc.TABLE_NAME,
    kcu.COLUMN_NAME,
    rc.REFERENCED_TABLE_NAME,
    kcu.REFERENCED_COLUMN_NAME
FROM information_schema.TABLE_CONSTRAINTS AS tc
    INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS AS rc
        ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    INNER JOIN information_schema.KEY_COLUMN_USAGE AS kcu
        ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE
    tc.CONSTRAINT_TYPE = 'FOREIGN KEY' AND
        tc.TABLE_SCHEMA = 'ci4_crud' AND
        tc.TABLE_NAME = 'track';

+----------------------+----------+-----------+---------------------+----------------------+
|CONSTRAINT_NAME       |TABLE_NAME|COLUMN_NAME|REFERENCED_TABLE_NAME|REFERENCED_COLUMN_NAME|
+----------------------+----------+-----------+---------------------+----------------------+
|track_album_id_foreign|track     |album_id   |album                |id                    |
|track_album_id_foreign|track     |album_id   |album                |id                    |
|track_album_id_foreign|track     |album_id   |album                |id                    |
|track_album_id_foreign|track     |album_id   |album                |id                    |
+----------------------+----------+-----------+---------------------+----------------------+

[kenjis]

SELECT
    tc.CONSTRAINT_NAME,
    tc.TABLE_NAME,
    kcu.COLUMN_NAME,
    rc.REFERENCED_TABLE_NAME,
    kcu.REFERENCED_COLUMN_NAME
FROM information_schema.TABLE_CONSTRAINTS AS tc
    INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS AS rc
        ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
        AND tc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
    INNER JOIN information_schema.KEY_COLUMN_USAGE AS kcu
        ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
        AND tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
WHERE
    tc.CONSTRAINT_TYPE = 'FOREIGN KEY' AND
        tc.TABLE_SCHEMA = 'ci4_crud' AND
        tc.TABLE_NAME = 'track';

+----------------------+----------+-----------+---------------------+----------------------+
|CONSTRAINT_NAME       |TABLE_NAME|COLUMN_NAME|REFERENCED_TABLE_NAME|REFERENCED_COLUMN_NAME|
+----------------------+----------+-----------+---------------------+----------------------+
|track_album_id_foreign|track     |album_id   |album                |id                    |
+----------------------+----------+-----------+---------------------+----------------------+