Pgadmin - Column name validation in embedded query.

Author

Column name validation in embedded query.

Lori Pate

2005-11-08, 4:13 pm

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.

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
AIM: loripate0508
http://www.opushealthcare.com/

Dave Page

2005-11-08, 4:13 pm

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

Jean-Pierre Pelletier

2005-11-08, 4:13 pm

Column name validation in embedded query.The columns of testorder are
visible within the subquery
so the dbms interpret (correcly) patientorder_key as coming
from table testorder and gives no error.

I suggest you prefix column names within the subquery to avoid
such mistake.

Jean-Pierre Pelletier
e-djuster

----- Original Message -----
From: Lori Pate
To: pgadmin-support@postgresql.org
Sent: Monday, November 07, 2005 9:55 AM
Subject: [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 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
AIM: loripate0508
http://www.opushealthcare.com/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links