From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: trecherous subselect needs warning?
Date: Tue, 7 Feb 2006 12:01:10 -0500
Message-ID: <20060207120110.5d5f3c44.gry@ll.mit.edu> (raw)
[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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gry@ll.mit.edu
Subject: Re: trecherous subselect needs warning?
In-Reply-To: <20060207120110.5d5f3c44.gry@ll.mit.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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