[Biojava-dev] 'select top 0 *' in HypersonicDBHelper

[Bradford Powell]

hsqldb's SQL extension "select top n" doesn't work the way I (or
apparently Len Trigg) would expect when n = 0. Instead of returning a
result with zero rows, it returns all rows. This is their documented
behaviour (http://hsqldb.sourceforge.net/web/hsqlDocsFrame.html), as
"top n" is equivalent to "limit 0 n" which is special-cased for n=0 to
mean "start with the zeroth result and return rows with no limit".

The end result of this is that the statement
'"select top 0 * from " + tablename'
in containsTable() of HypersonicDBHelper.java is equivalent to to
'"select * from " + tablename', which returns can a lot of
unnecessary rows considering the intent is just to see if the table
exists.

The easiest change would be to change the query to
'"select top 1 * from " + tablename',
(i.e. just change '0' to '1') which will return zero or one
rows. Alternatively, the presence of the table in question could be
determined by jdbc metadata or someone could lobby the hsqldb folks to
change the behaviour of their sql extension.

-- bradford powell