Bug #63992 getTables did not return a result for table names with a dot in it.
Submitted: 11 Jan 2012 10:20 Modified: 7 Sep 2012 15:23
Reporter: Martin Schaaf Email Updates:
Status: In progress Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.13 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any

[11 Jan 2012 10:20] Martin Schaaf
Description:
DatabaseMetaData.getTables(null, null, "a.b", "%"); returns no tables in case the table exists 
DatabaseMetaData.getTables(null, null, "`a.b`", "%"); returns the table table

but 

DatabaseMetaData.getPrimaryKeys(null, null, "a.b") returns a primary key
DatabaseMetaData.getPrimaryKeys(null, null, "`a.b`") returns no primary key

I think the problem comes with revision http://bazaar.launchpad.net/~mark-mysql/connectorj/5.1/revision/993

I would not expect to quote a table name that is used in method if the catalog and schema is given with separate arguments.

How to repeat:
1. create a table with a primary key and a dot in the table name.
2. use DatabaseMetaData.getTables(null, null, "a.b", "%") to retrieve the table
[11 Jan 2012 12:00] Tonci Grgin
Hi Martin and thanks for your report.

I remember introducing this change and I do not see how I can make it cover all the corner cases. So I decided to cover the most common one in SQL world which passing "db.table" as an argument to function call. This is usual while naming table with DOT is not.

What's your take on this?
[12 Jan 2012 11:35] Martin Schaaf
Hej Tonci,

For now our solution is to advice customers to use the driver version 5.1.12. Let me explain what our problem is with it and why we don't like to circumvent this in our code.

We use the jdbc api and in this case the DatabaseMetaData.getTables. We use the same codebase for different databases to retrieve tables. If I quote now the database it will work with the mysql jdbc connector but no longer with the e.g. the hsql org postgress jdbc connector. So I don't have anything from using a API that abstracts database usage.

The second point is that the quoting is only necessary for the DatabaseMetaData.getTables. So the API is inconsistent.

The third point is that the parameter is called a tableNamePattern and is not saying a full qualified tableName. How would you interpret "a.%' and 'a%'. They are the same or different?

And forth to fix it in our code would mean bring in special code for mysql in a codebase that did not depend on the underlaying database.

Thank you for your time and help
martin
[12 Jan 2012 16:00] Tonci Grgin
Martin, I will take your observation into consideration. Can't just rollback the change as it was made for a good reason.
[7 Sep 2012 15:23] Alexander Soklakov
There is another open bug#65871 related to complex identifiers. We should resolve this problem for both.
[16 Dec 2017 11:44] Nils Petzall
Can someone elaborate on the good reason for the change.

I might be going by faulty information since I'm looking at git blame.

Changes where done to fix https://bugs.mysql.com/bug.php?id=57697 which seems to be a double quotation issue.

The fix does more then check if the supplied tableNamePattern is quoted or not.