[Hsqldb-user] Unexpected behavior from HSQLDB

[Chris P. <the...@gm...>]

We're running into a bit of a head scratcher with HSQLDB.  We were
initially using this query

SELECT s.* FROM SECTIONS s JOIN SCHOOLSTAFF ss ON s.TEACHER=ss.ID JOIN
TERMS t ON s.TERMID=t.ID AND s.SCHOOLID=t.SCHOOLID WHERE
ss.USERS_DCID=:dcid AND t.YEARID IN (SELECT y.YEARID FROM TERMS y WHERE
y.SCHOOLID=t.SCHOOLID AND NOT((:end < t.FIRSTDAY) OR (t.LASTDAY < :start))
AND y.ISYEARREC <> 0);

Which worked fine in both Oracle and HSQLDB, but we found that it was
missing a couple of corner cases.  So we updated the query to:

SELECT s.* FROM SECTIONS s JOIN SCHOOLSTAFF ss ON s.TEACHER=ss.ID JOIN
TERMS t ON s.TERMID=t.ID AND s.SCHOOLID=t.SCHOOLID WHERE
ss.USERS_DCID=:dcid AND t.YEARID IN (SELECT y.YEARID FROM TERMS y WHERE
y.SCHOOLID=t.SCHOOLID AND NOT((:end < t.FIRSTDAY) OR (t.LASTDAY < :start))
AND y.ISYEARREC <> 0);

Which gives us the results we're looking for in Oracle, but completely
hangs HSQLDB... forever...  I did some research on the Google and found out
that we should use HSQLDB's MVCC model, so we added ;hsqldb.tx=mvcc to our
JDBC URL's and that prevented the hanging, but now HSQLDB is just returning
an empty ResultSet, which is not correct.  Can anyone provide any insight
into how to work around this problem?  Thanks.
  (*Chris*)