Re: [SQL] Problem with limit / union / etc.

"M. Scott Smith" <mssmit1@afterlife.ncsc.mil> writes:
>    What I want to do is pull out upcoming events, limiting the number
> returned.  The following query is my attempt to do that.  The
> first select selects all events that have a URL associated with
> them; the second query selects all events that DON'T have a URL
> associated with them; the results are combined together.  My
> hope is that the order by/limit clause will limit the results returned
> after they are combined, but it seems to be ignoring this.

Yeah, there's a bug in 6.5.* that causes it to ignore LIMIT on a UNION.
I fixed it a couple months ago, but there's been no new release since
then.  Here's the patch, if you want to patch your copy:

*** src/backend/rewrite/rewriteHandler.c~    Sun Jul 11 13:54:30 1999
--- src/backend/rewrite/rewriteHandler.c    Tue Oct 19 00:38:05 1999
***************
*** 2859,2864 ****
--- 2859,2866 ----     bool        isBinary,                 isPortal,                 isTemp;
+     Node       *limitOffset,
+                *limitCount;     CmdType        commandType = CMD_SELECT;     List       *rtable_insert = NIL; 
***************
*** 2909,2914 ****
--- 2911,2918 ----     isBinary = parsetree->isBinary;     isPortal = parsetree->isPortal;     isTemp =
parsetree->isTemp;
+     limitOffset = parsetree->limitOffset;
+     limitCount = parsetree->limitCount;      /*      * The operator tree attached to parsetree->intersectClause is
still
***************
*** 3094,3099 ****
--- 3098,3105 ----     result->isPortal = isPortal;     result->isBinary = isBinary;     result->isTemp = isTemp;
+     result->limitOffset = limitOffset;
+     result->limitCount = limitCount;      /*      * The relation to insert into is attached to the range table of
the
        regards, tom lane