On Fri, 13 May 2016 15:13:01 +0100 Simon Slavin <slavins at bigfraud.org> wrote:
> On 13 May 2016, at 3:07pm, dandl <david at andl.org> wrote: > > > I have no deep knowledge of standard SQL. > > I used to know SQL92 very well. There's no facility for doing > anything like LIMIT or OFFSET in it. You had to use your programming > language to work your way through all the results and skip the ones > you didn't want. I suggest the reason LIMIT hasn't been standardized is that it's contrary to the fundamental idea that rows in a table have no meaningful order. SQL doesn't honor relational theory with complete fidelity, but at least that horse is still in the barn. The problem with LIMIT is it's not based in the data. Cutting off results at some arbitrary N tells you *nothing* about the data other than that N or more rows met the criteria. Note that predicate logic has constructs for "for all" and "there exists" , but not "are some"! I have yet to see a query using LIMIT 1 posted on this list that cannot be expressed -- better, IMO -- with min(). Queries that limit the results to "top N" to support things like pagination inevitably include assumptions about transactionality (or lack thereof) that are either invalid or ill-considered. Every one would be better served either by just fetching the needed rows as required (and letting pending rows pend), or by supplying the last "high" value as a minimum for the WHERE clause instead of an OFFSET. Were I a fan of conspiracies, I'd suspect the LIMIT-OFFSET constructs were invented by antilogicians to prevent learning and hobble performance. By the way, i'm also a LIMIT club member, with limits. I use it for convenience on the command line while exploring data. It's great for that, in the absence of pager support. Maybe keeping it a little "weird" will help remind new developers to use it as a convenience instead of a crutch. --jkl
