Thread: BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name

The following bug has been logged online:

Bug reference:      3894
Logged by:          Adam Hardy
Email address:      adam.hardy@cyberspaceroad.com
PostgreSQL version: 8.2.5
Operating system:   Linux
Description:        JDBC DatabaseMetaData.getTables is inconsistently
case-sensitive with schema name
Details:

I created a schema with this cmd:

create schema DEV;

set search_path to DEV;

but then DatabaseMetaData.getTables(null, "DEV", "%", new String[]
{"TABLE"}) returned an empty resultset.

However giving it the schema name "dev" returns the full resultset.

It took me 4 hours to isolate this problem which was hidden in the code of
DbUnit.

On Tue, 22 Jan 2008, Adam Hardy wrote:

>
> The following bug has been logged online:
>
> Bug reference:      3894
> Description:        JDBC DatabaseMetaData.getTables is inconsistently
> case-sensitive with schema name
> Details:
>
> create schema DEV;
>
> but then DatabaseMetaData.getTables(null, "DEV", "%", new String[]
> {"TABLE"}) returned an empty resultset.
>
> However giving it the schema name "dev" returns the full resultset.
>

This is by design.  While SQL provides case folding and quoting rules
those are not the same as the JDBC driver's rules.  In SQL an unquoted
identifier is case folded while quoted identifiers are not.  To provide
the most straightforward mapping for the JDBC driver, we require an exact
match because quoting in Java String objects isn't the same as SQL.  It
would be odd to say getTable(..., "\"Dev\"",...) to imply that you wanted
a case sensitive match.  Even odder would it be to do a search for a
schema with a quote in it's name using SQL identifier rules in Java code.

Kris Jurka

Kris Jurka on 23/01/08 08:51, wrote:
> On Tue, 22 Jan 2008, Adam Hardy wrote:
>> The following bug has been logged online:
>>
>> Bug reference:      3894
>> Description:        JDBC DatabaseMetaData.getTables is inconsistently
>> case-sensitive with schema name
>> Details:
>>
>> create schema DEV;
>>
>> but then DatabaseMetaData.getTables(null, "DEV", "%", new String[]
>> {"TABLE"}) returned an empty resultset.
>>
>> However giving it the schema name "dev" returns the full resultset.
>>
>
> This is by design.  While SQL provides case folding and quoting rules
> those are not the same as the JDBC driver's rules.  In SQL an unquoted
> identifier is case folded while quoted identifiers are not.  To
> provide the most straightforward mapping for the JDBC driver, we
> require an exact match because quoting in Java String objects isn't
> the same as SQL.  It would be odd to say getTable(..., "\"Dev\"",...)
> to imply that you wanted a case sensitive match.  Even odder would it
> be to do a search for a schema with a quote in it's name using SQL
> identifier rules in Java code.
>
> Kris Jurka
>
Hi Kris,

it seems to me from what you just said that PostgreSQL server and JDBC
driver require the schema name to be lower case deliberately, and that
any given name that is not all lower case is converted to lower case by
the server or the driver. Am I correct?

Thanks
Adam

Adam Hardy написа:
[...]
> it seems to me from what you just said that PostgreSQL server and JDBC
> driver require the schema name to be lower case deliberately, and that
> any given name that is not all lower case is converted to lower case by
> the server or the driver. Am I correct?


http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS,
(especially the last paragraph of 4.1.1.),



--
Milen A. Radev