Thread: JDBC rewriting a bad query?
I have an issue that I am trying to understand. We had an issue where we had a query with a syntax error, but we would not get any errors in the log files. It turns out that the query was being shortened by the time it got to the server and not causing an error. The version of jdbc driver we are running is 8.2-507 QUERY FROM WEB SERVER: ( the problem is one too many close parens ')' ) 14:01:43,922 DEBUG [syr/lloyd/9333C6.W01] AvailabilityDAOSql: SET search_path = 'public' 14:01:44,307 DEBUG [syr/lloyd/9333C6.W01] AvailabilityDAOSql: SELECT date, enddate, status, threshdesc, pollgrpid, pollid, REPLACE(polldata,'dhct:','') AS pollname FROM outagelog JOIN threshold USING(thresholdid, pollid) JOIN poll USING (pollid) WHERE (status != 3) AND (lower(threshdesc) !~ 'dropped') AND pollid IN (SELECT pollid FROM poll WHERE (rrdtypeid = 73) AND (split_part(polldata,':',2)='IR')) AND (dsnum=2)) AND ((enddate > 1205640000) OR (enddate IS NULL)) AND (date < 1205726400) ORDER BY date, status DESC DEBUG FROM PGSQL SERVER: ( Chopped off everything after and including bad ')' ) Mar 17 14:01:43 brain03 postgres[9174]: [5-1] LOG: execute <unnamed>: SET search_path = 'public' Mar 17 14:01:44 brain03 postgres[9174]: [6-1] LOG: execute <unnamed>: SELECT date, enddate, status, threshdesc, pollgrpid, pollid, REPLACE(polldata,'dhct:','') AS pollname Mar 17 14:01:44 brain03 postgres[9174]: [6-2] FROM outagelog JOIN threshold USING(thresholdid, pollid) JOIN poll USING (pollid) WHERE (status != 3) AND (lower(threshdesc) !~ Mar 17 14:01:45 brain03 postgres[9174]: [6-3] 'dropped') AND pollid IN (SELECT pollid FROM poll WHERE (rrdtypeid = 73) AND (split_part(polldata,':',2)='IR')) AND Mar 17 14:01:45 brain03 postgres[9174]: [6-4] (dsnum=2) WEB QUERY IN PSQL: Welcome to psql 7.4.5, the PostgreSQL interactive terminal. SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) syr=> SELECT date, enddate, status, threshdesc, pollgrpid, pollid, REPLACE(polldata,'dhct:','') AS pollname FROM outagelog JOIN threshold USING(thresholdid, pollid) JOIN poll USING (pollid) WHERE (status != 3) AND (lower(threshdesc) !~ 'dropped') AND pollid IN (SELECT pollid FROM poll WHERE (rrdtypeid = 73) AND (split_part(polldata,':',2)='IR')) AND (dsnum=2)) AND ((enddate > 1205640000) OR (enddate IS NULL)) AND (date < 1205726400) ORDER BY date, status DESC; ERROR: syntax error at or near ")" at character 366 We have found our issue, but it would have been nice to have had an error. Could be we just need to be more conservative in our use of parens with jdbc. Thanks, Woody ---------------------------------------- iGLASS Networks 211-A S. Salem St Apex NC 27502 (919) 387-3550 x813 www.iglass.net
On Mon, 17 Mar 2008, Woody Woodring wrote: > I have an issue that I am trying to understand. We had an issue where we > had a query with a syntax error, but we would not get any errors in the log > files. It turns out that the query was being shortened by the time it got > to the server and not causing an error. The version of jdbc driver we are > running is 8.2-507 Are you sure you are running 8.2-507? This bug was fixed early in the 8.2 series: Version 8.2-dev501 (2006-02-09): When performing replace processing we must continue processing until we hit the end of a user supplied query, not just once we've detected the end of a valid query. Consider the example: SELECT a FROM t WHERE (1>0)) ORDER BY a; We must send the whole query to the backend, not just the section before the last closing parenthesis. (jurka) Thanks to Senden Kris. Kris Jurka
I am fairly sure, or at least that is what is given in the comments of our CVS tree. I will upgrade to the 8.3 driver and see if it goes away. Thanks for you help > Are you sure you are running 8.2-507? This bug was fixed > early in the 8.2 > series:
> > Are you sure you are running 8.2-507? This bug was fixed > early in the > > 8.2 > > series: We were able to reproduce the issue with postgresql-8.3-603.jdbc3.jar. Please let me know if I can be of any assistance in finding the issue. Thanks, Woody
On Wed, 19 Mar 2008, Woody Woodring wrote: > We were able to reproduce the issue with postgresql-8.3-603.jdbc3.jar. > Please let me know if I can be of any assistance in finding the issue. > If you can send a self-contained test case, I'd be happy to look at it. I tried the attached test case and I get the expected: Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" Position: 356 Kris Jurka
Attachment
Kris, Sorry for wasting your time. We ran your sample and it worked. Upon further investigation, our tomcat web servers have a copy of a postgres jar file that it must use instead of the one with our webapp. Who knows how old it is. We upgraded that one and the problem went away. Thanks for your help, Woody > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Kris Jurka > Sent: Wednesday, March 19, 2008 11:35 PM > To: Woody Woodring > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] JDBC rewriting a bad query? > > > > On Wed, 19 Mar 2008, Woody Woodring wrote: > > > We were able to reproduce the issue with > postgresql-8.3-603.jdbc3.jar. > > Please let me know if I can be of any assistance in finding > the issue. > > > > If you can send a self-contained test case, I'd be happy to > look at it. I tried the attached test case and I get the expected: > > Exception in thread "main" org.postgresql.util.PSQLException: ERROR: > syntax error at or near ")" > Position: 356 > > > Kris Jurka >