Обсуждение: BUG #4617: JDBC Drivers 8.2/8.3 return no ResultSet

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';

On Thu, 15 Jan 2009, Raymond L. Naseef wrote:

>
> The following bug has been logged online:
>
> Bug reference:      4617
> 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:
>
> 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';
>

What's happening here is that you are actually issuing four commands, not
a single query.  The newer drivers will return a status for each command
executed, and won't just give you the final select result.  For
multi-command statements you should use Statement.execute() and then
getResultSet or getUpdateCount until getMoreResults is false.

Kris Jurka

Excellent.

So useful getUpdateCount(), getResultSet(), and getMoreResults() return
very detailed data about each query in a statement.

Updating driver also found place we were using ps.setString(value) for a
parameter for ID = ?, and we got error asking for proper use of
ps.setInt(value).


Kris Jurka wrote:
>
>
> On Thu, 15 Jan 2009, Raymond L. Naseef wrote:
>
>>
>> The following bug has been logged online:
>>
>> Bug reference:      4617
>> 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:
>>
>> 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';
>>
>
> What's happening here is that you are actually issuing four commands,
> not a single query.  The newer drivers will return a status for each
> command executed, and won't just give you the final select result.  For
> multi-command statements you should use Statement.execute() and then
> getResultSet or getUpdateCount until getMoreResults is false.
>
> Kris Jurka
>