Date: Tue, 7 Feb 2006 14:17:51 -0500
From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: Re: trecherous subselect needs warning?
Message-Id: <20060207141751.38effa97.gry@ll.mit.edu>
In-Reply-To: <27137.1139334353@sss.pgh.pa.us>
References: <20060207120110.5d5f3c44.gry@ll.mit.edu>
	<27137.1139334353@sss.pgh.pa.us>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Tue, 07 Feb 2006 12:45:53 -0500
Tom Lane <tgl@sss.pgh.pa.us> threw this fish to the penguins:

> george young <gry@ll.mit.edu> writes:
> > This query returns zero rows:
> 
> > newschm3=# select run_id from s_bake where opset_id not in (select opset_id from opset_steps);
> >  run_id
> > --------
> > (0 rows)
> 
> > But, by my mistake, table opset_steps has no column "opset_id"!
> > Surely it should return an error, or at least a warning, not just an
> > empty rowset.
> 
> Access to upper-level variables from subqueries is (a) useful and (b)

My orginal posting suggested a warning for the *useless*
"from opset_steps" clause, since it's presence is misleading.  

But I don't suppose the SQL spec allows warnings that are not
explicitly in the spec, alas.

Of course upper-level variables must in general be accessible
from subqueries.

> required by the SQL spec, so we are not going to start throwing warnings
> about it.

I was just trying to find a way to prevent other innocent users
from wasting many hours of torment tracking down this subtle
twist of SQL...

How about a *documentation* suggestion that sub-queries can be
very dangerous if one doesn't qualify all column references?
Maybe with an example like the one that bit me?

-- George 
-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)