MySQL Bugs: #116113: DatabaseMetaData#getTables doesn't work properly for delimited identifiers

Bug #116113	DatabaseMetaData#getTables doesn't work properly for delimited identifiers
Submitted:	15 Sep 2024 17:45	Modified:	15 Jan 15:59
Reporter:	Ashhar Hasan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	9.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
DatabaseMetaData#getTables doesn't work if the tableNamePattern is a value like "`back``quoted`" and a table exists on the server with that name.

I've attached a java class to reproduce this issue. Trying with or without the `pedantic` connection property leads to same result.

How to repeat:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

class Scratch
{
    public static void main(String[] args)
    {
        Properties properties = new Properties();
        properties.put("user", "test");
        properties.put("password", "test");
        properties.put("pedantic", "true");
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:33018/tpch", properties)) {
            try (Statement stmt = connection.createStatement()) {
                stmt.execute("CREATE TABLE IF NOT EXISTS ```back````quoted``` (```back````quoted``` bigint)");
            }
            try (ResultSet rs = connection.getMetaData().getTables(
                    "tpch",
                    null,
                    "`back``quoted`",
                    null)) {
                while (rs.next()) {
                    System.out.println("table");
                    System.out.println(rs.getString("TABLE_NAME"));
                }
            }
            try (ResultSet rs = connection.getMetaData().getColumns(
                    "tpch",
                    null,
                    "`back``quoted`",
                    null)) {
                while (rs.next()) {
                    System.out.println("column");
                    System.out.println(rs.getString("COLUMN_NAME"));
                }
            }
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

Hello Ashhar Hasan,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Hello Ashhar,

The tableNamePattern argument you are passing to getTables should be ```back````quoted```. The server treats `tableName`, the same as tablename, so we need a way to make them equivalent, meaning that Connector/J has to first unquote the tableName. There actually exists bug here, it lies in getColumns, as it is not unquoting the tableNamePattern given.
Thank you for your report.

Hi Ashhar,

Axyoan's explanation is true for default mode (pedantic=false), however, you are right with regard to the expected results when "pedantic=true".

Several metadata methods are currently handling quoted identifiers differently. They shall all be readjusted.

Posted by developer:
 
Added the following entry to the C/J 9.2.0 changelog: 

"Behaviors of a few of the DatabaseMetaData methods were inconsistent when handling parameters of identifiers quoted with backticks (`). With this patch, identifiers quoted by backticks are always unquoted when the connection is in non-pedantic mode, but they are always taken as-is (with the backticks escaped as parts of the identifier) when the connection is in pedantic mode."