Thread: Case-sensitive problem in AS clause?

Case-sensitive problem in AS clause?

From
Laurent Mazuel
Date:
Dear all,

I maybe found a problem with the "isCaseSensitive" method in the
"ResultSetMetadata" class when using it on a "AS" clause.

With the following test DB:
> test=> CREATE TABLE "Student" ("ID" INTEGER,"Name" VARCHAR(15));
> CREATE TABLE
> test=> INSERT INTO "Student" ("ID", "Name") VALUES(10,'Venus');
> INSERT 0 1
I execute the query with the "AS" clause:
> test=> Select ('Student' || "ID" ) AS StudentId, "ID", "Name" from
> "Student" ;
>  studentid | ID | Name
> -----------+----+-------
>  Student10 | 10 | Venus
> (1 ligne)

The StudentId regular identifier becomes studentid, but this kind of
transformation is not a problem, since a regular identifier is not case
sensitive.

The problem is where I execute my query in JDBC:
>     @Test
>     public void testAsCaseSensitive() throws Exception {
>     Connection conn =
> DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/test","root","root");
>     java.sql.Statement s = conn.createStatement(
>         ResultSet.HOLD_CURSORS_OVER_COMMIT, ResultSet.CONCUR_READ_ONLY);
>     s.executeQuery("Select ('Student' || \"ID\" ) AS StudentId from
> \"Student\";");
>     ResultSet rs = s.getResultSet();
>     ResultSetMetaData metaData = rs.getMetaData();
>     int n = metaData.getColumnCount();
>     for (int i = 1; i <= n; i++) {
>         System.out.println("Column: "+metaData.getColumnLabel(i)+"
> "+metaData.isCaseSensitive(i));
>     }
>     }
I obtain the output:
> Column: studentid true

Then, the column name is changed from StudentId to studentid, but the
"isCaseSensitive" flag is "true". I think it is not correct, since it is
impossible from now, when a user ask for the StudentId column to
retrieve it from a resultset. The "isCaseSensitive" don't authorize to
accept the string StudentId as a valid column name for the studentid
column in the ResultSet.

This on: psql (PostgreSQL) 9.1.4 (debian-squeeze backport packages),
with Maven dependency on JDBC plugin:
> <dependency>
> <groupId>postgresql</groupId>
> <artifactId>postgresql</artifactId>
> <version>9.1-901.jdbc4</version>
> </dependency>


I hope that I don't have miss something and I don't bother you....

Best regards,

Laurent Mazuel

--
Laurent Mazuel (lmazuel@antidot.net)
Ingénieur R&D, Web Sémantique
Antidot - Solutions de recherche d'information
29 avenue Jean Monnet, 13410 LAMBESC (FRANCE)
Tel: (33) 4 42 63 67 90 / Fax: (33) 4 42 28 61 03



Re: Case-sensitive problem in AS clause?

From
dmp
Date:
Laurent Mazuel wrote:
> Dear all,
>
> I maybe found a problem with the "isCaseSensitive" method in the
> "ResultSetMetadata" class when using it on a "AS" clause.
>
>> test=> Select ('Student' || "ID" ) AS StudentId, "ID", "Name" from
> The StudentId regular identifier becomes studentid, but this kind of
> transformation is not a problem, since a regular identifier is not case
> sensitive.

Select ('Student' || "ID" ) AS "StudentId", "ID", "Name" from "Student"

Quoting the "StudentID" will make it case sensitive for you resultset.

>
> The problem is where I execute my query in JDBC:
>> ~
>> ~
>>~
>> s.executeQuery("Select ('Student' || \"ID\" ) AS StudentId from
>> \"Student\";");
>> ResultSet rs = s.getResultSet();
>> ResultSetMetaData metaData = rs.getMetaData();
>> int n = metaData.getColumnCount();
>> for (int i = 1; i <= n; i++) {
>> System.out.println("Column: "+metaData.getColumnLabel(i)+"
>> "+metaData.isCaseSensitive(i));
>> }
>> }
> I obtain the output:
>> Column: studentid true

Again as the as identifier is not quoting it default to the default
behavior for assignment. As far as the isCaseSensitive() it is found
that the method in the Pgjdbc,
org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java is:

/*
      * Does a column's case matter? ASSUMPTION: Any field that is
      * not obviously case insensitive is assumed to be case sensitive
      *
      * @param column the first column is 1, the second is 2...
      * @return true if so
      * @exception SQLException if a database access error occurs
      */
     public boolean isCaseSensitive(int column) throws SQLException
     {
         Field field = getField(column);
         return connection.getTypeInfo().isCaseSensitive(field.getOID());
     }

As indicated by the comment, case is going to be assumed case sensitive,
true. Since the column is a result of AS clause it seems to be assigned
as a generic PGObject type. That type would then to be case insensitive.

>
> Then, the column name is changed from StudentId to studentid, but the
> "isCaseSensitive" flag is "true". I think it is not correct, since it is
> impossible from now, when a user ask for the StudentId column to
> retrieve it from a resultset. The "isCaseSensitive" don't authorize to
> accept the string StudentId as a valid column name for the studentid
> column in the ResultSet.

Recommed you quote AS "StudentID" to get the behavior you desire.

danap.


Re: Case-sensitive problem in AS clause?

From
"Kevin Grittner"
Date:
Laurent Mazuel <lmazuel@antidot.net> wrote:

> I maybe found a problem with the "isCaseSensitive" method in the
> "ResultSetMetadata" class when using it on a "AS" clause.

> I execute the query with the "AS" clause:
>> test=> Select ('Student' || "ID" ) AS StudentId, "ID", "Name"
>> from "Student" ;

> The StudentId regular identifier becomes studentid

Because a non-quoted identifier is folded to lower case in
PostgreSQL.

> but this kind of transformation is not a problem, since a regular
> identifier is not case sensitive.

Yes it is.

> the "isCaseSensitive" flag is "true". I think it is not correct

It is correct, because it will only match the lowercase name.  You
can, of course, generate that by leaving off the quotes.  It *is*
case sensitive, but there is folding of unquoted identifier names.

The easiest path is to always use lowercase identifiers.  Another
path which is not too unreasonable, especially if you have a
framework which can do it for you, is to automatically quote all
identifiers.  Using any uppercase letters in any identifiers and not
being consistent about whether or not you quote things, is sure to
cause trouble.

-Kevin

Re: Case-sensitive problem in AS clause?

From
"David Johnston"
Date:
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of dmp
> Sent: Monday, July 16, 2012 12:05 PM
> To: Laurent Mazuel; PostgreSQL JDBC
> Subject: Re: [JDBC] Case-sensitive problem in AS clause?
>
> Again as the as identifier is not quoting it default to the default
behavior for
> assignment. As far as the isCaseSensitive() it is found that the method in
the
> Pgjdbc, org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java is:
>
> /*
>       * Does a column's case matter? ASSUMPTION: Any field that is
>       * not obviously case insensitive is assumed to be case sensitive
>       *
>
> >
> > Then, the column name is changed from StudentId to studentid, but the
> > "isCaseSensitive" flag is "true". I think it is not correct, since it
> > is impossible from now, when a user ask for the StudentId column to
> > retrieve it from a resultset. The "isCaseSensitive" don't authorize to
> > accept the string StudentId as a valid column name for the studentid
> > column in the ResultSet.
>
> Recommed you quote AS "StudentID" to get the behavior you desire.
>
> danap.

Does the "isCaseSensitive(int)" function return whether the "value" stored
in the column is case-sensitive or does it return whether the "name" of the
column is case-sensitive?

The OP is using it to determine whether the "name" is case-sensitive - which
it is always.

My assumption is that it would indicate whether the "value" is
case-sensitive - which is also true because varchar/text is case-sensitive.

The fact that different fields may or may not be case-sensitive supports
this since the "case-sensitive" property of a column "name" should be
constant throughout the database/product.

David J.








Re: Case-sensitive problem in AS clause?

From
Dave Cramer
Date:
The name of the column is not always case sensitive in fact postgresql
all names are folded to lower case unless quoted

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Jul 16, 2012 at 12:49 PM, David Johnston <polobo@yahoo.com> wrote:
>> -----Original Message-----
>> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
>> owner@postgresql.org] On Behalf Of dmp
>> Sent: Monday, July 16, 2012 12:05 PM
>> To: Laurent Mazuel; PostgreSQL JDBC
>> Subject: Re: [JDBC] Case-sensitive problem in AS clause?
>>
>> Again as the as identifier is not quoting it default to the default
> behavior for
>> assignment. As far as the isCaseSensitive() it is found that the method in
> the
>> Pgjdbc, org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java is:
>>
>> /*
>>       * Does a column's case matter? ASSUMPTION: Any field that is
>>       * not obviously case insensitive is assumed to be case sensitive
>>       *
>>
>> >
>> > Then, the column name is changed from StudentId to studentid, but the
>> > "isCaseSensitive" flag is "true". I think it is not correct, since it
>> > is impossible from now, when a user ask for the StudentId column to
>> > retrieve it from a resultset. The "isCaseSensitive" don't authorize to
>> > accept the string StudentId as a valid column name for the studentid
>> > column in the ResultSet.
>>
>> Recommed you quote AS "StudentID" to get the behavior you desire.
>>
>> danap.
>
> Does the "isCaseSensitive(int)" function return whether the "value" stored
> in the column is case-sensitive or does it return whether the "name" of the
> column is case-sensitive?
>
> The OP is using it to determine whether the "name" is case-sensitive - which
> it is always.
>
> My assumption is that it would indicate whether the "value" is
> case-sensitive - which is also true because varchar/text is case-sensitive.
>
> The fact that different fields may or may not be case-sensitive supports
> this since the "case-sensitive" property of a column "name" should be
> constant throughout the database/product.
>
> David J.
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

Re: Case-sensitive problem in AS clause?

From
"David Johnston"
Date:
> -----Original Message-----
> From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf
> Of Dave Cramer
> Sent: Monday, July 16, 2012 12:56 PM
> To: David Johnston
> Cc: dmp; Laurent Mazuel; PostgreSQL JDBC
> Subject: Re: [JDBC] Case-sensitive problem in AS clause?
>
> The name of the column is not always case sensitive in fact postgresql all
> names are folded to lower case unless quoted
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Mon, Jul 16, 2012 at 12:49 PM, David Johnston <polobo@yahoo.com>
> wrote:
> >> -----Original Message-----
> >> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> >> owner@postgresql.org] On Behalf Of dmp
> >> Sent: Monday, July 16, 2012 12:05 PM
> >> To: Laurent Mazuel; PostgreSQL JDBC
> >> Subject: Re: [JDBC] Case-sensitive problem in AS clause?
> >>
> >> Again as the as identifier is not quoting it default to the default
> > behavior for
> >> assignment. As far as the isCaseSensitive() it is found that the
> >> method in
> > the
> >> Pgjdbc, org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java is:
> >>
> >> /*
> >>       * Does a column's case matter? ASSUMPTION: Any field that is
> >>       * not obviously case insensitive is assumed to be case sensitive
> >>       *
> >>
> >> >
> >> > Then, the column name is changed from StudentId to studentid, but
> >> > the "isCaseSensitive" flag is "true". I think it is not correct,
> >> > since it is impossible from now, when a user ask for the StudentId
> >> > column to retrieve it from a resultset. The "isCaseSensitive" don't
> >> > authorize to accept the string StudentId as a valid column name for
> >> > the studentid column in the ResultSet.
> >>
> >> Recommed you quote AS "StudentID" to get the behavior you desire.
> >>
> >> danap.
> >
> > Does the "isCaseSensitive(int)" function return whether the "value"
> > stored in the column is case-sensitive or does it return whether the
> > "name" of the column is case-sensitive?
> >
> > The OP is using it to determine whether the "name" is case-sensitive -
> > which it is always.
> >
> > My assumption is that it would indicate whether the "value" is
> > case-sensitive - which is also true because varchar/text is
case-sensitive.
> >
> > The fact that different fields may or may not be case-sensitive
> > supports this since the "case-sensitive" property of a column "name"
> > should be constant throughout the database/product.
> >
> > David J.
> >

Folding to lower-case does not negate the fact that the name itself is
always case-sensitive.  It is just when you don't put the name in quotes a
pre-processing phase translates the name into lower-case.  I get that this
is semantics but in a true case-insensitive system it would not matter
whether you used quotes or not when providing the name.  So in PostgreSQL
names are always case-sensitive but if the name is not quoted the case of
the name is automatically converted to lower-case before the case-sensitive
comparison is made.  In other words - once the name is stored it no longer
matters how the value was entered (i.e., with or without qutotes) its value
is stored in a case-sensitive manner.  There is no way to store an
mixed-case value and then compare to it in a case-insensitive manner.  E.G.,
If my field name has been stored as "ColumnA" there is no way to reference
that column other than by writing: SELECT "ColumnA" ... Futhermore the
system cannot distinguish between (columna) AND ("columna") - both
representations are equivalent.  By your logic the former should return
"case-insensitive" while the later should return "case-sensitive".

David J.





Re: Case-sensitive problem in AS clause?

From
dmp
Date:
David Johnston wrote:

>> Pgjdbc, org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java is:
>>
>> /*
>>        * Does a column's case matter? ASSUMPTION: Any field that is
>>        * not obviously case insensitive is assumed to be case sensitive
>>        *
> public boolean isCaseSensitive(int column) throws SQLException
>     {
>         Field field = getField(column);
>         return connection.getTypeInfo().isCaseSensitive(field.getOID());
>     }
> Does the "isCaseSensitive(int)" function return whether the "value" stored
> in the column is case-sensitive or does it return whether the "name" of the
> column is case-sensitive?
>
> The OP is using it to determine whether the "name" is case-sensitive - which
> it is always.
>
> My assumption is that it would indicate whether the "value" is
> case-sensitive - which is also true because varchar/text is case-sensitive.
>
> The fact that different fields may or may not be case-sensitive supports
> this since the "case-sensitive" property of a column "name" should be
> constant throughout the database/product.
>
> David J.

Case sensitivity appears to by based on the "type", not column "name" or
"value":

connection.getTypeInfo() returns TypeInfo
TypeInfo.isCaseSensitive() takes the field.getOID() which is a Type.

Test:

Connection Created
Select * from "Student"
Column: ID Type: 4 Type Name: int4 isSensitive: false
Column: Name Type: 12 Type Name: varchar is Sensitive: true
Connection Closed

Connection Created
Select ('Student' || "ID" ) AS StudentId from "Student"
Column: studentid Type: 12 Type Name: text isSensitive: true
Connection Closed

Your assumption appears be true, but based on type, which I guess you
could argue is value. The op is making the false assumption isCaseSenstive()
is based on column name which it is not looks from the code.

danap.

Re: Case-sensitive problem in AS clause?

From
Dave Cramer
Date:
Ok, I just looked up the tutorial and it really is whether the value
stored in the column is case sensitive, not the name of the column.

Yet another serious ambiguity in the API spec.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Mon, Jul 16, 2012 at 2:31 PM, dmp <danap@ttc-cmc.net> wrote:
> David Johnston wrote:
>
>>> Pgjdbc, org/postgresql/jdbc2/AbstractJdbc2ResultSetMetaData.java is:
>>>
>>> /*
>>>        * Does a column's case matter? ASSUMPTION: Any field that is
>>>        * not obviously case insensitive is assumed to be case sensitive
>>>        *
>>
>> public boolean isCaseSensitive(int column) throws SQLException
>>
>>     {
>>         Field field = getField(column);
>>         return connection.getTypeInfo().isCaseSensitive(field.getOID());
>>     }
>> Does the "isCaseSensitive(int)" function return whether the "value" stored
>> in the column is case-sensitive or does it return whether the "name" of
>> the
>> column is case-sensitive?
>>
>> The OP is using it to determine whether the "name" is case-sensitive -
>> which
>> it is always.
>>
>> My assumption is that it would indicate whether the "value" is
>> case-sensitive - which is also true because varchar/text is
>> case-sensitive.
>>
>> The fact that different fields may or may not be case-sensitive supports
>> this since the "case-sensitive" property of a column "name" should be
>> constant throughout the database/product.
>>
>> David J.
>
>
> Case sensitivity appears to by based on the "type", not column "name" or
> "value":
>
> connection.getTypeInfo() returns TypeInfo
> TypeInfo.isCaseSensitive() takes the field.getOID() which is a Type.
>
> Test:
>
> Connection Created
> Select * from "Student"
> Column: ID Type: 4 Type Name: int4 isSensitive: false
> Column: Name Type: 12 Type Name: varchar is Sensitive: true
> Connection Closed
>
> Connection Created
>
> Select ('Student' || "ID" ) AS StudentId from "Student"
> Column: studentid Type: 12 Type Name: text isSensitive: true
> Connection Closed
>
> Your assumption appears be true, but based on type, which I guess you
> could argue is value. The op is making the false assumption isCaseSenstive()
> is based on column name which it is not looks from the code.
>
> danap.
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

Re: Case-sensitive problem in AS clause?

From
Craig Ringer
Date:
On 07/17/2012 02:48 AM, Dave Cramer wrote:
> Ok, I just looked up the tutorial and it really is whether the value
> stored in the column is case sensitive, not the name of the column.
>
> Yet another serious ambiguity in the API spec.

Yay, won't THAT be fun to implement when Pg its self doesn't know
whether any given type is case-sensitive or not (eg 'text' vs 'citext')
and doesn't have the concept of per-column case sensitivity at all.

What's the right thing to return here? True unless 'citext'?

--
Craig Ringer