pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with limit / union / etc.
3+ messages / 3 participants
[nested] [flat]

* Problem with limit / union / etc.
@ 1999-12-28 16:17  M. Scott Smith <mssmit1@afterlife.ncsc.mil>
  0 siblings, 1 reply; 3+ messages in thread

From: M. Scott Smith @ 1999-12-28 16:17 UTC (permalink / raw)
  To: pgsql-sql

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;



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Problem with limit / union / etc.
@ 1999-12-28 18:29  Alain.Tesio@sip.fr
  0 siblings, 0 replies; 3+ messages in thread

From: Alain.Tesio@sip.fr @ 1999-12-28 18:29 UTC (permalink / raw)
  To: M. Scott Smith <mssmit1@afterlife.ncsc.mil>; pgsql-sql




--- "M. Scott Smith" <mssmit1@afterlife.ncsc.mil> wrote:
> 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;

Maybe the limit is applied only on the second query.

What about the following query ?

Alain

==

select
  event.title,date_start
into
  temp tmp_events
from
  event, event_url, url
where
  date_start >= 'now'::date
  and event.oid = event_url.eventid
  and url.oid = event_url.urlid
limit $total;

insert
  tmp_events
select
  event.title,date_start
from
  event,event_url
where
  date_start >= 'now'::date
  and event.oid not in
   (select distinct event_url.eventid from event_url)
limit $total;

select
  title
from
  tmp_events
order by event.date_start asc limit $total;

drop table tmp_events;






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Problem with limit / union / etc.
@ 1999-12-28 20:13  Tom Lane <tgl@sss.pgh.pa.us>
  parent: M. Scott Smith <mssmit1@afterlife.ncsc.mil>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 1999-12-28 20:13 UTC (permalink / raw)
  To: M. Scott Smith <mssmit1@afterlife.ncsc.mil>; +Cc: pgsql-sql

"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:

inline patch 1: src/backend/rewrite/rewriteHandler.c (+6/-0 lines) download inline-1.patch*** 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 ^ permalink raw reply [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-12-28 20:13 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-28 16:17 Problem with limit / union / etc. M. Scott Smith <mssmit1@afterlife.ncsc.mil>
1999-12-28 20:13 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-28 18:29 Re: [SQL] Problem with limit / union / etc. Alain.Tesio@sip.fr

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox