From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: Re: trecherous subselect needs warning?
Date: Tue, 7 Feb 2006 14:17:51 -0500
Message-ID: <20060207141751.38effa97.gry@ll.mit.edu> (raw)
In-Reply-To: <27137.1139334353@sss.pgh.pa.us>
References: <20060207120110.5d5f3c44.gry@ll.mit.edu>
<27137.1139334353@sss.pgh.pa.us>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gry@ll.mit.edu
Subject: Re: trecherous subselect needs warning?
In-Reply-To: <20060207141751.38effa97.gry@ll.mit.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox