[CONJ-55] DBMeta stores*QuotedIdentifiers returns inconsistent values between MariaDB and MySQL JDBC drivers. Created: 2013-07-08  Updated: 2013-07-16  Resolved: 2013-07-16

Status: Closed
Project: MariaDB Connector/J
Component/s: None
Affects Version/s: 1.1.3
Fix Version/s: 1.1.4

Type: Bug Priority: Minor
Reporter: Jody Grassel Assignee: Vladislav Vaintroub
Resolution: Fixed Votes: 0
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.

 Comments   
Comment by Vladislav Vaintroub [ 2013-07-09 ]

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

Comment by Jody Grassel [ 2013-07-09 ]

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.

Comment by Jody Grassel [ 2013-07-09 ]

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.

Comment by Vladislav Vaintroub [ 2013-07-09 ]

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.

Comment by Vladislav Vaintroub [ 2013-07-09 ]

More on identifier case sensitivity : http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

Comment by Jody Grassel [ 2013-07-09 ]

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

Comment by Vladislav Vaintroub [ 2013-07-09 ]

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

Comment by Vladislav Vaintroub [ 2013-07-16 ]

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.
Generated at Thu Feb 08 03:12:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.