pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem 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>
1999-12-28 20:13 ` Re: [SQL] Problem with limit / union / etc. Tom Lane <tgl@sss.pgh.pa.us>
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 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>
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
* 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
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