From: | george young <gry(at)ll(dot)mit(dot)edu> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: trecherous subselect needs warning? |
Date: | 2006-02-07 19:17:51 |
Message-ID: | 20060207141751.38effa97.gry@ll.mit.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tue, 07 Feb 2006 12:45:53 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> threw this fish to the penguins:
> george young <gry(at)ll(dot)mit(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-02-07 19:39:17 | Re: trecherous subselect needs warning? |
Previous Message | Tom Lane | 2006-02-07 17:45:53 | Re: trecherous subselect needs warning? |