db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeremy Boynes <jboy...@apache.org>
Subject Re: Support for SQL Limit?
Date Sat, 27 Nov 2004 20:31:13 GMT
Brian Abbott wrote:
> As far as I can tell, Derby does not currently support the SQL Limit 
> command. Am I missing something? If it doesnt, are there any plans to 
> implement it?
>  

LIMIT is really a MySQL feature not an SQL command. Different databases 
implement this in different ways as it was not standardized until SQL03 
(with window functions that I don't think anyone implements yet and 
which are really there for OLAP processing).

One problem with it is that SQL does not define the order in which rows 
are returned; even order by will not define the order for rows with the 
same sort key. This means that if you execute the same query twice you 
may get the rows returned in different orders - this is common on any 
database that uses a parallel query algorithm. As a result the data 
returned by LIMIT/OFFSET may well be non-deterministic.

Subject to these constraints, you can mimic this on the client side by 
controlling how you iterate over the ResultSet - e.g. skip offset rows, 
process limit rows and then close it. This actually gives you a solution 
that is portable between database vendors.

The downside is that the offset rows need to be evaluated and returned 
to the client only to be discarded. However, unless offset is very large 
(which would imply that perhaps you should look for a better query 
strategy anyway) this is probably not where the majority of your 
processing effort is going. Further, if you are using Derby embedded 
then you are simply duplicating what the server would need to do to 
implement offset anyway so the difference is going to be minimal.

However, if you feel that LIMIT/OFFSET is critical or useful 
functionality you might want to open a Jira item to add it as an 
enhancement.

--
Jeremy

Mime
View raw message