MariaDB Client Library for Java Applications
  1. MariaDB Client Library for Java Applications
  2. CONJ-55

DBMeta stores*QuotedIdentifiers returns inconsistent values between MariaDB and MySQL JDBC drivers.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: jdbc-1.1.3
    • Fix Version/s: jdbc-1.1.4
    • Labels:
      None
    • Environment:
      Server-Linux, Client-MacOS
    • Global Rank:
      3313

      Description

      Simple to reproduce:

      DatabaseMetaData dbMeta = con.getMetaData();

      System.out.println("dbMeta.storesMixedCaseQuotedIdentifiers() = " + dbMeta.storesMixedCaseQuotedIdentifiers());
      System.out.println("dbMeta.storesUpperCaseQuotedIdentifiers() = " + dbMeta.storesUpperCaseQuotedIdentifiers());
      System.out.println("dbMeta.storesLowerCaseQuotedIdentifiers() = " + dbMeta.storesLowerCaseQuotedIdentifiers());

      But depending on the JDBC driver used, the results are different:

      Talking to MariaDB with a MySQL 5.1.18 JDBC Driver:
      dbMeta.storesMixedCaseQuotedIdentifiers() = true
      dbMeta.storesUpperCaseQuotedIdentifiers() = true
      dbMeta.storesLowerCaseQuotedIdentifiers() = false

      Talking to MariaDB with the MariaDB 1.1.3 JDBC Driver:
      dbMeta.storesMixedCaseQuotedIdentifiers() = false
      dbMeta.storesUpperCaseQuotedIdentifiers() = true
      dbMeta.storesLowerCaseQuotedIdentifiers() = false

      Talking to MySQL with the MySQL JDBC Driver:
      dbMeta.storesMixedCaseQuotedIdentifiers() = false
      dbMeta.storesUpperCaseQuotedIdentifiers() = true
      dbMeta.storesLowerCaseQuotedIdentifiers() = true

      Talking to MySQL with the MariaDB JDBC Driver:
      dbMeta.storesMixedCaseQuotedIdentifiers() = false
      dbMeta.storesUpperCaseQuotedIdentifiers() = true
      dbMeta.storesLowerCaseQuotedIdentifiers() = false

      I would have expected the return results to be the same regardless which JDBC driver implementation was used.

        Activity

        Hide
        Vladislav Vaintroub added a comment -

        There is no MySQL JDBC driver. That driver is called ConnectorJ

        Show
        Vladislav Vaintroub added a comment - There is no MySQL JDBC driver. That driver is called ConnectorJ
        Hide
        Jody Grassel added a comment -

        http://dev.mysql.com/downloads/connector/j/ – "MySQL Connector/J is the official JDBC driver for MySQL."

        So fine. Why is the behavior different between the two drivers? That's what I'm asking.

        Show
        Jody Grassel added a comment - http://dev.mysql.com/downloads/connector/j/ – "MySQL Connector/J is the official JDBC driver for MySQL." So fine. Why is the behavior different between the two drivers? That's what I'm asking.
        Hide
        Jody Grassel added a comment - - edited

        Further, what should be the return values? My database has a table, "nsd c map" (the exact string produced by the "show tables" mysql client command).

        Using "NSD C MAP" as the table name argument for dbMeta.getColumns() returned an empty ResultSet, while using "nsd c map" returned a ResultSet with the needed data. The tool I'm using look at the return values for stores*CaseQuotedIdentifiers to determine whether toUpper or toLower operations are needed for the dbMeta.getColumns() call.

        With dbMeta.storesUpperCaseQuotedIdentifiers() returning true, this tells the tool to upper case the table name when it seems that would cause dbMeta.getColumns() to fail returning useful data.

        It almost seems like storesMixedCaseQuotedIdentifiers() should true, with the other two (stores[Upper|Lower]CaseQuotedIdentifiers) should return false.

        Show
        Jody Grassel added a comment - - edited Further, what should be the return values? My database has a table, "nsd c map" (the exact string produced by the "show tables" mysql client command). Using "NSD C MAP" as the table name argument for dbMeta.getColumns() returned an empty ResultSet, while using "nsd c map" returned a ResultSet with the needed data. The tool I'm using look at the return values for stores*CaseQuotedIdentifiers to determine whether toUpper or toLower operations are needed for the dbMeta.getColumns() call. With dbMeta.storesUpperCaseQuotedIdentifiers() returning true, this tells the tool to upper case the table name when it seems that would cause dbMeta.getColumns() to fail returning useful data. It almost seems like storesMixedCaseQuotedIdentifiers() should true, with the other two (stores [Upper|Lower] CaseQuotedIdentifiers) should return false.
        Hide
        Vladislav Vaintroub added a comment -

        It is complicated, and not consistent between platforms.

        The casing of database and table names (how they are stored) is controlled with variable 'lower_case_table_names', which has 3 values 0 (stored mixed case, compare insensitive), 1 (store lower case, case-insensitive compare), or 2 (store mixed case, compare case-insensitive). IIRC, this lowercasing does not apply to column names though, that iirc are always mixed case.

        storesUpperCase is definitely wrong. We (and ConnectorJ) should return mixed or lower.

        Show
        Vladislav Vaintroub added a comment - It is complicated, and not consistent between platforms. The casing of database and table names (how they are stored) is controlled with variable 'lower_case_table_names', which has 3 values 0 (stored mixed case, compare insensitive), 1 (store lower case, case-insensitive compare), or 2 (store mixed case, compare case-insensitive). IIRC, this lowercasing does not apply to column names though, that iirc are always mixed case. storesUpperCase is definitely wrong. We (and ConnectorJ) should return mixed or lower.
        Hide
        Vladislav Vaintroub added a comment -
        Show
        Vladislav Vaintroub added a comment - More on identifier case sensitivity : http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html
        Hide
        Jody Grassel added a comment -

        Is there a way for a JDBC client to obtain the value of this setting
        through DBMeta?

        Show
        Jody Grassel added a comment - Is there a way for a JDBC client to obtain the value of this setting through DBMeta?
        Hide
        Vladislav Vaintroub added a comment - - edited

        Not through DatabaseMetaData, currently. You can use "select @@lower_case_table_names" to obtain it.

        Show
        Vladislav Vaintroub added a comment - - edited Not through DatabaseMetaData, currently. You can use "select @@lower_case_table_names" to obtain it.
        Hide
        Vladislav Vaintroub added a comment -

        I fixed this one. The solution is think fairly consistent translation of lower_case_table_names parameter to JDBC spec*

        storesUpper = false
        storesMixed = (lower_case_table_names == 2)
        storesLower = (lower_case_table_names == 1)
        supportsMixed = ((lower_case_table_names == 0)
        Quoted identifiers are treated the same way as unquoted with regard to case sensitivity.

        • This does not mean the behaviour will be consistent to ConnectorJ. JDBC conformance has higher prio.
        Show
        Vladislav Vaintroub added a comment - I fixed this one. The solution is think fairly consistent translation of lower_case_table_names parameter to JDBC spec* storesUpper = false storesMixed = (lower_case_table_names == 2) storesLower = (lower_case_table_names == 1) supportsMixed = ((lower_case_table_names == 0) Quoted identifiers are treated the same way as unquoted with regard to case sensitivity. This does not mean the behaviour will be consistent to ConnectorJ. JDBC conformance has higher prio.

          People

          • Assignee:
            Vladislav Vaintroub
            Reporter:
            Jody Grassel
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: