Обсуждение: 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
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
> 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
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) #
> 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
>>>> * 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
> >>>> * 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
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
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
> 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
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