#451 Query returns no rows when run through JDBC

1.3 Dev Q
open
nobody
None
5
2013-12-02
2013-09-03
Abbas Butt
No

These queries have been taken form puzzle # 3 of JOE CELKO’S SQL PUZZLES & ANSWERS Second Edition.

Steps to reproduce

Step 1: Create the tables

CREATE TABLE Clock AS SELECT CAST('2001-01-07' AS DATE)
+ (n || ' minute')::INTERVAL as clock_time FROM
generate_series(0, 1440*30) n;

CREATE TABLE Procs( proc_id INTEGER, anest_name VARCHAR(64),
start_time TIMESTAMP, end_time TIMESTAMP );

Step 2: Insert rows

INSERT INTO Procs VALUES( 10, 'Baker', '01-07-01 08:00', '01-07-01 11:00');

INSERT INTO Procs VALUES( 20, 'Baker', '01-07-01 09:00', '01-07-01 13:00');

INSERT INTO Procs VALUES( 30, 'Dow' , '01-07-01 09:00', '01-07-01 15:30');

INSERT INTO Procs VALUES( 40, 'Dow' , '01-07-01 08:00', '01-07-01 13:30');

INSERT INTO Procs VALUES( 50, 'Dow' , '01-07-01 10:00', '01-07-01 11:30');

INSERT INTO Procs VALUES( 60, 'Dow' , '01-07-01 12:30', '01-07-01 13:30');

INSERT INTO Procs VALUES( 70, 'Dow' , '01-07-01 13:30', '01-07-01 14:30');

INSERT INTO Procs VALUES( 80, 'Dow' , '01-07-01 18:00', '01-07-01 19:00');

Step 3: Run the query

SELECT X.anest_name, MAX(X.proc_tally) FROM (SELECT P1.anest_name, COUNT(DISTINCT proc_id) FROM Procs AS P1, Clock AS C WHERE C.clock_time BETWEEN P1.start_time AND P1.end_time GROUP BY P1.anest_name) AS X(anest_name, proc_tally) GROUP BY X.anest_name ORDER BY 1;

It results in the following rows

anest_name | max

------------+-----

Baker | 2

Dow | 6

(2 rows)

However the same query when run through JDBC returns no rows

pstmt = conn.prepareStatement("SELECT X.anest_name, MAX(X.proc_tally) "
+"FROM (SELECT P1.anest_name, "
+"COUNT(DISTINCT proc_id) FROM Procs AS P1, Clock AS C WHERE "
+"C.clock_time BETWEEN P1.start_time AND P1.end_time GROUP BY P1.anest_name) "
+"AS X(anest_name, proc_tally) GROUP BY X.anest_name ORDER BY 1");
rs = pstmt.executeQuery();

if (!rs.next() )
{
System.out.println("no data");
}
else
{
rs.last();
int rowCount = rs.getRow();
}

The above code fragment prints
no data

Discussion

  • Koichi Suzuki
    Koichi Suzuki
    2013-12-02

    Could be related to binary mode transfer in libpq, which we do not support. If it is, then we have a workaround to prevent binary mode used.

     
  • Koichi Suzuki
    Koichi Suzuki
    2013-12-02

    • Group: 1.2 Dev Q --> 1.3 Dev Q