MySQL Bugs: #23308: getTables() throws exception with null catalog

Bug #23308	getTables() throws exception with null catalog
Submitted:	15 Oct 2006 19:59	Modified:	10 Jan 2007 22:30
Reporter:	Alexander Hristov (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.3	OS:	Any (Any)
Assigned to:	Mark Matthews	CPU Architecture:	Any
Tags:	getTables, information schema

Description:
The JDBC api docs state that when specifying a null catalog in getTables(), "null means that the catalog name should not be used to narrow the search".
However, when the following is simultanously true:
- nullCatalogMeansCurrent=false (for JDBC compliance) AND
- useInformationSchema=true AND
- catalog = null

the driver raises an SQLException with 'catalog' parameter can not be null. This behaviour imho is incorrect, and is inconsistent with the result obtained when useInformationSchema=false, in which case the driver behaves properly

How to repeat:
import java.util.Properties;
import java.sql.*;

public class Test {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Properties properties = new Properties();
    properties.setProperty("user","...");
    properties.setProperty("password","...");
    properties.setProperty("nullCatalogMeansCurrent","false");
    properties.setProperty("pedantic","true");
    properties.setProperty("nullNamePatternMatchesAll","false");
    properties.setProperty("useInformationSchema","true");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test",properties);
    DatabaseMetaData db = con.getMetaData();
    ResultSet rs = db.getTables(null,null,"%",null);
    while (rs.next()) {
      System.out.println(rs.getString("TABLE_CAT")+" "+rs.getString("TABLE_SCHEM")+" "+rs.getString("TABLE_NAME"));
    }
  }
}

Suggested fix:
In com.mysql.jdbc.DatabaseMetaDataUsingInfoSchema.java, in the getTables() method, replace

    if (catalog == null) {
      if (!this.conn.getNullCatalogMeansCurrent()) {
        throw SQLError.createSQLException("'catalog' parameter can not be null",
            SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
      } 

      catalog = this.database;
    }

with

    if (catalog == null) {
      if (!this.conn.getNullCatalogMeansCurrent()) {
        catalog = "%"
      } else
        catalog = this.database;
    }

Oops.. a ; is missing after the catalog="%"...

The same error is found elsewhere:

getColumnPrivileges()
getColumns()
getCrossReference() [twice]
getExportedKeys()
getImportedKeys()
getPrimaryKeys()
getProcedures()

and I believe the same fix type can be applied, except in

getColumnPrivileges(), where the statement is created with a strict comparison (TABLE_SCHEMA = ?) instead of (TABLE_SCHEMA LIKE ?). 

getCrossReference(), same reason. 

getExportedKeys(), same reason

getImportedKeys(), same reason

getProcedures(). Now getProcedures is an interesting case : only uses the nullCatalogMeansCurrent property if the catalog is "", but if it is null, it assumes that the catalog is the current database, regardless of the nullCatalogMeansCurrent property. I don't know if this is a fix to some wierd MySQL Server concerning the ROUTINES information schema (doesn't look so), but  the code just doesn't look right:

    if (catalog == null) {
      db = this.database;
    } else if (catalog.length() > 0) {
      db = catalog;
    } else {
      if (!this.conn.getNullCatalogMeansCurrent()) {
        throw SQLError.createSQLException("'catalog' parameter can not be null",
            SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
      }

      catalog = null;
      db = null;
    }

and by the way catalog is not used further in that method, so the catalog=null assignment is superfluous anyway.

Probably it should be:

    if (catalog == null) {
      if (!this.conn.getNullCatalogMeansCurrent()) {
      	db = "%";
      else
      	db = this.database;
    } else 
      db = catalog;
    }

Hi Alexander and thanks for your report. I will have to consult on this one.

Fixed as part of changes for BUG#23304.

Note that the fixes are only in the source repository, and present in a nightly build of 5.0, they didn't make it into 5.0.4. See http://downloads.mysql.com/snapshots.php#connector-j for nightly snapshot builds.