Обсуждение: Column name validation in embedded query.
<p><font face="Arial" size="2">I am using pgAdmin III, PostgreSQL tools, Version 1.2.0 Beta on Windows XP Pro.</font><p><fontface="Arial" size="2">The following query, when executed as a stand alone query returns an error, correctly,that the column name is not valid:</font><p><font face="Arial" size="2">Query A) Select patientorder_key from patientorderwhere visit_key = 250314 and provider_key = 301;</font><p><font face="Arial" size="2">The correct syntax shouldbe:</font><br /><font face="Arial" size="2">Query B) Select patientorder.key from patientorder where visit_key = 250314and provider_key = 301;</font><p><font face="Arial" size="2">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 apparentlyignores the where clause all together, resulting in complete data deletion, as if there were no where clause:</font><p><fontface="Arial" size="2">Begin;</font><br /><font face="Arial" size="2">Delete from testorder where patientorder_keyin </font><br /><font face="Arial" size="2">(Select patientorder_key from patientorder where visit_key =250314 and provider_key = 301);</font><br /><font face="Arial" size="2">End;</font><p><font face="Arial" size="2">This resultedin massive amounts of data being deleted erroneously.</font><p><font face="Arial" size="2">I believe this is a bug.</font><p><b><fontface="Arial">Lori E. Pate</font></b><br /><font face="Arial"> </font><br /><font face="Arial">QualityAssurance Engineer</font><br /><font face="Arial">Opus Healthcare Solutions, Inc.</font><br /><font face="Arial">12301Research Blvd.</font><br /><font face="Arial">Bldg. IV, Suite 200</font><br /><font face="Arial">Austin,Texas 78759</font><br /><font face="Arial"> </font><br /><font face="Arial">Phone: 512.336.4562</font><br/><font face="Arial">E-Mail: </font><a href="mailto:lpate@opushealthcare.com"><u><font color="#0000FF"face="Arial">lpate@opushealthcare.com</font></u></a><br /><font face="Arial">AIM: loripate0508</font><br /><ahref="http://www.opushealthcare.com/"><u><font color="#0000FF" face="Times New Roman">http://www.opushealthcare.com/</font></u></a><br/><font face="Arial"> </font>
<font face="Verdana, Helvetica, Arial"><span style="font-size:12.0px"><br /><br /><br /> On 7/11/05 2:55 pm, "Lori Pate"<lpate@opushealthcare.com> wrote:<br /><br /></span></font><blockquote><span style="font-size:12.0px"><font face="Arial">Iam using pgAdmin III, PostgreSQL tools, Version 1.2.0 Beta on Windows XP Pro.</font><font face="Verdana, Helvetica,Arial"><br /><br /></font><font face="Arial">The following query, when executed as a stand alone query returnsan error, correctly, that the column name is not valid:<br /></font><font face="Verdana, Helvetica, Arial"><br /></font><fontface="Arial">Query A) Select patientorder_key from patientorder where visit_key = 250314 and provider_key =301;</font><font face="Verdana, Helvetica, Arial"><br /><br /></font><font face="Arial">The correct syntax should be:</font><fontface="Verdana, Helvetica, Arial"><br /></font><font face="Arial">Query B) Select patientorder.key from patientorderwhere visit_key = 250314 and provider_key = 301;</font><font face="Verdana, Helvetica, Arial"><br /><br /></font><fontface="Arial">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:<br /></font><font face="Verdana, Helvetica,Arial"><br /></font><font face="Arial">Begin;</font><font face="Verdana, Helvetica, Arial"><br /></font><font face="Arial">Deletefrom testorder where patientorder_key in <br /> (Select patientorder_key from patientorder where visit_key= 250314 and provider_key = 301);</font><font face="Verdana, Helvetica, Arial"><br /></font><font face="Arial">End;</font><fontface="Verdana, Helvetica, Arial"><br /><br /></font><font face="Arial">This resulted in massiveamounts of data being deleted erroneously.</font><font face="Verdana, Helvetica, Arial"><br /><br /></font><font face="Arial">Ibelieve this is a bug.</font><font face="Verdana, Helvetica, Arial"><br /></font></span></blockquote><spanstyle="font-size:12.0px"><font face="Verdana, Helvetica, Arial"><br /> 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. <br /><br/> I would suggest producing an easily re-createable test case and posting it to pgsql-bugs@postgresql.org.<br /><br/> Regards, Dave <br /><br /></font></span>
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/