MySQL Bugs: #49660: SQLForeignKeys returns rows for another table with same name but different schem

Bug #49660	SQLForeignKeys returns rows for another table with same name but different schem
Submitted:	14 Dec 2009 3:49	Modified:	8 Apr 2010 9:32
Reporter:	Farid Zidan (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.01.06.00	OS:	Windows (XP SP3)
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any
Tags:	SQLForeignKeys

Description:
MySQL 5.1.37-community via TCP/IP Windows XP 64-bit
MySQL ODBC 5.1 Driver 5.01.06.00 Windows XP 64-bit and 32-bit

I have databases (schemas) myrep and myrep123 and two users repadmin and repadmin123. repadmin creates the same schema in myrep and repadmin123 creates the same schema in myrep123.

repadmin is granted all privileges in myrep and myrep123
repadmin123 is granted all privileges in myrep and myrep123

Executing ::SQLForeignKeys by repadmin123 in database myrep123 for a table returns fk rows for the given table in database myrep123 AND fk rows for the same table in myrep. Should only return fk rows for the table ::SQLForeignKeys executed on, name the table in myrep123

How to repeat:
1 create database myrep
2 create database myrep123
3 create user repadmin
4 create user repadmin123
5 grant repadmin all database priviliges in myrep 
6 grant repadmin all database priviliges in myrep123 
7 grant repadmin123 all database priviliges in myrep 
8 grant repadmin123 all database priviliges in myrep123 
9 run the following script by repadmin in myrep:

create table test_parent (
   parent_oid integer not null,
   description varchar(255),
   primary key (parent_oid)
);
create table test_child (
  child_oid integer not null,
  parent_oid integer,
  primary key (child_oid)
);

alter table test_child add constraint FK_PARENT_OID foreign key (parent_oid)
  references test_parent (parent_oid) on delete restrict on update restrict;

10 run the same script by repadmin123 in database myrep123
11 execute ::SQLForeignKeys (imported FK) by repadmin123 on table `myrep123`.`test_child` 
12 you get two rows instead of one
FK_PARENT_OID	parent_oid	1
FK_PARENT_OID	parent_oid	1
(see screen shot 1)

13 execute ::SQLForeignKeys (exported FK) by repadmin on table `myrep123`.`test_parent`
14 you get two rows instead of one
FK_PARENT_OID	parent_oid	1
FK_PARENT_OID	parent_oid	1
(see screen shot 2)

Suggested fix:
Seems that resultset sql is no correctly limited by catalog where clause. Should only return foreign keys for the given table and not for other tables that have the same name but are ceated in different databases on the server

imported Foreign keys extra rows

Attachment: imported fk extra rows.JPG (image/jpeg, text), 76.73 KiB.

Imported Foreign keys extra rows

Attachment: imported fk extra rows.JPG (image/jpeg, text), 76.73 KiB.

Correction Step 13 should say:
13 execute ::SQLForeignKeys (exported FK) by repadmin123 on table `myrep123`.`test_parent`

Hi Farid and thanks for your report.

Verified as described. SQLForeignKeys will return two instead of one row:
SQLForeignKeys:
	In: StatementHandle = 0x00675C20, PkCatalogName = "", NameLength1 = 0, PkSchemaName = "myrep123", NameLength2 = 8, PkTableName = "test_parent", NameLength3 = 11, FkCatalogName = "", NameLength4 = 0, FkSchemaName = "myrep123", NameLength5 = 8, FkTableName = "test_child", NameLength6 = 10
	Return: SQL_SUCCESS=0

Get Data All:
"A.PKTABLE_CAT", ".PKTABLE_SCHEM", "A.PKTABLE_NAME", "A.PKCOLUMN_NAME", "A.FKTABLE_CAT", ".FKTABLE_SCHEM", "A.FKTABLE_NAME", "A.FKCOLUMN_NAME", "A.KEY_SEQ", ".UPDATE_RULE", ".DELETE_RULE", "A.FK_NAME", ".PK_NAME", ".DEFERRABILITY"
"myrep123", <Null>, "test_parent", "parent_oid", "myrep123", <Null>, "test_child", "parent_oid", 1, 3, 3, "FK_PARENT_OID", "PRIMARY", 7
"myrep123", <Null>, "test_parent", "parent_oid", "myrep123", <Null>, "test_child", "parent_oid", 1, 3, 3, "FK_PARENT_OID", "PRIMARY", 7
2 rows fetched from 14 columns.

PkCatalogName restrictions: "PKCatalogName cannot contain a string search pattern."
Behavior is the same regardless of the value in xxCatalogName fields (actual catalog or empty string). Same goes for xxSchemaName.
Note: In SQLForeignKeys schema parameters are disregarded.

Judging by the queries sent, there is something wrong with *first* I__S query. It is that query that returns 2 rows instead of one:
SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM,A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM,A.TABLE_NAME AS FKTABLE_NAME,A.COLUMN_NAME AS FKCOLUMN_NAME,A.ORDINAL_POSITION AS KEY_SEQ,CASE WHEN R.UPDATE_RULE = 'CASCADE' THEN 0 WHEN R.UPDATE_RULE = 'SET NULL' THEN 2 WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE = 'SET RESTRICT' THEN 1 WHEN R.UPDATE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,CASE WHEN R.DELETE_RULE = 'CASCADE' THEN 0 WHEN R.DELETE_RULE = 'SET NULL' THEN 2 WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4 WHEN R.DELETE_RULE = 'SET RESTRICT' THEN 1 WHEN R.DELETE_RULE = 'SET NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE,A.CONSTRAINT_NAME AS FK_NAME,(SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = REFERENCED_TABLE_SCHEMA AND TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,7 AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME,TABLE_NAME) JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND R.TABLE_NAME = B.TABLE_NAME AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA) WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND A.REFERENCED_TABLE_SCHEMA = 'myrep123' AND A.REFERENCED_TABLE_NAME = 'test_parent' AND A.TABLE_SCHEMA = 'myrep123' AND A.TABLE_NAME = 'test_child' ORDER BY FKTABLE_CAT, FKTABLE_NAME, KEY_SEQ, PKTABLE_NAME;

Patch for the bug

Attachment: bug49660.patch (application/octet-stream, text), 2.72 KiB.

Fixed in the patch for Bug#51422. Closing