Thread: is it possible to use LIMIT and INTERSECT ?
Subject says all. In general I want to limit output from select ......intersect select ...... Current implementation of LIMIT doesn't support this. Are there any solutions ? Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
The only workaround I can think of is to do an INSERT.. SELECT or SELECT ... INTO TABLE with the INSERSECT, and use LIMIT on the resulting table. > Subject says all. > In general I want to limit output from > > select ...... > intersect > select ...... > > Current implementation of LIMIT doesn't support this. > Are there any solutions ? > > > > Regards, > Oleg > > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > > ************ > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Oleg Bartunov <oleg@sai.msu.su> writes:
> select ......
> intersect
> select ......
> Current implementation of LIMIT doesn't support this.
> Are there any solutions ?
The problem seems to be right about where I suspected it was...
Try the attached (line numbers are for current, probably are way off
for 6.5.*, but the code in that routine hasn't changed much).
regards, tom lane
*** src/backend/rewrite/rewriteHandler.c.orig Thu Oct 7 00:23:15 1999
--- src/backend/rewrite/rewriteHandler.c Sun Oct 17 19:18:01 1999
***************
*** 1806,1811 ****
--- 1806,1813 ---- bool isBinary, isPortal, isTemp;
+ Node *limitOffset,
+ *limitCount; CmdType commandType = CMD_SELECT; List *rtable_insert = NIL;
***************
*** 1856,1861 ****
--- 1858,1865 ---- isBinary = parsetree->isBinary; isPortal = parsetree->isPortal; isTemp =
parsetree->isTemp;
+ limitOffset = parsetree->limitOffset;
+ limitCount = parsetree->limitCount; /* * The operator tree attached to parsetree->intersectClause is
still
***************
*** 2057,2062 ****
--- 2061,2068 ---- 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
Tom, patch was applied smoothly to 6.5.2 What's the syntax ? select a.msg_id, c.status_set_date, c.title from Message_Keyword_map a, messages c, keywords d where c.status_id=1 and d.name ~* 'moon' and a.key_id=d.key_id and c.msg_id=a.msg_id intersect select a.msg_id, a.status_set_date, a.title from messages a where a.status_id = 1 and a.title~* 'moon' limit 5; produces (10 rows) select a.msg_id, c.status_set_date, c.title from Message_Keyword_map a, messages c, keywords d where c.status_id=1 and d.name ~* 'moon' and a.key_id=d.key_id and c.msg_id=a.msg_id limit 5 intersect select a.msg_id, a.status_set_date, a.title from messages a where a.status_id = 1 and a.title~* 'moon' limit 5; ERROR: parser: parse error at or near "intersect" Oleg On Sun, 17 Oct 1999, Tom Lane wrote: > Date: Sun, 17 Oct 1999 19:31:09 -0400 > From: Tom Lane <tgl@sss.pgh.pa.us> > To: Oleg Bartunov <oleg@sai.msu.su> > Cc: pgsql-hackers@postgreSQL.org > Subject: Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? > > Oleg Bartunov <oleg@sai.msu.su> writes: > > select ...... > > intersect > > select ...... > > Current implementation of LIMIT doesn't support this. > > Are there any solutions ? > > The problem seems to be right about where I suspected it was... > > Try the attached (line numbers are for current, probably are way off > for 6.5.*, but the code in that routine hasn't changed much). > > regards, tom lane > > > > *** src/backend/rewrite/rewriteHandler.c.orig Thu Oct 7 00:23:15 1999 > --- src/backend/rewrite/rewriteHandler.c Sun Oct 17 19:18:01 1999 > *************** > *** 1806,1811 **** > --- 1806,1813 ---- > bool isBinary, > isPortal, > isTemp; > + Node *limitOffset, > + *limitCount; > CmdType commandType = CMD_SELECT; > List *rtable_insert = NIL; > > *************** > *** 1856,1861 **** > --- 1858,1865 ---- > isBinary = parsetree->isBinary; > isPortal = parsetree->isPortal; > isTemp = parsetree->isTemp; > + limitOffset = parsetree->limitOffset; > + limitCount = parsetree->limitCount; > > /* > * The operator tree attached to parsetree->intersectClause is still > *************** > *** 2057,2062 **** > --- 2061,2068 ---- > 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 > > ************ > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov wrote: > > Tom, > > patch was applied smoothly to 6.5.2 > What's the syntax ? > > select a.msg_id, c.status_set_date, c.title > from Message_Keyword_map a, messages c, keywords d > where c.status_id =1 and d.name ~* 'moon' and a.key_id=d.key_id > and c.msg_id=a.msg_id > intersect > select a.msg_id, a.status_set_date, a.title from messages a > where a.status_id = 1 and a.title ~* 'moon' limit 5; > > produces (10 rows) > > select a.msg_id, c.status_set_date, c.title > from Message_Keyword_map a, messages c, keywords d > where c.status_id =1 and d.name ~* 'moon' and a.key_id=d.key_id > and c.msg_id=a.msg_id limit 5 > intersect > select a.msg_id, a.status_set_date, a.title from messages a > where a.status_id = 1 and a.title ~* 'moon' limit 5; > As the limit is applied to the final result, I guess you can have only one LIMIT per query. So try removing the limit 5 before intersect . ----------- Hannu
On Mon, 18 Oct 1999, Hannu Krosing wrote: > Date: Mon, 18 Oct 1999 08:22:31 +0000 > From: Hannu Krosing <hannu@tm.ee> > To: Oleg Bartunov <oleg@sai.msu.su> > Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgreSQL.org > Subject: Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? > > Oleg Bartunov wrote: > > > > Tom, > > > > patch was applied smoothly to 6.5.2 > > What's the syntax ? > > > > select a.msg_id, c.status_set_date, c.title > > from Message_Keyword_map a, messages c, keywords d > > where c.status_id =1 and d.name ~* 'moon' and a.key_id=d.key_id > > and c.msg_id=a.msg_id > > intersect > > select a.msg_id, a.status_set_date, a.title from messages a > > where a.status_id = 1 and a.title ~* 'moon' limit 5; > > > > produces (10 rows) > > > > select a.msg_id, c.status_set_date, c.title > > from Message_Keyword_map a, messages c, keywords d > > where c.status_id =1 and d.name ~* 'moon' and a.key_id=d.key_id > > and c.msg_id=a.msg_id limit 5 > > intersect > > select a.msg_id, a.status_set_date, a.title from messages a > > where a.status_id = 1 and a.title ~* 'moon' limit 5; > > > > As the limit is applied to the final result, I guess you can have only one > LIMIT per query. > > So try removing the limit 5 before intersect . > This was my first try (look above). It works but produces 10 rows instead of 5. Oleg > ----------- > Hannu > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> patch was applied smoothly to 6.5.2
> select a.msg_id, c.status_set_date, c.title
> from Message_Keyword_map a, messages c, keywords d
> where c.status_id =1 and d.name ~* 'moon' and a.key_id=d.key_id
> and c.msg_id=a.msg_id
> intersect
> select a.msg_id, a.status_set_date, a.title from messages a
> where a.status_id = 1 and a.title ~* 'moon' limit 5;
> produces (10 rows)
Hmm. It seemed to work as expected in current --- maybe there is
another bug still lurking in 6.5.*. I'll look when I get a chance.
regards, tom lane
I wrote:
> Hmm. It seemed to work as expected in current --- maybe there is
> another bug still lurking in 6.5.*. I'll look when I get a chance.
Yup, this change that was already in current is also needed:
*** src/backend/parser/gram.y.orig Mon Oct 18 23:59:35 1999
--- src/backend/parser/gram.y Mon Oct 18 23:55:18 1999
***************
*** 2768,2773 ****
--- 2768,2775 ---- /* finally attach the sort clause */ first_select->sortClause =
$2; first_select->forUpdate = $3;
+ first_select->limitOffset = nth(0, $4);
+ first_select->limitCount = nth(1, $4); $$ = (Node *)first_select;
} if (((SelectStmt *)$$)->forUpdate != NULL && QueryIsRule)
I have updated both current and REL6_5 branches.
regards, tom lane
Thanks Tom, I synced REL6_5 tree, compile, install but still query select a.msg_id, c.status_set_date, c.title from Message_Keyword_map a, messages c, keywords d where c.status_id=1 and d.name ~* 'sun' and a.key_id=d.key_id and c.msg_id=a.msg_id intersect select a.msg_id, a.status_set_date, a.title from messages a where a.status_id = 1 and a.title ~* 'sun' limit 10; produces more than 10 rows ! Regards, Oleg On Tue, 19 Oct 1999, Tom Lane wrote: > Date: Tue, 19 Oct 1999 00:42:08 -0400 > From: Tom Lane <tgl@sss.pgh.pa.us> > To: Oleg Bartunov <oleg@sai.msu.su>, pgsql-hackers@postgreSQL.org > Subject: Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? > > I wrote: > > Hmm. It seemed to work as expected in current --- maybe there is > > another bug still lurking in 6.5.*. I'll look when I get a chance. > > Yup, this change that was already in current is also needed: > > *** src/backend/parser/gram.y.orig Mon Oct 18 23:59:35 1999 > --- src/backend/parser/gram.y Mon Oct 18 23:55:18 1999 > *************** > *** 2768,2773 **** > --- 2768,2775 ---- > /* finally attach the sort clause */ > first_select->sortClause = $2; > first_select->forUpdate = $3; > + first_select->limitOffset = nth(0, $4); > + first_select->limitCount = nth(1, $4); > $$ = (Node *)first_select; > } > if (((SelectStmt *)$$)->forUpdate != NULL && QueryIsRule) > > > > I have updated both current and REL6_5 branches. > > regards, tom lane > > ************ > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> Thanks Tom,
> I synced REL6_5 tree, compile, install but still query
> select a.msg_id, c.status_set_date, c.title
> from Message_Keyword_map a, messages c, keywords d
> where c.status_id =1 and d.name ~* 'sun' and a.key_id=d.key_id
> and c.msg_id=a.msg_id
> intersect
> select a.msg_id, a.status_set_date, a.title
> from messages a where a.status_id = 1 and a.title ~* 'sun' limit 10;
> produces more than 10 rows !
If you'd care to provide a reproducible stand-alone test case I'll look
into it further. I do not feel like trying to reverse-engineer your
table declarations.
regards, tom lane