MySQL Bugs: #57697: MetaDataRegressionTest.testCharacterSetForDBMD causes false negative

Bug #57697	MetaDataRegressionTest.testCharacterSetForDBMD causes false negative
Submitted:	24 Oct 2010 19:41	Modified:	3 Nov 2010 17:50
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MetaDataRegressionTest.testCharacterSetForDBMD does the following:

- generates tableName from 3 unicode symbols surrounded by quote characters;
- creates the table;
- attempts to see the table using SHOW FULL TABLES like 'tableName'
  => fails (asserts) getting an empty result set

Unicode symbols apart, what happens is that the test creates table as
CREATE TABLE `t` ...
and then expects to find it using
SHOW TABLES LIKE '`t`'

This seems to be a valid failure, the expectation in the test is unjustified -- backticks are not a part of the created table name, and should not be present in the search pattern. 

The test only fails with server 5.5 because it has a server version check (with a rather unclear comment 'server is broken, fixed in 5.2/6.0') and is not executed at all for 5.1.

How to repeat:
Run regression.MetaDataRegressionTest.testCharacterSetForDBMD
or inspect its code.

Suggested fix:
< String tableName = quoteChar + "\u00e9\u0074\u00e9" + quoteChar;
< createTable(tableName, "(field1 int)");
< this.rs = this.conn.getMetaData().getTables(this.conn.getCatalog(), 
< null, tableName, new String[] {"TABLE"});
< assertEquals(true, this.rs.next());

> String tableName = "\u00e9\u0074\u00e9";
> String quotedTableName = quoteChar + tableName + quoteChar;
> createTable(quotedTableName, "(field1 int)");
> this.rs = this.conn.getMetaData().getTables(this.conn.getCatalog(), 
> null, tableName, new String[] {"TABLE"});
> assertEquals(true, this.rs.next());

I can verify that the proposed fix does work in windows.  Just need to make sure it works with the pre-5.2 server versions and that version test can be removed also.

Problem seems to be in DatabaseMetaData.getTables around line 4937:
	results = stmt.executeQuery("SHOW TABLES FROM "
		+ quotedId + catalogStr.toString()
		+ quotedId + " LIKE '"
		+ tableNamePat + "'");

Fixed for useInformationSchema=false (code above).
Now needs check for useInformationSchema=true.

Fixed both cases. Patch needs to go through regression testing before pushing.

Final patch pushed up to revision 993.