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.