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


Sun, 17 Oct 1999 20:40:32 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
is it possible to use LIMIT and INTERSECT ?
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, Pennsylvania 19026

Sun, 17 Oct 1999 13:25:00 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
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

Sun, 17 Oct 1999 19:31:09 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? 
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


Mon, 18 Oct 1999 10:20:28 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
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 .

-----------
Hannu

Mon, 18 Oct 1999 08:22:31 +0000
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] is it possible to use LIMIT and INTERSECT ?
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


Mon, 18 Oct 1999 12:28:32 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] is it possible to use LIMIT and INTERSECT ?
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

Mon, 18 Oct 1999 10:37:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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

Tue, 19 Oct 1999 00:42:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? 
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


Tue, 19 Oct 1999 09:28:55 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? 
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

Tue, 19 Oct 1999 01:33:09 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] is it possible to use LIMIT and INTERSECT ? 