Result set is empty but query works in Postgres

I’m trying to get some results from Postgres into my Java application. As of now, I always used PreparedStatements with ResultSets to get data from my database in memory. But today my code refuses to work, although I use the same code as usual. Here is my piece of code:

PreparedStatement stmtExplicitCollections = this.originalGraph.getPreparedStatement(
    "(" +
    "SELECT ce1.collId " +
    "FROM quotient_summary_edges qse, collection_to_ec ce1, collection_to_ec ce2 " +
    "WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget = ce2.ecId " +
    "GROUP BY ce1.collId " +
    "HAVING COUNT(ce2.collId) = 1" +
    ") INTERSECT (" +
    "SELECT ce1.collId " +
    "FROM quotient_summary_edges qse, collection_to_ec ce1 " +
    "WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget IN (" + StringUtils.join(interestingEquivalenceClasses, ",") + ")" +
    ");");
log.info(stmtExplicitCollections.toString());
ResultSet rsExplicitCollections = stmtExplicitCollections.executeQuery();
ArrayList<Integer> explicitCollectionsIds = new ArrayList<>();
while (rsExplicitCollections.next()) {
    explicitCollectionsIds.add(rsExplicitCollections.getInt(1));
}
log.info(explicitCollectionsIds);

Here is the log:

2022-03-29 13:35:03 INFO  RecordsCollectionsComputation:307 - (SELECT ce1.collId FROM quotient_summary_edges qse, collection_to_ec ce1, collection_to_ec ce2 WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget = ce2.ecId GROUP BY ce1.collId HAVING COUNT(ce2.collId) = 1) INTERSECT (SELECT ce1.collId FROM quotient_summary_edges qse, collection_to_ec ce1 WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget IN (4,0,1,5))
2022-03-29 13:35:03 INFO  RecordsCollectionsComputation:313 - []

The ArrayList explicitCollectionsIds is empty/not filled and if(rsExplicitCollections.next()) returns false.

EDIT: Also, the query works in Postgres as shown below:

cl_xml=# (SELECT ce1.collId FROM quotient_summary_edges qse, collection_to_ec ce1, collection_to_ec ce2 WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget = ce2.ecId GROUP BY ce1.collId HAVING COUNT(ce2.collId) = 1) INTERSECT (SELECT ce1.collId FROM quotient_summary_edges qse, collection_to_ec ce1 WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget IN (4,0,1,5));

 collid
--------
      0
      1
(2 rows)

Can someone help me? Thanks in advance!

Answer

I finally found the issue thanks to @Antoniossss! The problem was that I was working on uncommitted changes, i.e. the table collection_to_ec is not filled when I was executing the query (it is filled later)

Advertisement

Answer