"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