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