Bug #90887 DatabaseMetaDataUsingInfoSchema#getTables fails if method arguments are null
Submitted: 16 May 2018 10:39 Modified: 17 Sep 2018 20:37
Reporter: Maksim Gorbunkov Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.11 OS:Windows (Windows 10 x64)
Assigned to: CPU Architecture:x86

[16 May 2018 10:39] Maksim Gorbunkov
Description:
The com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema#getTables method fails with an exception if all passed method arguments are null. 

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME' at line 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118) ~[mysql-connector-java-8.0.11.jar:8.0.11]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) ~[mysql-connector-java-8.0.11.jar:8.0.11]
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.11.jar:8.0.11]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960) ~[mysql-connector-java-8.0.11.jar:8.0.11]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1019) ~[mysql-connector-java-8.0.11.jar:8.0.11]
        at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.executeMetadataQuery(DatabaseMetaDataUsingInfoSchema.java:62) ~[mysql-connector-java-8.0.11.jar:8.0.11]
        at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.getTables(DatabaseMetaDataUsingInfoSchema.java:835) ~[mysql-connector-java-8.0.11.jar:8.0.11]

An interface documentation states that null values for these parameters are possible. 

The reason for the exception is the invalid SQL query that contains "WHERE ORDER BY ..." statement without "where" conditions. The testcase is attached in the "How to repeat" field.

MySQL database version used for tests is 8.0.11 (run with the Docker)

How to repeat:
import com.mysql.cj.jdbc.util.BaseBugReport;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.util.Properties;

public class MyBugReport extends BaseBugReport {

    @Override
    public void setUp() throws Exception {

    }

    @Override
    public void tearDown() throws Exception {

    }

    @Override
    public void runTest() throws Exception {
        Properties props = new Properties();
        props.setProperty("user", "root");
        props.setProperty("password", "root");
        Connection connection = getConnection("jdbc:mysql:///test?useSSL=false", props);
        DatabaseMetaData metaData = connection.getMetaData();
        metaData.getTables(null, null, null, null);
    }

    public static void main(String[] args) throws Exception {
        new MyBugReport().runTest();
    }
}
[16 May 2018 10:51] Maksim Gorbunkov
JDK version is 1.8.0_171
[16 May 2018 14:34] Chiranjeevi Battula
Hello  Maksim,

Thank you for the bug report and testcase.
Verified this behavior on MySQL Connector / J 8.0.11.

Thanks,
Chiranjeevi.
[16 May 2018 14:34] Chiranjeevi Battula
Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1019)
	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.executeMetadataQuery(DatabaseMetaDataUsingInfoSchema.java:62)
	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.getTables(DatabaseMetaDataUsingInfoSchema.java:835)
	at mysqljavabugs.Bug_90887.main(Bug_90887.java:32)
C:\Users\cbattula\AppData\Local\NetBeans\Cache\8.2\executor-snippets\run.xml:53: Java returned: 1
BUILD FAILED (total time: 2 seconds)
[21 May 2018 11:22] Alexander Soklakov
Hi Maksim, thanks for the report.

Though the error message could be better but the exception is expected in case you pass null as a tableNamePattern to DatabaseMetaData.getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]).

The javadoc says: "tableNamePattern a table name pattern; must match the table name as it is stored in the database". No special meaning for null is specified, in opposite to other parameters of this method.

So, I keep this report as verified but only to fix the error message. Please ensure that your code does not pass null value to tableNamePattern.
[7 Sep 2018 10:32] Alexander Soklakov
Bug#92296 is marked as a duplicate of this one.
[7 Sep 2018 10:36] Alexander Soklakov
Actually, the JDBC specification (API) DOES say that `tableNamePattern` can be null. In end of the description of the interface `DatabaseMetaData` there is:
 
"Some DatabaseMetaData methods take arguments that are String patterns. These arguments all have names such as fooPattern. Within a pattern String, "%" means match any substring of 0 or more characters, and "_" means match any one character. Only metadata entries matching the search pattern are returned. If a search pattern argument is set to null, that argument's criterion will be dropped from the search."
 
So, a null pattern means all. The c/J 8.0 will be fixed to support it, the same as is was in c/J 5.1.
[17 Sep 2018 20:37] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.13:

"Connector/J threw a SQLSyntaxErrorException when the parameter tableName for DatabaseMetaDataUsingInfoSchema.getTables() had a null argument."