MySQL Bugs: #4518: SQLForeignKeys returns to many foreign key

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

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'.

In my example NAME2 is the wrong table name, it must be NAME11

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;

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.

Test program to demonstrate the problem

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

Please reverify w/ 3.51.12 as there are fixes in there that most likely address this bug.

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` )

Fix problems with SQLForeignKeys

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

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.

The fix has been committed to the repository, and will be included in the next release (3.51.15). Thanks for the report.

A note has been added to the 3.51.15 changelog.