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
Triage: D3 (Medium)

[23 Feb 2010 14:16] Lawrenty Novitsky
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
[23 Feb 2010 14:18] Tonci Grgin
Lawrin, I believe this is a duplicate...
[25 Feb 2010 10:28] Lawrenty Novitsky
Patch that uses in query "correct" ways of identifying foreign and primary keys

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

[25 Feb 2010 10:31] Lawrenty Novitsky
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.

[25 Feb 2010 10:36] Lawrenty Novitsky
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.

[25 Feb 2010 11:02] Lawrenty Novitsky
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
[23 Mar 2010 20:47] Jim Winstead
The last 'optimized' patch looks fine.
[8 Apr 2010 9:26] Lawrenty Novitsky
pushed to working_tree rev#885
[20 May 2010 18:57] Lawrenty Novitsky
pushed to trunk
[30 Jun 2010 10:19] Tony Bedford
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.