0

I am running Tomcat 9.0 with a PostgreSQL 10.2 Database, using the Tomcat JDBC Pool configured with the org.postgresql.Driver.

I have the following simple query, which scans a small reference table for values:

SELECT DISTINCT quality_rank, quality_desc 
  FROM flooring_quality_type 
ORDER BY quality_rank

The table has exactly 3 rows, and the pgAdmin Query Tool returns those same 3 rows regardless of the DISTINCT keyword being used.

I am using the SQL in the following code to add the values to a list (currently not using the rank column, before I was using putIfAbsent with a map to get around the bug).

String QUALITIES_SQL = "SELECT DISTINCT quality_rank, quality_desc " + 
    " FROM flooring_quality_type ORDER BY quality_rank";

try (Connection con = DataSourceKeeper.getConnection();
     PreparedStatement mapStmt = con.prepareStatement(PRICE_MAP_SQL); // Another query, not exhibiting problem
     PreparedStatement qualStmt = con.prepareStatement(QUALITIES_SQL)) {

    try (ResultSet qualRs = mapStmt.executeQuery()) {
        while (qualRs.next()) {
            System.out.println("Got another result");
            qualities.add(qualRs.getString("quality_desc"));
        }
    }

    // Other query executed here ... SNIP
    // The mapStmt does not cause the same issue, but uses joins.
    // Problem occurs regardless of the order of statement execution.

} catch (SQLException e) {
    throw new UserFacingException();
}

I am looking at JSON output of the list, as well as the printed message. While the table has 3 rows, I can see 12 entries. They are ordered in non-distinct groups of 3, as if the query was repeated 4 times in order, returned to the same ResultSet.

Any idea what could be causing this and how to solve it? Am I doing something wrong with my JDBC code?

dtgman
  • 1
  • 2
  • As a note, the DataSourceKeeper just holds the DataSource obtained via JNDI. – dtgman Feb 19 '18 at 23:47
  • Instead of posting extra comments you can edit your question if you want to add details – Devstr Feb 20 '18 at 00:04
  • 1
    Could the whole code be executed several times? Do you use multiple threads? I recommend stripping your code to just that query and checking the contents of qualities var. Debugger might also help. – Devstr Feb 20 '18 at 00:08
  • 1
    Shouldn't you call executeQuery on qualStmt, not mapStmt? – Honeyboy Wilson Feb 20 '18 at 00:09
  • @HoneyboyWilson That's it... I've been coding for too long. Thank you for finding that. – dtgman Feb 20 '18 at 00:26

1 Answers1

0

HoneyboyWilson found the problem. Was calling the wrong statement but it contained the correct columns, so was not producing any exceptions.

dtgman
  • 1
  • 2