Postgres Pro
Downloads
Home   >   mailing lists

Thread: union and LIMIT problem

union and LIMIT problem

From
Oleg Bartunov
Date:
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

Re: [HACKERS] union and LIMIT problem

From
Bruce Momjian
Date:
> 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,
Pennsylvania19026

Re: [HACKERS] union and LIMIT problem

From
wieck@debis.com (Jan Wieck)
Date:
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) #

Re: [HACKERS] union and LIMIT problem

From
Bruce Momjian
Date:
> 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,
Pennsylvania19026

Re: [HACKERS] union and LIMIT problem

From
Tom Lane
Date:
>>>> * 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

Re: [HACKERS] union and LIMIT problem

From
Bruce Momjian
Date:
> >>>> * 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,
Pennsylvania19026

Re: [HACKERS] union and LIMIT problem

From
Bruce Momjian
Date:
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,
Pennsylvania19026

Re: [HACKERS] union and LIMIT problem

From
Tom Lane
Date:
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

Re: [HACKERS] union and LIMIT problem

From
Bruce Momjian
Date:
> 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,
Pennsylvania19026

Re: [HACKERS] union and LIMIT problem

From
Oleg Bartunov
Date:
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