Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-55

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

    Details

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

      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.

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            wlad Vladislav Vaintroub added a comment -

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

            Show
            wlad Vladislav Vaintroub added a comment - There is no MySQL JDBC driver. That driver is called ConnectorJ
            Hide
            fyrewyld 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
            fyrewyld 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
            fyrewyld 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
            fyrewyld 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
            wlad 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
            wlad 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
            wlad Vladislav Vaintroub added a comment -
            Show
            wlad Vladislav Vaintroub added a comment - More on identifier case sensitivity : http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html
            Hide
            fyrewyld Jody Grassel added a comment -

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

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

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

            Show
            wlad Vladislav Vaintroub added a comment - - edited Not through DatabaseMetaData, currently. You can use "select @@lower_case_table_names" to obtain it.
            Hide
            wlad 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
            wlad 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:
                wlad Vladislav Vaintroub
                Reporter:
                fyrewyld Jody Grassel
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: