Thread: Case-sensitive problem in AS clause?
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
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.
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
> -----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.
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
> -----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.
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.
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
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