Date: Tue, 7 Feb 2006 12:01:10 -0500
From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: trecherous subselect needs warning?
Message-Id: <20060207120110.5d5f3c44.gry@ll.mit.edu>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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