Thread: trecherous subselect needs warning?

[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)

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

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)

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