>>> On Tue, Dec 19, 2006 at 9:58 AM, in message
<26199(dot)1166543928(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> I'm having trouble seeing how it is a useful construct in the
context
>> of a scalar subquery. A non- standard extension should be useful in
some
>> way.
>
> There is 0 chance that we'd disallow it at the top level after
allowing
> it all these years.
I wouldn't want to eliminate it there -- it is clearly a useful
extension to the standard at the top level.
> And probably not even just top- level; consider
> select 1 union all select 2 union all select 3;
> which has been the recommended workaround up to 8.2 for our lack of
> multi- row VALUES lists. We will certainly break a lot of code if
we
> disallow that.
Point taken.
> So now you have to make a case why we should make a
> non- orthogonal distinction between certain subqueries and other
> subqueries.
Well, I don't think of the terms for set operations as subqueries, and
there are other differences already in what is allowed for a query term
and a subquery. Arguably there is more risk of error of the type
recently reported where you are in a scalar subquery context.
> As for potential usefulness, consider a set- returning function
invoked
> in the targetlist: it makes perfect sense to do
> WHERE foo IN (SELECT mysrf(...))
> and maybe even add an ORDER BY/LIMIT to that.
That is sufficient to answer my concerns. I tend to operate from the
context of the standard, because we have our own ANSI based parser which
generates portable Java query classes. ORDER BY and LIMIT are not
allowed in the subqueries in the standard but are obviously useful
extensions. The missing FROM then adds value to the other extensions.
Case closed. Thanks.
By the way, when I read my previous message it struck me that it could
be taken with a tone I didn't intend. That was the result of whipping
it out quickly without taking sufficient time to review it. Sorry; no
offense was intended. I'll try to avoid doing that again.
-Kevin