Does anybody know how to use UNION and LIMIT together ?
I want to get 10 rows from publications and 10 rows 
from keys.

select msg_id from publications limit 10 union
select key_id from keys         limit 10 
produces 
ERROR:  parser: parse error at or near "union

select msg_id from publications union 
select key_id from keys         limit 10
produces something I wasn't expected


	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


Wed, 6 Oct 1999 23:06:26 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
union and LIMIT problem
> Does anybody know how to use UNION and LIMIT together ?
> I want to get 10 rows from publications and 10 rows 
> from keys.
> 
> select msg_id from publications limit 10 union
> select key_id from keys         limit 10 
> produces 
> ERROR:  parser: parse error at or near "union
> 
> select msg_id from publications union 
> select key_id from keys         limit 10
> produces something I wasn't expected

I have on the TODO list:

	* UNION with LIMIT fails


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

Wed, 6 Oct 1999 15:23:37 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] union and LIMIT problem
Bruce Momjian wrote:

>
> > Does anybody know how to use UNION and LIMIT together ?
> > I want to get 10 rows from publications and 10 rows
> > from keys.
> >
> > select msg_id from publications limit 10 union
> > select key_id from keys         limit 10
> > produces
> > ERROR:  parser: parse error at or near "union
> >
> > select msg_id from publications union
> > select key_id from keys         limit 10
> > produces something I wasn't expected
>
> I have on the TODO list:
>
>    * UNION with LIMIT fails

    and must fail by it's implementation. LIMIT is handled by the
    outermost executor loop, suppressing OFFSET result tuples and
    stopping execution after LIMIT results sent to the client.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Thu, 7 Oct 1999 12:00:22 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] union and LIMIT problem
> Bruce Momjian wrote:
> 
> >
> > > Does anybody know how to use UNION and LIMIT together ?
> > > I want to get 10 rows from publications and 10 rows
> > > from keys.
> > >
> > > select msg_id from publications limit 10 union
> > > select key_id from keys         limit 10
> > > produces
> > > ERROR:  parser: parse error at or near "union
> > >
> > > select msg_id from publications union
> > > select key_id from keys         limit 10
> > > produces something I wasn't expected
> >
> > I have on the TODO list:
> >
> >    * UNION with LIMIT fails
> 
>     and must fail by it's implementation. LIMIT is handled by the
>     outermost executor loop, suppressing OFFSET result tuples and
>     stopping execution after LIMIT results sent to the client.

Ah, but it works sometimes:

  test=> select * from pg_language union select * from pg_language limit 1;
  lanname|lanispl|lanpltrusted|lanplcallfoid|lancompiler
  -------+-------+------------+-------------+-----------
  |t     |f      |f           |            0|/bin/cc    
  (1 row)

so we would need to get it working, or disable it from happening.

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

Thu, 7 Oct 1999 12:36:57 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] union and LIMIT problem
>>>> * UNION with LIMIT fails
>> 
>> and must fail by it's implementation. LIMIT is handled by the
>> outermost executor loop, suppressing OFFSET result tuples and
>> stopping execution after LIMIT results sent to the client.

> Ah, but it works sometimes:

Well, the real question is what do you mean by "works" or "fails".
In particular, do you think that LIMIT applies to the overall result
of the whole query, or to any one sub-select?

IIRC, ORDER BY is supposed to apply to the end result (and you can
only write it at the very end of the query, not after a sub-select),
and I'd vote for making LIMIT work the same.  In which case the
executor should be fine, and we probably just have a problem with
the parser hanging the info on the wrong node of the querytree...

			regards, tom lane

Thu, 07 Oct 1999 13:29:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] union and LIMIT problem 
> >>>> * UNION with LIMIT fails
> >> 
> >> and must fail by it's implementation. LIMIT is handled by the
> >> outermost executor loop, suppressing OFFSET result tuples and
> >> stopping execution after LIMIT results sent to the client.
> 
> > Ah, but it works sometimes:
> 
> Well, the real question is what do you mean by "works" or "fails".
> In particular, do you think that LIMIT applies to the overall result
> of the whole query, or to any one sub-select?

Should apply to overall result, like ORDER BY.

> 
> IIRC, ORDER BY is supposed to apply to the end result (and you can
> only write it at the very end of the query, not after a sub-select),
> and I'd vote for making LIMIT work the same.  In which case the
> executor should be fine, and we probably just have a problem with
> the parser hanging the info on the wrong node of the querytree...
> 
> 			regards, tom lane
> 


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

Thu, 7 Oct 1999 13:39:40 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] union and LIMIT problem

Can I assume this is fixed?  I see it marked on the TODO list.

> Does anybody know how to use UNION and LIMIT together ?
> I want to get 10 rows from publications and 10 rows 
> from keys.
> 
> select msg_id from publications limit 10 union
> select key_id from keys         limit 10 
> produces 
> ERROR:  parser: parse error at or near "union
> 
> select msg_id from publications union 
> select key_id from keys         limit 10
> produces something I wasn't expected
> 
> 
> 	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

Mon, 29 Nov 1999 19:29:05 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] union and LIMIT problem
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can I assume this is fixed?  I see it marked on the TODO list.

Yes, I think it is (barring a counterexample from someone ... the
UNION rewriter is awfully crufty ...).

It might be nice to allow LIMIT to be attached to subselects rather
than just the top level, but I have no idea what it would take in the
executor to implement that.  I could handle fixing the parser & planner
if someone else wants to fix it in the executor.

>> Does anybody know how to use UNION and LIMIT together ?
>> 
>> select msg_id from publications union 
>> select key_id from keys         limit 10
>> produces something I wasn't expected

			regards, tom lane

Mon, 29 Nov 1999 21:34:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] union and LIMIT problem 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Can I assume this is fixed?  I see it marked on the TODO list.
> 
> Yes, I think it is (barring a counterexample from someone ... the
> UNION rewriter is awfully crufty ...).
> 
> It might be nice to allow LIMIT to be attached to subselects rather
> than just the top level, but I have no idea what it would take in the
> executor to implement that.  I could handle fixing the parser & planner
> if someone else wants to fix it in the executor.

Let's wait for someone to ask for it.

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

Mon, 29 Nov 1999 21:41:44 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] union and LIMIT problem
On Mon, 29 Nov 1999, Bruce Momjian wrote:

> Date: Mon, 29 Nov 1999 19:29:05 -0500 (EST)
> From: Bruce Momjian <pgman@candle.pha.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] union and LIMIT problem
> 
> 
> Can I assume this is fixed?  I see it marked on the TODO list.
> 

Yes, it is fixed in 6.5.3 by Tom Lane. 

	Regards,

		Oleg

> > Does anybody know how to use UNION and LIMIT together ?
> > I want to get 10 rows from publications and 10 rows 
> > from keys.
> > 
> > select msg_id from publications limit 10 union
> > select key_id from keys         limit 10 
> > produces 
> > ERROR:  parser: parse error at or near "union
> > 
> > select msg_id from publications union 
> > select key_id from keys         limit 10
> > produces something I wasn't expected
> > 
> > 
> > 	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
> 

_____________________________________________________________
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, 30 Nov 1999 13:31:22 +0300 (MSK)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] union and LIMIT problem