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

I am using pgAdmin III, PostgreSQL tools, Version 1.2.0 Beta on Windows
XP Pro.

The following query, when executed as a stand alone query returns an
error, correctly, that the column name is not valid:

Query A) Select patientorder_key from patientorder where visit_key =
250314 and provider_key = 301;

The column patientorder_key does not exist in the table patientorder,
the correct syntax should be:
Query B) Select patientorder.key from patientorder where visit_key =
250314 and provider_key = 301;

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;

This resulted in massive amounts of data being deleted erroneously.

I believe this is a bug.

Lori E. Pate

Quality Assurance Engineer
Opus Healthcare Solutions, Inc.
12301 Research Blvd.
Bldg. IV, Suite 200
Austin, Texas 78759

Phone: 512.336.4562
E-Mail: lpate@opushealthcare.com <mailto:lpate@opushealthcare.com>
AIM: loripate0508
http://www.opushealthcare.com/

________________________________

From: Dave Page [mailto:dpage@vale-housing.co.uk]
Sent: Monday, November 07, 2005 1:36 PM
To: Lori Pate; pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] Column name validation in embedded query.

On 7/11/05 2:55 pm, "Lori Pate" <lpate@opushealthcare.com> wrote:

I am using pgAdmin III, PostgreSQL tools, Version 1.2.0 Beta on
Windows XP Pro.

The following query, when executed as a stand alone query
returns an error, correctly, that the column name is not valid:

Query A) Select patientorder_key from patientorder where
visit_key = 250314 and provider_key = 301;

The correct syntax should be:
Query B) Select patientorder.key from patientorder where
visit_key = 250314 and provider_key = 301;

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;

This resulted in massive amounts of data being deleted
erroneously.

I believe this is a bug.

Not in pgAdmin. pgAdmin passes the query verbatim to PostgreSQL, making
no attempt to parse or understand it at all. PostgreSQL parses, plans
and executes the query and returns any resulting data or messages to
pgAdmin which displays it/them.

I would suggest producing an easily re-createable test case and posting
it to pgsql-bugs@postgresql.org.

Regards, Dave