Skip site navigation (1) Skip section navigation (2)

Re: No error when FROM is missing in subquery

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: "Thomas H(dot)" <me(at)alternize(dot)com>, "Jaime Casanova" <systemguards(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org, "mike" <mike(at)thegodshalls(dot)com>
Subject: Re: No error when FROM is missing in subquery
Date: 2006-12-19 15:58:48
Message-ID: 26199.1166543928@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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.  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.  So now you have to make a case why we should make a
non-orthogonal distinction between certain subqueries and other
subqueries.

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.  Yeah, no doubt this is
easy to change to putting the SRF in FROM, but you can bet there are
Postgres applications out there today using it; we have never officially
deprecated this way of using an SRF.

I also recall having advised people in years past to use dummy
(SELECT expr) sub-selects to work around planning issues.  I don't
recall at the moment whether any of those issues are still live, but
again you can bet the construct is still in some applications' SQL.

So basically we get to introduce some arbitrary non-orthogonality, plus
break an unknown number of applications, in order to block off a type
of user error that AFAIR has never been complained of before in all the
years I've been working with Postgres.  Sorry, no sale ...

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Gurjeet SinghDate: 2006-12-19 15:59:16
Subject: Re: BUG #2840: \set HISTCONTROL ignoredups doesn't work in psql
Previous:From: Kevin GrittnerDate: 2006-12-19 15:37:43
Subject: Re: No error when FROM is missing in subquery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group