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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Lori Pate" <lpate(at)opushealthcare(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: FW: [pgadmin-support] Column name validation in embedded query.
Date: 2005-11-08 14:42:44
Message-ID: 5264.1131460964@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Lori Pate" <lpate(at)opushealthcare(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2005-11-08 14:46:12 Re: a problem, maybe not a bug
Previous Message Alvaro Herrera 2005-11-08 13:14:24 Re: BUG #2027: Select on view hangs.