Andrew Sullivan <ajs@crankycanuck.ca> writes: > On Fri, Dec 14, 2007 at 01:47:23AM +0000, Gregory Stark wrote: >> Huh, I was all set to post an example of a useful application of it but then >> apparently I'm wrong and it doesn't work: > I dimly remember some discussion of this issue once before, maybe a year > ago. My memory isn't what it was, and I can't find it by trolling archives, > but I recall Tom saying that it was dumb, yes, but don't do that, because > there's some reason not to change it. I know, helpful search terms R me. Hmm ... I don't recall much either. The code in nodeLimit.c just silently replaces a negative input value by zero. It'd certainly be possible to make it throw an error instead, but what the downsides of that might be aren't clear. I guess that on purely philosophical grounds, it's not an unreasonable behavior. For example, "LIMIT n" means "output at most n tuples", not "output exactly n tuples". So when it outputs no tuples in the face of a negative limit, it's meeting its spec. If you want to throw an error for negative limit, shouldn't you logically also throw an error for limit larger than the actual number of rows produced by the subplan? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

"Jonah H. Harris" <jonah.harris@gmail.com> writes: > Don't we have any similar usability cases in the system like this, > where negatives are not allowed only for the sake of it being an > insane setting? I'm tired, but I thought we did. Yeah, probably. It's the kind of thing where the call is close enough that it might be made differently by different people. After thinking about it for a bit, the only downside I can think of is that throwing an error might create an unexpected corner case for code that computes a LIMIT value on-the-fly and might sometimes come up with a slightly negative value. But you could always do LIMIT greatest(whatever, 0) so that seems like a weak argument. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

Simon Riggs <simon@2ndquadrant.com> writes: > I even found an existing, unused error message called > ERRCODE_INVALID_LIMIT_VALUE That's a bad idea I think. That code is defined by SQL99. I can't find anyplace that they specify what it should be raised for, but we can be pretty confident that it's not meant for LIMIT. I think we should just use INVALID_PARAMETER_VALUE. How do people feel about applying this to 8.3, rather than holding it? One possible objection is that we're past string freeze, but I noted Peter doing some message editorializing as recently as today, so it would seem a slushy freeze at best. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly

Andrew Sullivan <ajs@crankycanuck.ca> writes: > On Fri, Dec 14, 2007 at 06:42:24PM -0500, Tom Lane wrote: >> How do people feel about applying this to 8.3, rather than holding it? > To me, this is a feature change, and therefore should be held. Well, I wouldn't advocate making it in a minor release, but it's not clear how that translates into saying it can't go into 8.3. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Bruce Momjian <bruce@momjian.us> writes: > Since we got LIMIT/OFFSET from MySQL, would someone tell us how MySQL > behaves in these cases? Not very well, at least not in mysql 5.0.45: mysql> select * from t limit -2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2' at line 1 mysql> select * from t limit 2 offset -2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2' at line 1 This behavior suggests that they can't even deal with LIMIT/OFFSET values that aren't simple integer literals ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend

Gregory Stark <stark@enterprisedb.com> writes: > "Tom Lane" <tgl@sss.pgh.pa.us> writes: >> This behavior suggests that they can't even deal with LIMIT/OFFSET >> values that aren't simple integer literals ... > I suppose when they added these features I think they didn't have subqueries, > so there wasn't really much useful that could be done with arbitrary > expressions here. Being able to do "LIMIT 1+1" doesn't actually add anything. Sure. I think our first implementation of LIMIT was similarly constrained. It's just amusing that they haven't moved past that, despite having had the feature first ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org