Result set is empty but query works in Postgres-CodePudding

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!

CodePudding user response：

It looks to me as if your application fires a query SELECT id FROM table, then receives an empty result set. No exception thrown.

That means the communication to the database is fine, query execution is fine but there is just no data found.

Stop looking at the code or changes in the network. Start looking at your database content. What is in the tables?

CodePudding user response：

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)