Bug #4518 SQLForeignKeys returns to many foreign key
Submitted: 12 Jul 2004 16:26 Modified: 30 Apr 2007 14:01
Reporter: Jochen Willinghöfer Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51 / 3.52 OS:Any (any)
Assigned to: Jim Winstead CPU Architecture:Any

[12 Jul 2004 16:26] Jochen Willinghöfer
Description:
SQLForeignKeys returns to many foreign key for tables with similar names

How to repeat:
Database with table name NAME1 and table name NAME11, both tables with foreign keys. SQLForeignKeys for table name NAME1 returns foreign keys for NAME1 and NAME2 because SQLForeignKeys use function mysql_table_status which use function my_append_wild. Function my_append_wild adds % to the table name.

Suggested fix:
Add new parameter 'int bWild' to function my_append_wild:

static void my_append_wild(char *to, 
                           char *end, 
                           const char *wild,
						   int bWild) 
// New parameter bWild
{
  end-= 5;         /* Some extra */
  to= strmov(to," like '");
  
  if (wild)
  {
    while (*wild && to < end)
    {
      if (*wild == '\\' || *wild == '\'')
        *to++= '\\';
      *to++= *wild++;
    }
  }
// Use new parameter
  if(bWild != 0)
  {
	*to++= '%';        /* Nicer this way */
  }
//
  to[0]= '\'';
  to[1]= 0;
}

Add for every call to my_append_wild new integer value '1' except the call in function mysql_table_status which add new integer value '0'.
[15 Jul 2004 8:46] Jochen Willinghöfer
In my example NAME2 is the wrong table name, it must be NAME11
[31 Jul 2004 0:43] Timothy Smith
I'm attaching a test case that demonstrates the problem - check the Files section.  First create the three tables, and then run the C program.  I get this result, on FreeBSD 5.2, latest MyODBC and MySQL 4.0.21:

tim@sand:m/odbc/t$ ./bug4518 
Listing all foreign keys in table 'ref1':
ref1 ( `fk1` )--> obj` ( `id` )
ref11 ( `fk11` )--> obj` ( `id` )
tim@sand:m/odbc/t$ 

Here are the tables:

CREATE TABLE obj (
  id int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) TYPE=InnoDB;

CREATE TABLE ref1 (
  fk1 int(11) NOT NULL default '0',
  KEY fk1 (fk1),
  CONSTRAINT `ref1_ibfk_1` FOREIGN KEY (`fk1`) REFERENCES `obj` (`id`)
) TYPE=InnoDB;

CREATE TABLE ref11 (
  fk11 int(11) NOT NULL default '0',
  KEY fk11 (fk11),
  CONSTRAINT `ref11_ibfk_1` FOREIGN KEY (`fk11`) REFERENCES `obj` (`id`)
) TYPE=InnoDB;
[31 Jul 2004 1:01] Timothy Smith
It looks like the  '%' in the my_wild_append() function should just be taken out.  After skimming over the code to see where it's used, I don't think it serving the intended purpose.  It seems that it was meant as a convenience, but at this point it's introducing bugs in several places and I don't think it adds any useful functionality.
[31 Jul 2004 1:03] Timothy Smith
Test program to demonstrate the problem

Attachment: bug4518.c (application/octet-stream, text), 8.03 KiB.

[6 Oct 2005 21:41] Mark Matthews
Please reverify w/ 3.51.12 as there are fixes in there that most likely address this bug.
[9 Oct 2005 10:49] Vasily Kishkin
It's verified on 3.51.12. There is same result.

Listing all foreign keys in table 'ref1':
ref1 ( `fk1` )--> obj` ( `id` )
ref11 ( `fk11` )--> obj` ( `id` )
[16 Mar 2007 0:32] Jim Winstead
Fix problems with SQLForeignKeys

Attachment: bug4518.diff (application/octet-stream, text), 18.45 KiB.

[16 Mar 2007 0:36] Jim Winstead
The attached patch still has one shortcoming, in that it does not properly escape _ and % in table names, so may return too many results for such tables. There will be other catalog functions with the same issue when we start to clean all of those up, so I've put off implementing a fix until we get there.

The patch also fixes the quotes left around the column names in the results, as well as populating FKTABLE_CAT correctly.
[9 Apr 2007 18:25] Jim Winstead
The fix has been committed to the repository, and will be included in the next release (3.51.15). Thanks for the report.
[30 Apr 2007 14:01] MC Brown
A note has been added to the 3.51.15 changelog.