Thread: BUG: DatabaseMetaData.getColumns isn't case insensitive

Hello,

I'was trying to get meta data of a column and discovered that the column name is case sensitive though it should be
caseinsensitive. I don't know if this can be somehow determined by a configuration or environment change because I
believethat till recently it  worked in a case-insensitive manner. 

I've the table studna_monitor.mon_user_session with the column "username". The following code wrongly returns null
(notice"username".toUpperCase()): 
---
String col = null;
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost/mydb?user=me&password=secret";
Connection conn = DriverManager.getConnection(url);
    DatabaseMetaData databaseMetaData = conn.getMetaData();
    ResultSet columnsMD = databaseMetaData.getColumns(null, "studna_monitor", "mon_user_session",
"username".toUpperCase());
    while(columnsMD.next()) {
        col = columnsMD.getString("COLUMN_NAME");
    }
    return col;
---
While the following returns the column name "username":
---
...
ResultSet columnsMD = databaseMetaData.getColumns(null, "studna_monitor", "mon_user_session",
"username".toLowerCase());
...
---

BTW, the metadata reports:
supportsMixedCaseIdentifiers: false
supportsMixedCaseQuotedIdentifiers: true
(which is pretty much expected)

Driver: postgresql-8.3-604.jdbc3.jar, postgresql-jdbc3-8.2.jar (as distributed with Ubuntu, doesn't provide build
number)
Server:  Postgres 8.2.6 for Solaris


Thank you.
Jakub Holy
maly.velky@email.cz

Shouldn't you quote the column in question if you want to get a MixedCase quoted column ?

Dave

On Mon, 2 Feb 2009, maly.velky@email.cz wrote:

> I'was trying to get meta data of a column and discovered that the column
> name is case sensitive though it should be case insensitive. I don't
> know if this can be somehow determined by a configuration or environment
> change because I believe that till recently it worked in a
> case-insensitive manner.

This is done by design and has been since 7.3 I believe.  The problem is
that you can legally have two columns named "T1" and "t1".  To support
this we need to be case sensitive on the Java side.

> BTW, the metadata reports:
> supportsMixedCaseIdentifiers: false
> supportsMixedCaseQuotedIdentifiers: true
> (which is pretty much expected)

This is reporting how the server matches up with the SQL standard, not how
a particular JDBC API call works.

Kris Jurka

Hello, thank you very much for your clarification.

So there is no way how to perform case-insensitive search in getColumns() ?

> > supportsMixedCaseIdentifiers: false
> This is reporting how the server matches up with the SQL standard, not how
> a particular JDBC API call works.
So when does this (namely "Retrieves whether this database treats mixed case unquoted SQL identifiers as case
sensitive"= false) apply? As you say it doesn't apply to the getColumns call but when does it? For insert/update/select
statementsonly? If it applies for select statements, what happens (referring to you example) when I've "select t1 from
...",does it select t1, T1, or fail? 

> ------------ Původní zpráva ------------
> Od: Kris Jurka <books@ejurka.com>
> Předmět: Re: [JDBC] BUG: DatabaseMetaData.getColumns isn't case insensitive
> Datum: 03.2.2009 05:49:23
> ----------------------------------------
>
>
> On Mon, 2 Feb 2009, maly.velky@email.cz wrote:
>
> > I'was trying to get meta data of a column and discovered that the column
> > name is case sensitive though it should be case insensitive. I don't
> > know if this can be somehow determined by a configuration or environment
> > change because I believe that till recently it worked in a
> > case-insensitive manner.
>
> This is done by design and has been since 7.3 I believe.  The problem is
> that you can legally have two columns named "T1" and "t1".  To support
> this we need to be case sensitive on the Java side.
>
> > BTW, the metadata reports:
> > supportsMixedCaseIdentifiers: false
> > supportsMixedCaseQuotedIdentifiers: true
> > (which is pretty much expected)
>
> This is reporting how the server matches up with the SQL standard, not how
> a particular JDBC API call works.
>
> Kris Jurka
>
>
>

Jakub Holy
maly.velky@email.cz

On Tue, 3 Feb 2009, maly.velky@email.cz wrote:

> Hello, thank you very much for your clarification.
>
> So there is no way how to perform case-insensitive search in getColumns() ?

Sorry, no.

>> > supportsMixedCaseIdentifiers: false
>>
>> This is reporting how the server matches up with the SQL standard, not how
>> a particular JDBC API call works.
>>
> So when does this (namely "Retrieves whether this database treats mixed case
> unquoted SQL identifiers as case sensitive" = false) apply? As you say it
> doesn't apply to the getColumns call but when does it? For
> insert/update/select statements only? If it applies for select statements,
> what happens (referring to you example) when I've "select t1 from ...", does
> it select t1, T1, or fail?

jurka=# create table t ("c1" int, "C1" int);
CREATE TABLE
jurka=# insert into t values (1, 2);
INSERT 0 1
jurka=# select c1, C1 from t;
  c1 | c1
----+----
   1 |  1
(1 row)


This is actually against the SQL spec, the spec says unquoted identifiers
should be folded to upper case rather than postgresql's behavior of
folding to upper case.  So a spec complaint database would have retrieved
C1's value of 2.  If you look further at DatabaseMetaData you can see this
called out by storesLowerCaseIdentifiers returning true.

To get both values out the column names must be quoted.

jurka=# SELECT "c1", "C1" from t;
  c1 | C1
----+----
   1 |  2
(1 row)

Supporting that in getColumns would be odd because requiring a column name
to be quoted there isn't something that would happen naturally.  It's
would be especially bad if you also consider that column names can have
quotes in them:

jurka=# alter table t add column """" int;
ALTER TABLE
jurka=# \d t
        Table "public.t"
  Column |  Type   | Modifiers
--------+---------+-----------
  c1     | integer |
  C1     | integer |
  "      | integer |


Kris Jurka