title: SQL Query Will Not Execute SQLite, Java
body: <p><strong>Problem Synopsis:</strong>
  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.</p>
  
  <p><strong>Question:</strong>
  What am I doing wrong to explain why the ResultSet never "returns?"</p>
  
  <p><strong>TroubleShooting</strong>
  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. </p>
  
  <p>NOTE: No exception is thrown. The system merely seems to "hang" and becomes unresponsive until a manual kill. (waiting more than 10 minutes.)</p>
  
  <p><strong>Code:</strong>
  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.) </p>
  
  <p>Basically, the SELECT returns a single DB item so the user can review that item.</p>
  
  <pre><code>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 &lt; 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 ;") ;
  </code></pre>
  
  <p><strong>Configuration:</strong>
  Java 6,
  JDBC Driver = Xerial sqlite-jdbc-3.7.2,
  SQLite 3,
  Windows</p>
  
  <p><strong>Update</strong>
  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.</p>
  
  <pre><code>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 &lt; 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 ;") ;
  </code></pre>