pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrecherous subselect needs warning?
4+ messages / 2 participants
[nested] [flat]
* trecherous subselect needs warning?
@ 2006-02-07 17:01 george young <gry@ll.mit.edu>
0 siblings, 1 reply; 4+ messages in thread
From: george young @ 2006-02-07 17:01 UTC (permalink / raw)
To: pgsql-sql
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
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. "s_bake" *does* have an "opset_id" column, so that's
what it uses.
The "from opset_steps" is useless. I can understand it might be
inappropriate to make such illegal, but wouldn't a warning be appropriate?
It seems like postgres should know immediately that there is a
useless "from" clause.
Even trickier would be:
select run_id from s_bake where opset_id in (select opset_id from opset_steps);
which would return all rows from s_bake IFF opset_steps has any rows!
Eeek!
I suppose the moral of the story is to ALWAYS, absolutely ALWAYS
qualify a correlation name (table alias). Of course, what I meant
in the original query was:
select s.run_id from s_bake s where s.opset_id not in (select os.opset_id from old_opset_steps os);
Sigh. Am I missing something here?
-- George Young
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trecherous subselect needs warning?
@ 2006-02-07 17:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: george young <gry@ll.mit.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-02-07 17:45 UTC (permalink / raw)
To: gry@ll.mit.edu; +Cc: pgsql-sql
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)
required by the SQL spec, so we are not going to start throwing warnings
about it.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trecherous subselect needs warning?
@ 2006-02-07 19:17 george young <gry@ll.mit.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: george young @ 2006-02-07 19:17 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trecherous subselect needs warning?
@ 2006-02-07 19:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: george young <gry@ll.mit.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-02-07 19:39 UTC (permalink / raw)
To: gry@ll.mit.edu; +Cc: pgsql-sql
george young <gry@ll.mit.edu> writes:
> 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?
I have no objection to a documentation example, but there isn't any
obvious place where it would fit in the manual. Perhaps some sort
of "SQL gotchas" webpage on techdocs would be appropriate.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-02-07 19:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-07 17:01 trecherous subselect needs warning? george young <gry@ll.mit.edu>
2006-02-07 17:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-07 19:17 ` george young <gry@ll.mit.edu>
2006-02-07 19:39 ` Tom Lane <tgl@sss.pgh.pa.us>
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