MySQL Bugs: #51422: SQLForeignKeys returns rows for FK that points to unique constraint field

Bug #51422	SQLForeignKeys returns rows for FK that points to unique constraint field
Submitted:	23 Feb 2010 14:16	Modified:	30 Jun 2010 10:19
Reporter:	Lawrenty Novitsky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any

Description:
From http://msdn.microsoft.com/en-us/library/ms709315(VS.85).aspx

"If *PKTableName contains a table name, SQLForeignKeys returns a result set that contains the primary key of the specified table and all the foreign keys that refer to it. The list of foreign keys in other tables does not include foreign keys that point to unique constraints in the specified table.

If *FKTableName contains a table name, SQLForeignKeys returns a result set that contains all the foreign keys in the specified table that point to primary keys in other tables, and the primary keys in the other tables to which they refer. The list of foreign keys in the specified table does not contain foreign keys that refer to unique constraints in other tables."

MyODBC includes in result foreign keys referring to unique constraint field

How to repeat:
CREATE TABLE `uu` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  UNIQUE KEY `uukey` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `uu2` (
  `id` int(10) unsigned NOT NULL,
  `uuid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uufk` (`uuid`),
  CONSTRAINT `uu2_ibfk_1` FOREIGN KEY (`uuid`) REFERENCES `uu` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

run SQLForeignKeys on any of tables as PK or FK tables respectively and note that resultset is not empty, but contains uu2_ibfk_1 key row

Suggested fix:
change sql query to include fk pointing to primary keys only

Lawrin, I believe this is a duplicate...

Patch that uses in query "correct" ways of identifying foreign and primary keys

Attachment: bug51422incl49660_correct.patch (application/octet-stream, text), 5.45 KiB.

Same as "correct", but exploiting fact, that for foreign keys only(?) referenced_* fields in the KEY_COLUMN_USAGE are not NULL

Attachment: bug51422incl49660_correct_opt.patch (application/octet-stream, text), 5.79 KiB.

Same as "optimized correct", but uses for identifying of primary key uses the fact that constraint name for PK is 'PRIMARY'

Attachment: bug51422incl49660_optimized.patch (application/octet-stream, text), 5.57 KiB.

Base revision is #866(if i didn't miss anything - the current revision in the trunk).
Patches include patch for 49660. Patches on top of 49660 are available on request :)

As I tried to explain in comments to the patches, "correct" patch does all required joins to check B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND C.CONSTRAINT_TYPE='PRIMARY KEY'
"correct optimized" assumes that for foreign keys only REFERENCED_* fields in the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table are not NULL
"optimized" to verify that key is primary uses the fact that constraint name of all primary keys is "PRIMARY"

My bet is "optimized" patch :) but giving here all variants.

i did some very simple estimation of query speed improvement. so "correct" query is on ~1/3 faster than original(or original is on 50% slower than "correct"), "correct optimized" is ~20% faster than "correct"(almost 2 times faster than original), and "optimized" is almost 40% faster than "optimized". or in absolute numbers on my system on my test db:
"original"    .75-.9s
"correct"     .5 -.51s
"correct opt" .39-.41s
"optimized"   .25-.26s

The last 'optimized' patch looks fine.

pushed to working_tree rev#885

pushed to trunk

An entry has been added to the 5.1.7 changelog:

SQLForeignKeys() did not return the correct information. The list of foreign keys in other tables should not have included foreign keys that point to unique constraints in the specified table.