Thread: Problem with limit / union / etc.

Hi!
  It's been awhile since I've done SQL, and I'm having a problem with
a query.  I thought I had this working but can't figure out what's
wrong now.  What I'm trying to do is pull a list of events from a
database, and limit the number of events returned.  The query is
a little complicated because three tables are involved:

create table event
( title               varchar(256) not null, date_start    date not null
);

create table url
( title...
);

create table event_url
( eventid   OID, urlid        OID
);
  An event may (theoretically) have zero or more URL's associated
with it; which is why the url isn't stored directly in the event table.
The event_url table relates URL(s) with an event.
  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.  I've tried
placing the order by in other places or multiple places to no avail.
  Should this be working?  Am I doing something stupid?  Any
help would be greatly appreciated!  I suppose I could output the
results of this query into a temporary table and then select all
from that table with limit, but it doesn't seem like that should
be necessary.  (Separately, are there better ways to reflect
"one or more" entities in a table (such as URLs) without resorting
to multiple tables?)

Thanks!

- Scott

select event.title
from event, event_url, url
where date_start >= 'now'::date and event.oid = event_url.eventid and url.oid = event_url.urlid

union

select event.title
from event,event_url
where date_start >= 'now'::date and event.oid not in  (select distinct event_url.eventid from event_url)

order by event.date_start asc limit $total;

"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