From 95bf76a7b5e6b8dabf8da566a0945b8ebb96cdee Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Henning=20P=C3=B6ttker?= Date: Sat, 6 Jan 2024 20:19:00 +0100 Subject: [PATCH] Fix for Bug#108088, fix join condition for retrieval of imported primary keys. --- .../jdbc/DatabaseMetaDataUsingInfoSchema.java | 2 +- .../regression/MetaDataRegressionTest.java | 85 +++++++++++++++++++ 2 files changed, 86 insertions(+), 1 deletion(-) diff --git a/src/main/user-impl/java/com/mysql/cj/jdbc/DatabaseMetaDataUsingInfoSchema.java b/src/main/user-impl/java/com/mysql/cj/jdbc/DatabaseMetaDataUsingInfoSchema.java index 1020bf461..c6970753e 100644 --- a/src/main/user-impl/java/com/mysql/cj/jdbc/DatabaseMetaDataUsingInfoSchema.java +++ b/src/main/user-impl/java/com/mysql/cj/jdbc/DatabaseMetaDataUsingInfoSchema.java @@ -456,7 +456,7 @@ public java.sql.ResultSet getImportedKeys(String catalog, String schema, String sqlBuf.append(" AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, R.UNIQUE_CONSTRAINT_NAME AS PK_NAME,"); sqlBuf.append(importedKeyNotDeferrable); sqlBuf.append(" AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A"); - sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) "); + sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_NAME) "); sqlBuf.append(generateOptionalRefContraintsJoin()); sqlBuf.append("WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY'"); if (db != null) { diff --git a/src/test/java/testsuite/regression/MetaDataRegressionTest.java b/src/test/java/testsuite/regression/MetaDataRegressionTest.java index 974114721..62870297d 100644 --- a/src/test/java/testsuite/regression/MetaDataRegressionTest.java +++ b/src/test/java/testsuite/regression/MetaDataRegressionTest.java @@ -5548,4 +5548,89 @@ void testBug109808() throws Exception { } while (useIS = !useIS); } + /** + * Tests fix for Bug#108088, DatabaseMetaData.getImportedKeys can return foreign keys from wrong schema. + * + * @throws Exception + */ + @Test + public void testBug108088() throws Exception { + String databaseName1 = "dbBug108088_1"; + String databaseName2 = "dbBug108088_2"; + if (isServerRunningOnWindows()) { + databaseName1 = databaseName1.toLowerCase(); + databaseName2 = databaseName2.toLowerCase(); + } + String tableName1 = "table1"; + String tableName2 = "table2"; + String constraintName = "fk_table2_fk_id"; + + createDatabase(databaseName1); + createTable(databaseName1 + "." + tableName1, "(`ID` bigint unsigned AUTO_INCREMENT PRIMARY KEY)"); + createTable(databaseName1 + "." + tableName2, + "(`ID` bigint unsigned AUTO_INCREMENT PRIMARY KEY, `FK_ID` bigint unsigned NOT NULL," + + "CONSTRAINT `" + constraintName + "` FOREIGN KEY (`FK_ID`) REFERENCES `" + + databaseName1 + "`.`" + tableName1 + "` (`ID`) ON DELETE CASCADE)"); + createDatabase(databaseName2); + createTable(databaseName2 + "." + tableName1, "(`ID` bigint unsigned AUTO_INCREMENT PRIMARY KEY)"); + createTable(databaseName2 + "." + tableName2, + "(`ID` bigint unsigned AUTO_INCREMENT PRIMARY KEY, `FK_ID` bigint unsigned NOT NULL," + + "CONSTRAINT `" + constraintName + "` FOREIGN KEY (`FK_ID`) REFERENCES `" + + databaseName2 + "`.`" + tableName1 + "` (`ID`) ON DELETE RESTRICT)"); + + Properties props = new Properties(); + props.setProperty(PropertyKey.sslMode.getKeyName(), SslMode.DISABLED.name()); + props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true"); + for (boolean useIS : new boolean[] { false, true }) { + for (String databaseTerm : new String[] { "CATALOG", "SCHEMA" }) { + props.setProperty(PropertyKey.useInformationSchema.getKeyName(), "" + useIS); + props.setProperty(PropertyKey.databaseTerm.getKeyName(), databaseTerm); + + boolean dbTermIsSchema = databaseTerm.equals("SCHEMA"); + + String err = "useInformationSchema=" + useIS + ", databaseTerm=" + databaseTerm; + Connection con = getConnectionWithProps(props); + DatabaseMetaData meta = con.getMetaData(); + + this.rs = dbTermIsSchema ? meta.getImportedKeys(null, databaseName1, tableName2) : meta.getImportedKeys(databaseName1, null, tableName2); + assertTrue(this.rs.next(), err); + assertEquals(dbTermIsSchema ? "def" : databaseName1, this.rs.getString("PKTABLE_CAT"), err); + assertEquals(dbTermIsSchema ? databaseName1 : null, this.rs.getString("PKTABLE_SCHEM"), err); + assertEquals(dbTermIsSchema ? "def" : databaseName1, this.rs.getString("FKTABLE_CAT"), err); + assertEquals(dbTermIsSchema ? databaseName1 : null, this.rs.getString("FKTABLE_SCHEM"), err); + assertEquals(tableName1, this.rs.getString("PKTABLE_NAME"), err); + assertEquals("ID", this.rs.getString("PKCOLUMN_NAME"), err); + assertEquals(tableName2, this.rs.getString("FKTABLE_NAME"), err); + assertEquals("FK_ID", this.rs.getString("FKCOLUMN_NAME"), err); + assertEquals(1, this.rs.getInt("KEY_SEQ"), err); + assertEquals(1, this.rs.getInt("UPDATE_RULE"), err); + assertEquals(0, this.rs.getInt("DELETE_RULE"), err); + assertEquals(constraintName, this.rs.getString("FK_NAME"), err); + assertEquals(useIS ? "PRIMARY" : null, this.rs.getString("PK_NAME"), err); + assertEquals(7, this.rs.getInt("DEFERRABILITY"), err); + assertFalse(this.rs.next(), err); + + this.rs = dbTermIsSchema ? meta.getImportedKeys(null, databaseName2, tableName2) : meta.getImportedKeys(databaseName2, null, tableName2); + assertTrue(this.rs.next(), err); + assertEquals(dbTermIsSchema ? "def" : databaseName2, this.rs.getString("PKTABLE_CAT"), err); + assertEquals(dbTermIsSchema ? databaseName2 : null, this.rs.getString("PKTABLE_SCHEM"), err); + assertEquals(dbTermIsSchema ? "def" : databaseName2, this.rs.getString("FKTABLE_CAT"), err); + assertEquals(dbTermIsSchema ? databaseName2 : null, this.rs.getString("FKTABLE_SCHEM"), err); + assertEquals(tableName1, this.rs.getString("PKTABLE_NAME"), err); + assertEquals("ID", this.rs.getString("PKCOLUMN_NAME"), err); + assertEquals(tableName2, this.rs.getString("FKTABLE_NAME"), err); + assertEquals("FK_ID", this.rs.getString("FKCOLUMN_NAME"), err); + assertEquals(1, this.rs.getInt("KEY_SEQ"), err); + assertEquals(1, this.rs.getInt("UPDATE_RULE"), err); + assertEquals(1, this.rs.getInt("DELETE_RULE"), err); + assertEquals(constraintName, this.rs.getString("FK_NAME"), err); + assertEquals(useIS ? "PRIMARY" : null, this.rs.getString("PK_NAME"), err); + assertEquals(7, this.rs.getInt("DEFERRABILITY"), err); + assertFalse(this.rs.next(), err); + + con.close(); + } + } + } + }