JDBC rewriting a bad query? - Mailing list pgsql-jdbc

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