Thread: JDBC bug?
Hi, I've found an annoyance with the postgresql JDBC driver (from the 7.1.3 release). Pardon if this has been asked before, but the web archives are offline... In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines that call toLowerCase() on the table string names. This causes 'table unknown' erros when accessing tables with mixed case in the name. This popped out at me when trying to use the XML-DBMS package and also DbVisualizer. Also, when using the pgsql shell, \d createdBy returns table unknown, but \d "createBy" returns the right info. So does postgres not support mixed case in table names, or the backend does but the clients dont, or what? nate
> Hi, > > I've found an annoyance with the postgresql JDBC driver (from the 7.1.3 > release). > > Pardon if this has been asked before, but the web archives are offline... > > In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines > that call toLowerCase() on the table string names. This causes 'table > unknown' erros when accessing tables with mixed case in the name. This > popped out at me when trying to use the XML-DBMS package and also > DbVisualizer. > > Also, when using the pgsql shell, \d createdBy returns table unknown, > but \d "createBy" returns the right info. > > So does postgres not support mixed case in table names, or the backend > does but the clients dont, or what? Well, we support mixed case identifiers, but they need to be double-quoted, as you saw. The \d behavior looks correct. The jdbc issue looks more complicated because we are forcing lowercase in the jdbc code. Perhaps some jdbc folks can comment on that. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Nate, In general it seems that doing the toLowerCase is the correct behavior. SQL is case insensitive, and in postgres this is acheived by lowercasing all names (tablenames, columnnames, functionnames, etc). So in general there is no difference between createdBy, createdby, CreatedBy, CREATEDBY from a SQL perspective. However SQL also allows mixed case identifiers if they are quoted. Thus "createdBy" will only match "createdBy" and will not match createdBy without quotes (because the latter if folded to lower case). I am not sure exactly what your problem is. Can you provide some more details given the explanation presented above? thanks, --Barry Nate Gelbard wrote: > Hi, > > I've found an annoyance with the postgresql JDBC driver (from the 7.1.3 > release). > > Pardon if this has been asked before, but the web archives are offline... > > In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines > that call toLowerCase() on the table string names. This causes 'table > unknown' erros when accessing tables with mixed case in the name. This > popped out at me when trying to use the XML-DBMS package and also > DbVisualizer. > > Also, when using the pgsql shell, \d createdBy returns table unknown, > but \d "createBy" returns the right info. > > So does postgres not support mixed case in table names, or the backend > does but the clients dont, or what? > > nate > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
>> Hi, >> >> I've found an annoyance with the postgresql JDBC driver (from the 7.1.3 >> release). >> >> Pardon if this has been asked before, but the web archives are offline... >> >> In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines >> that call toLowerCase() on the table string names. This causes 'table >> unknown' erros when accessing tables with mixed case in the name. This >> popped out at me when trying to use the XML-DBMS package and also >> DbVisualizer. >> >> So does postgres not support mixed case in table names, or the backend >> does but the clients dont, or what? > >Well, we support mixed case identifiers, but they need to be >double-quoted, as you saw. The \d behavior looks correct. The jdbc >issue looks more complicated because we are forcing lowercase in the >jdbc code. Perhaps some jdbc folks can comment on that. I think Nate's being kind calling this an annoyance. I may have missed something in the docs, but I just spent a few frustrating hours confronting a similar issue with tables of mixed case. Everything was working correctly with my servlet, except when I referenced a column with a name like questionID in a where clause. Note the casing. I had no idea this is what was wrong until, other ideas exhausted, I recreated my query text with pgadmin's query wizard and it put quotes around everything in the SQL it generated. Is there a pqsql-jdbc log somewhere that this might have shown up? I was so sure it was my java code (which in the end it was, sort of :) I never bothered to check any Postgres logs. Would it have shown up somewhere there? Either way, personally, I think this isn't the best behavior. I don't want to get into a casing flame war, but, I would've expected an exception if it couldn't figure out what was happening with the where clause, something at least. Colin Freas
Note that this is SQL92 standards compliant behavior: identifiers are supposed to be case-insensitive, unless quoted. Ross On Mon, Nov 26, 2001 at 01:30:24PM -0500, Bruce Momjian wrote: > > Hi, > > > > I've found an annoyance with the postgresql JDBC driver (from the 7.1.3 > > release). > > > > Pardon if this has been asked before, but the web archives are offline... > > > > In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines > > that call toLowerCase() on the table string names. This causes 'table > > unknown' erros when accessing tables with mixed case in the name. This > > popped out at me when trying to use the XML-DBMS package and also > > DbVisualizer. > > > > Also, when using the pgsql shell, \d createdBy returns table unknown, > > but \d "createBy" returns the right info. > > > > So does postgres not support mixed case in table names, or the backend > > does but the clients dont, or what? > > Well, we support mixed case identifiers, but they need to be > double-quoted, as you saw. The \d behavior looks correct. The jdbc > issue looks more complicated because we are forcing lowercase in the > jdbc code. Perhaps some jdbc folks can comment on that. >
> >> So does postgres not support mixed case in table names, or the backend > >> does but the clients dont, or what? > > > >Well, we support mixed case identifiers, but they need to be > >double-quoted, as you saw. The \d behavior looks correct. The jdbc > >issue looks more complicated because we are forcing lowercase in the > >jdbc code. Perhaps some jdbc folks can comment on that. > > I think Nate's being kind calling this an annoyance. I may have missed > something in the docs, but I just spent a few frustrating hours confronting > a similar issue with tables of mixed case. Everything was working correctly > with my servlet, except when I referenced a column with a name like > questionID in a where clause. Note the casing. I had no idea this is what > was wrong until, other ideas exhausted, I recreated my query text with > pgadmin's query wizard and it put quotes around everything in the SQL it > generated. > > Is there a pqsql-jdbc log somewhere that this might have shown up? I was so > sure it was my java code (which in the end it was, sort of :) I never > bothered to check any Postgres logs. Would it have shown up somewhere > there? Either way, personally, I think this isn't the best behavior. I > don't want to get into a casing flame war, but, I would've expected an > exception if it couldn't figure out what was happening with the where > clause, something at least. Yes, I agree, it clearly looks like a jdbc bug. If you have created mixed-case identifiers, the jdbc toLower call make accessing it impossible. If we can trust the case of the indentifier supplied, we can just put double-quotes around it to preserve the case as passed to the backend. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> I haven't seen any evidence in this thread of a JDBC bug. The original > posting refered to calls in the DatabaseMetaData classes doing > toLowerCase(). This is appropriate because the DatabaseMetaData class > is querying the pg_* tables to get information about database objects, > thus since the backend stores the identifiers in lower case, therefore > the jdbc code needs to do a toLowerCase(). The followup below is more But the backend doesn't store identifiers in lowercase if they have created mixed-case identifiers with double-quotes, right? I am confused. > of a complaint on how SQL is case insensitive and if you have created > your objects with quoted mixed case identifiers you need to access them > via quoted mixed case identifiers. I haven't seen any evidence that > basic SQL operations (select, insert, update, delete) have a bug in them. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
I haven't seen any evidence in this thread of a JDBC bug. The original posting refered to calls in the DatabaseMetaData classes doing toLowerCase(). This is appropriate because the DatabaseMetaData class is querying the pg_* tables to get information about database objects, thus since the backend stores the identifiers in lower case, therefore the jdbc code needs to do a toLowerCase(). The followup below is more of a complaint on how SQL is case insensitive and if you have created your objects with quoted mixed case identifiers you need to access them via quoted mixed case identifiers. I haven't seen any evidence that basic SQL operations (select, insert, update, delete) have a bug in them. thanks, --Barry Bruce Momjian wrote: >>>>So does postgres not support mixed case in table names, or the backend >>>>does but the clients dont, or what? >>>> >>>Well, we support mixed case identifiers, but they need to be >>>double-quoted, as you saw. The \d behavior looks correct. The jdbc >>>issue looks more complicated because we are forcing lowercase in the >>>jdbc code. Perhaps some jdbc folks can comment on that. >>> >>I think Nate's being kind calling this an annoyance. I may have missed >>something in the docs, but I just spent a few frustrating hours confronting >>a similar issue with tables of mixed case. Everything was working correctly >>with my servlet, except when I referenced a column with a name like >>questionID in a where clause. Note the casing. I had no idea this is what >>was wrong until, other ideas exhausted, I recreated my query text with >>pgadmin's query wizard and it put quotes around everything in the SQL it >>generated. >> >>Is there a pqsql-jdbc log somewhere that this might have shown up? I was so >>sure it was my java code (which in the end it was, sort of :) I never >>bothered to check any Postgres logs. Would it have shown up somewhere >>there? Either way, personally, I think this isn't the best behavior. I >>don't want to get into a casing flame war, but, I would've expected an >>exception if it couldn't figure out what was happening with the where >>clause, something at least. >> > > Yes, I agree, it clearly looks like a jdbc bug. If you have created > mixed-case identifiers, the jdbc toLower call make accessing it > impossible. If we can trust the case of the indentifier supplied, we > can just put double-quotes around it to preserve the case as passed to > the backend. > >
> via quoted mixed case identifiers. I haven't seen any evidence that
> basic SQL operations (select, insert, update, delete) have a bug in them.
I'd peg a where clause as a basic SQL operation, and be it bug, feature,
annoyance, whatever, it wasn't functioning because of the case of a column
name.
This worked:
1. rs = stmt.executeQuery("select * from response");
But I wanted to do this, which didn't work:
2. rs = stmt.executeQuery("select * from response where questionID=16");
This is how I got it to work:
3. rs = stmt.executeQuery("SELECT \"response\".\"questionID\",
\"response\".\"respondentID\", \"response\".\"answer\", \"response\".\"ID\"
FROM \"response\" WHERE "\response\".\"questionID\"=16");
Sorry, does anyone think the third is better than the second?
Bruce Momjian wrote: >>I haven't seen any evidence in this thread of a JDBC bug. The original >>posting refered to calls in the DatabaseMetaData classes doing >>toLowerCase(). This is appropriate because the DatabaseMetaData class >>is querying the pg_* tables to get information about database objects, >>thus since the backend stores the identifiers in lower case, therefore >>the jdbc code needs to do a toLowerCase(). The followup below is more >> > > But the backend doesn't store identifiers in lowercase if they have > created mixed-case identifiers with double-quotes, right? I am > confused. > Correct. But if you ask the API to get information about table myTable, you should get an answer back for a table created as (create table myTable (foo text)). The only way to do this is to lowercase the input to match what the database is storing (mytable in this case). I agree that if the table is created with a mixed case identifier (create table "myTable" (foo text)) then there isn't a need to lower case in the driver. Therefore the fact that there are a bunch of calls to toLowerCase() in the jdbc code for DatabaseMetaData doesn't mean that there is a bug here as they are entirely appropriate under some circumstances. If someone produces a test case that demonstrates a bug I will be glad to look into it. But up to this point there has only been a lot of speculation on code that may be entirely correct. Test cases demonstrating a real problem would be much apprecieated in this case. thanks, --Barry > > >>of a complaint on how SQL is case insensitive and if you have created >>your objects with quoted mixed case identifiers you need to access them >>via quoted mixed case identifiers. I haven't seen any evidence that >>basic SQL operations (select, insert, update, delete) have a bug in them. >> >
Colin,
Whether it is better or not isn't the issue. That is how the SQL
standard says it is supposed to work. If you create the objects with
quoted mixed case identifiers you must use quoted mixed case identifiers
to access the objects. A better solution to your problem would be to
create the tables without using quoted indentifiers and then your
original query would work.
thanks,
--Barry
Colin Freas wrote:
>>via quoted mixed case identifiers. I haven't seen any evidence that
>>basic SQL operations (select, insert, update, delete) have a bug in them.
>>
>
> I'd peg a where clause as a basic SQL operation, and be it bug, feature,
> annoyance, whatever, it wasn't functioning because of the case of a column
> name.
>
> This worked:
> 1. rs = stmt.executeQuery("select * from response");
>
> But I wanted to do this, which didn't work:
> 2. rs = stmt.executeQuery("select * from response where questionID=16");
>
> This is how I got it to work:
> 3. rs = stmt.executeQuery("SELECT \"response\".\"questionID\",
> \"response\".\"respondentID\", \"response\".\"answer\", \"response\".\"ID\"
> FROM \"response\" WHERE "\response\".\"questionID\"=16");
>
> Sorry, does anyone think the third is better than the second?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
> > via quoted mixed case identifiers. I haven't seen any evidence that
> > basic SQL operations (select, insert, update, delete) have a bug in them.
>
> I'd peg a where clause as a basic SQL operation, and be it bug, feature,
> annoyance, whatever, it wasn't functioning because of the case of a column
> name.
>
> This worked:
> 1. rs = stmt.executeQuery("select * from response");
>
> But I wanted to do this, which didn't work:
> 2. rs = stmt.executeQuery("select * from response where questionID=16");
Only this should have worked:
rs = stmt.executeQuery("select * from response where \"questionID\"=16");
The expansion of the "*" should protect any mixed case items by default.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> Correct. But if you ask the API to get information about table myTable, > you should get an answer back for a table created as (create table > myTable (foo text)). The only way to do this is to lowercase the input > to match what the database is storing (mytable in this case). I agree > that if the table is created with a mixed case identifier (create table > "myTable" (foo text)) then there isn't a need to lower case in the > driver. Therefore the fact that there are a bunch of calls to > toLowerCase() in the jdbc code for DatabaseMetaData doesn't mean that > there is a bug here as they are entirely appropriate under some > circumstances. If someone produces a test case that demonstrates a bug > I will be glad to look into it. But up to this point there has only > been a lot of speculation on code that may be entirely correct. Test > cases demonstrating a real problem would be much apprecieated in this case. > OK, I see what you mean. If you pass in myTable, you want to look for mytable in pg_class. However, what if they pass in "myTable", with the quotes? Do we skip the lowercase step? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> of a complaint on how SQL is case insensitive and if you have created > your objects with quoted mixed case identifiers you need to accessthem > via quoted mixed case identifiers. I haven't seen any evidence that I am mistaken. This is actually a bug in the applications that are building SQL statements without quotes around tablenames of mixedcase. nate
Barry,
I see what you're saying, and you're right. But as for not using mixed case
identifiers as being the better solution, I have to disagree. It's not like
I'm asking to put spaces in my column names and have the parser read my
mind. I just think mixed case identifiers are easier on the eyes.
Although, the \"'s obviously aren't. And is it \"s or \"'s?
I also just wanted to make sure I understand what's going on. Postgres
stores the identifiers in a case insensitve manner by default, but if you
mix case when creating the tables or what have you, it stores them as case
sensitive. Then, the JDBC driver would take 'select * from z where XyZZy =
"Fool!"' and send it as '...xyzzy...', which would... what? Is that going
to throw an error, or just not return anything? pgadmin II says attribute
undefined or somesuch, and that is what quickly led me to the solution.
I'm just saying that it's frustrating because in one of my earlier checks to
make sure my SQL was ok, I used Access 2000 with some linked tables to my
Postgres db. When I ran 'select * from response where questionID=16'
through that, it worked without a hitch (actually, the 16 needed to be
'16'), so I figured the problem was somewhere else. Plus it was such simple
SQL, I mean, what could be wrong with it? I'd be the first to admit I
don't... well, I didn't... know the nuances of the SQL9X casing rules. My
issue, as stated in my initial note, was more that nobody (jdbc, postgres,
tomcat) told me that my where clause was, essentially, bogus. I mean, what
am I catching that SQLException for?
And Bruce wrote...
>rs = stmt.executeQuery("select * from response where \"questionID\"=16");
That works.
Doh!
Colin
------
Barry wrote:
Colin,
Whether it is better or not isn't the issue. That is how the SQL
standard says it is supposed to work. If you create the objects with
quoted mixed case identifiers you must use quoted mixed case identifiers
to access the objects. A better solution to your problem would be to
create the tables without using quoted indentifiers and then your
original query would work.
thanks,
--Barry
Colin Freas wrote:
>>via quoted mixed case identifiers. I haven't seen any evidence that
>>basic SQL operations (select, insert, update, delete) have a bug in them.
>>
>
> I'd peg a where clause as a basic SQL operation, and be it bug, feature,
> annoyance, whatever, it wasn't functioning because of the case of a column
> name.
>
> This worked:
> 1. rs = stmt.executeQuery("select * from response");
>
> But I wanted to do this, which didn't work:
> 2. rs = stmt.executeQuery("select * from response where questionID=16");
>
> This is how I got it to work:
> 3. rs = stmt.executeQuery("SELECT \"response\".\"questionID\",
> \"response\".\"respondentID\", \"response\".\"answer\",
\"response\".\"ID\"
> FROM \"response\" WHERE "\response\".\"questionID\"=16");
>
> Sorry, does anyone think the third is better than the second?
On Mon, Nov 26, 2001 at 03:41:28PM -0500, Colin Freas wrote:
> > via quoted mixed case identifiers. I haven't seen any evidence that
> > basic SQL operations (select, insert, update, delete) have a bug in them.
>
> I'd peg a where clause as a basic SQL operation, and be it bug, feature,
> annoyance, whatever, it wasn't functioning because of the case of a column
> name.
>
> This worked:
> 1. rs = stmt.executeQuery("select * from response");
>
> But I wanted to do this, which didn't work:
> 2. rs = stmt.executeQuery("select * from response where questionID=16");
>
> This is how I got it to work:
> 3. rs = stmt.executeQuery("SELECT \"response\".\"questionID\",
> \"response\".\"respondentID\", \"response\".\"answer\", \"response\".\"ID\"
> FROM \"response\" WHERE "\response\".\"questionID\"=16");
How about:
4. rs = stmt.executeQuery("select * from response where \"questionID\"=16");
Quote what you must, and no more.
>
> Sorry, does anyone think the third is better than the second?
>
Yes, since it conforms to SQL92. If you'd bothered to check the postgresql
log, you would have seen error messages of the form:
test=# select * from response where questionID=16;
ERROR: Attribute 'questionid' not found
Which would have clued you into what the problem was. Alternatively,
try your queries in psql so you can _see_ the responses.
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
On Mon, Nov 26, 2001 at 01:02:53PM -0800, Nate Gelbard wrote: > > > of a complaint on how SQL is case insensitive and if you have created > > your objects with quoted mixed case identifiers you need to accessthem > > via quoted mixed case identifiers. I haven't seen any evidence that > > I am mistaken. This is actually a bug in the applications that are > building SQL statements without quotes around tablenames of mixedcase. Bad apps are everywhere. I've met quite a few that make the Oracle assumption about casefolding: MixedCase == MIXEDCASE i.e. Oracle upcases everything. The other place you'll hit this is NTFS (i.e. NT file system): names are casepresevering but _not_ case sensitive, so MixEdCase == MIXEDCASE == mixecase all conflict, but will be stored (and returned) however the app. (or user) spelled it the first time. Another example of being overly helpful. Ross
On Mon, Nov 26, 2001 at 04:24:46PM -0500, Colin Freas wrote:
>
> Barry,
>
> I'm just saying that it's frustrating because in one of my earlier checks to
> make sure my SQL was ok, I used Access 2000 with some linked tables to my
Ah, here's the real problem: MS-Access 'helpfully' quotes all the identifiers
for you, behind your back. Kind of like the 'helpful' things Word does as you
type. I got bit by the same behavior: I created same tables from inside
Access, and got to spend some time finding all the MiXedCase BuGs in PostgreSQL
v 6.2 (or so).
> Postgres db. When I ran 'select * from response where questionID=16'
> through that, it worked without a hitch (actually, the 16 needed to be
> '16'), so I figured the problem was somewhere else. Plus it was such simple
> SQL, I mean, what could be wrong with it? I'd be the first to admit I
> don't... well, I didn't... know the nuances of the SQL9X casing rules. My
> issue, as stated in my initial note, was more that nobody (jdbc, postgres,
> tomcat) told me that my where clause was, essentially, bogus. I mean, what
> am I catching that SQLException for?
>
> And Bruce wrote...
> >rs = stmt.executeQuery("select * from response where \"questionID\"=16");
>
> That works.
Indeed.
BTW, as long as you stay away from software that meses with your queries,
you can get away with writing:
select * from response where questionID=16;
As long as the table was created the same way (i.e., no quotes):
create table response (questionID int , ...)
The only problem is that the column name returned by the system will
be questionid.
Ross
On Mon, 26 Nov 2001 16:24:46 -0500, you wrote: >I just think mixed case identifiers are easier on the eyes. That's fine. Since SQL is case insensitive you can write the names anyway you like. >Postgres stores the identifiers in a case insensitve manner by default Yes. >but if you mix case when creating the tables or what have you, it stores >them as case sensitive. No, only when you quote the names when creating the tables. Its up to you. If you don't quote them your problems are gone, IIUYC. Regards, René Pijlman <rene@lab.applinet.nl>