SQL Query Will Not Execute SQLite, Java

Question

Problem Synopsis: When attempting to execute a SQL query in Java with a SQLite Database, the SQL statement fails to return from the execute() or executeQuery() method. In other words, the system "hangs" when executing this SQL statement.

Question: What am I doing wrong to explain why the ResultSet never "returns?"

TroubleShooting I tried to narrow the problem and the problem seems to be with the Java execute() or executeQuery(). A ResultSet never seems to return. For example, I tried executing exactly the same query directly in SQLite (that is, using a SQLite DB manager). The query (outside Java) executes in about 5ms and returns the valid result set.

NOTE: No exception is thrown. The system merely seems to "hang" and becomes unresponsive until a manual kill. (waiting more than 10 minutes.)

Code: I heavily edited this code to make the problem simpler to see. (In production, this uses Prepared Statements. But, the error occurs in both methods--straight Statement and prepared Statement versions.)

Basically, the SELECT returns a single DB item so the user can review that item.

Statement st = conn.createStatement() ;
ResultSet rs = st.executeQuery("SELECT DISTINCT  d1.id, d1.sourcefullfilepath, " +
    "d1.sourcefilepath, d1.sourcefilename, d1.classificationid, d1.classid, " +
    "d1.userid  FROM MatterDataset,   (SELECT MatterDataset.id, " + 
    "MatterDataset.sourcefullfilepath, MatterDataset.sourcefilepath, " +
    "MatterDataset.sourcefilename, MatterDataset.matterid   , " +
    "DocumentClassification.classificationid, DocumentClassification.classid," +
    " DocumentClassification.userid   FROM MatterDataset    " +
    "LEFT JOIN DocumentClassification ON " +
    "DocumentClassification.documentid = Matterdataset.id    " +
    "WHERE (   DocumentClassification.classid = 1 OR  " +
    "DocumentClassification.classid = 2 )   AND " +
    "DocumentClassification.userid < 0    AND " +
    "MatterDataset.matterid = \'100\'   ) AS d1    " +
    "LEFT JOIN PrivilegeLog ON " +
    "d1.id = PrivilegeLog.documentparentid AND " +
    "d1.matterid = PrivilegeLog.matterid  " +
    "WHERE PrivilegeLog.privilegelogitemid IS NULL  " +
    "AND MatterDataset.matterid = \'100\'   " +
    "ORDER BY d1.id  LIMIT 1 ;") ;

Configuration: Java 6, JDBC Driver = Xerial sqlite-jdbc-3.7.2, SQLite 3, Windows

Update Minor revision: as I continue to work with this, adding a MIN(d1.id) to the beginning of the SQL statement at least returns a ResultSet (rather than "hanging"). But, this is not really what I wanted as the MIN obviates the LIMIT function.

Statement st = conn.createStatement() ;
ResultSet rs = st.executeQuery("SELECT DISTINCT  MIN(d1.id), d1.id,
    d1.sourcefullfilepath, " +
    "d1.sourcefilepath, d1.sourcefilename, d1.classificationid, d1.classid, " +
    "d1.userid  FROM MatterDataset,   (SELECT MatterDataset.id, " + 
    "MatterDataset.sourcefullfilepath, MatterDataset.sourcefilepath, " +
    "MatterDataset.sourcefilename, MatterDataset.matterid   , " +
    "DocumentClassification.classificationid, DocumentClassification.classid," +
    " DocumentClassification.userid   FROM MatterDataset    " +
    "LEFT JOIN DocumentClassification ON " +
    "DocumentClassification.documentid = Matterdataset.id    " +
    "WHERE (   DocumentClassification.classid = 1 OR  " +
    "DocumentClassification.classid = 2 )   AND " +
    "DocumentClassification.userid < 0    AND " +
    "MatterDataset.matterid = \'100\'   ) AS d1    " +
    "LEFT JOIN PrivilegeLog ON " +
    "d1.id = PrivilegeLog.documentparentid AND " +
    "d1.matterid = PrivilegeLog.matterid  " +
    "WHERE PrivilegeLog.privilegelogitemid IS NULL  " +
    "AND MatterDataset.matterid = \'100\'   " +
    "ORDER BY d1.id  LIMIT 1 ;") ;

Answer 1

What a messy SQL statement (sorry)! I don't know SQLite, but why not simplify to:

SELECT DISTINCT md.id, md.sourcefullfilepath, md.sourcefilepath, md.sourcefilename, 
                dc.classificationid, dc.classid, dc.userid
FROM MatterDataset md
LEFT JOIN DocumentClassification dc
       ON dc.documentid = md.id
      AND (dc.classid = 1 OR dc.classid = 2 )
      AND dc.userid < 0
LEFT JOIN PrivilegeLog pl
       ON md.id = pl.documentparentid 
      AND md.matterid = pl.matterid
WHERE pl.privilegelogitemid IS NULL
  AND md.matterid = \'100\'
ORDER BY md.id LIMIT 1 ;

I was uncertain whether you wanted to LEFT JOIN or INNER JOIN to DocumentClassification (using LEFT JOIN and then put requirements on classid and userid in the WHERE statement is - in my opinion - contradictory). If DocumentClassification has to exist, then change to INNER JOIN and put the references to classid and userid into the WHERE clause, if DocumentClassification may or may not exist in your result set, then keep the query as I suggested above.