The following bug has been logged online:
Bug reference: 4617
Logged by: Raymond L. Naseef
Email address: naseef@egr.msu.edu
PostgreSQL version: 8.3 & 7.4
Operating system: FreeBSD
Description: JDBC Drivers 8.2/8.3 return no ResultSet
Details:
Below is query using temporary table instead of "IN" to greatly improve
performance. This worked in older JDBC driver, but no longer does. The
following were checked, making no difference:
1) Removed from query:
CREATE UNIQUE INDEX lookup_username_idx
ON lookup_username
USING btree
(username);
2) Setting auto-commit (checked with false and true, and unset).
Servers:
PostgreSQL 7.4.19 on amd64-portbld-freebsd7.0, compiled by GCC cc (GCC)
4.2.1.20070719 [FreeBSD]
PostgreSQL 8.3.5 on amd64-portbld-freebsd7.0, compiled by GCC cc (GCC)
4.2.1.20070719 [FreeBSD]
JDBC Drivers:
Result Driver
====== ===================
WORK pg74.216.jdbc3.jar
FAIL postgresql-8.2-506.jdbc3.jar
FAIL postgresql-8.3-604.jdbc3.jar
This was tested using AutoCommit default and false, using EITHER A or B:
PreparedStatement ps = connection.prepareStatement([shown_below]);
[ A ]
ps.execute();
ResultSet rs = ps.getResultSet();
RESULT:
rs == null
[ B ]
ResultSet rs = ps.executeQuery();
RESULT:
org.postgresql.util.PSQLException: No results were returned by the query.
QUERY:
------
create temporary table lookup_username (username varchar) on commit drop;
insert into lookup_username values ('naseef');
CREATE UNIQUE INDEX lookup_username_idx
ON lookup_username
USING btree
(username);
SELECT enp.username, trim(enp.full_name)
FROM egr_nis_person as enp
JOIN lookup_username as lu
ON lu.username = enp.username
WHERE enp.start_date < now() + interval '30 seconds'
AND enp.end_date > now() + interval '30 seconds';
