Uploaded image for project: '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
    • 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.

        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: