Re: FW: [pgadmin-support] Column name validation in embedded query.

"Lori Pate" <lpate@opushealthcare.com> writes:
> However, when the query with the incorrect column name (Query A) is
> embedded in a where clause, column validation does not happen, no error
> is displayed, and PGAdmin apparently ignores the where clause all
> together, resulting in complete data deletion, as if there were no where
> clause:

> Begin;
> Delete from testorder where patientorder_key in
> (Select patientorder_key from patientorder where visit_key = 250314 and
> provider_key = 301);
> End;

Unfortunately for you, that IS a valid SQL command.

In a subselect like that, unqualified column names are supposed to be
sought first in the subselect (ie, table patientorder) and then in the
outer query (table testorder).  So patientorder_key will be found as an
outer reference to testorder.  The fact that this renders the query a
bit silly does not change the fact that it's valid per spec.

            regards, tom lane