Re: Bug or Feature? Subquery issue.

On Tue, 21 Oct 2003, Josh Berkus wrote:

> Folks,
>
> Came across this counter-intuitive behavior on IRC today:

> test1=> --this generates an error
> test1=> select iddomain from vhost where IDvhost = 100;
> ERROR:  column "iddomain" does not exist
> test1=> -- This should generate an error, because IDdomain isn't a column of
> vhost
> test1=> --instead it deletes a row.
> test1=> delete from forwarding where iddomain in (select iddomain from vhost
> where idvhost = 100);
> DELETE 1
> test1=>
>
> According to Neil, what's happening is that "select iddomain" in the subquery
> is grabbing the iddomain column from the forwarding table in the outer query.
> This is not intutive, for certain; however, what I don't know is if it's SQL
> Spec.
>
> So, my question:  does the SQL spec allow for citing the outer query in the
> SELECT target list of a subquery?

AFAICT yes. I don't see anything that would limit a column reference that
was an outer reference from being in the target list in general (there are
specific limitations for some subcases) at least in sql92.