Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group