Quantcast

Unexpected behavior from HSQLDB

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Unexpected behavior from HSQLDB

Chris Pratt
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.
http://tv.slashdot.org/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Unexpected behavior from HSQLDB

Fred Toussi-2
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.
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
Slashdot TV.  
Video for Nerds.  Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...