Re: Unexpected behavior from HSQLDB

You need to provide the CREATE TABLE and CREATE INDEX
statements together with some sample data. Use the Help forum
at SourceForge to report this and attach the required
information.

Fred

On Fri, Aug 22, 2014, at 22:49, Chris Pratt wrote:

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*)

---------------------------------------------------------------
---------------

Slashdot TV.

Video for Nerds.  Stuff that matters.

[1]http://tv.slashdot.org/

_______________________________________________

Hsqldb-user mailing list

[2][email protected]

[3]https://lists.sourceforge.net/lists/listinfo/hsqldb-user

References

1. http://tv.slashdot.org/
2. mailto:[email protected]
3. https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user