Ver. 330 has some incorrect SQL statements

[Aivar Annamaa]

Hi,

I was testing our embedded SQL analyzer ( http://alvor.googlecode.com ) against Compiere version 330 and found following issues. I didn't file them as bugs because I don't know if they actually surface in real usage.

regards,
Aivar
====================================
project "ad":

---

GridWindowVO.java: line 69
String sql = "SELECT AD_Window_ID, IsSOTrx, IsReadOnly FROM AD_Menu "
+ "WHERE AD_Menu_ID=? AND Action='W'";
   
ORA-00904: "ISSOTRX": invalid identifier

---

MTable.java: line 771

ORA-00904: "REFCOL"."ENTITYTYPE": invalid identifier
SQL:
SELECT refC.ColumnName, refC.AD_Table_ID, srcC.ColumnName FROM AD_Column refC INNER JOIN AD_Ref_Table rt ON (refC.AD_Reference_Value_ID=rt.AD_Reference_ID) INNER JOIN AD_Table refT ON (refC.AD_Table_ID=refT.AD_Table_ID)  INNER JOIN AD_Column srcC ON (srcC.AD_Column_ID=rt.Column_Key_ID) WHERE rt.AD_Table_ID=? AND refC.ColumnSql IS NULL AND refT.IsView='N' AND refCol.EntityType=? MTable.java /ad/src/org/compiere/model /ad/src/org/compiere/model/MTable.java SQL error marker

=====================================
base:

---

CalloutOrder:1298
SQL test failed  - ORA-00904: "OL"."M_INOUTLINE_ID": invalid identifier
SQL:
SELECT SUM(QtyOrdered) FROM C_OrderLine ol INNER JOIN C_Order o ON (ol.C_Order_ID=o.C_Order_ID) WHERE ol.M_InOutLine_ID=? AND o.IsReturnTrx='Y'  AND (o.DocStatus IN ('CO','CL') OR o.C_Order_ID=?)  AND ol.C_OrderLine_ID<>? CalloutOrder.java /base/src/org/compiere/model /base/src/org/compiere/model/CalloutOrder.java SQL error marker

---

MRequestType:77
SQL test failed  - ORA-00904: "R_REQUEST_ID": invalid identifier
SQL:
SELECT * FROM R_RequestType WHERE AD_Client_ID IN (0,?) AND IsActive='Y'ORDER BY ASCII(IsDefault) DESC, AD_Client_ID DESC, R_Request_ID DESC MRequestType.java /base/src/org/compiere/model /base/src/org/compiere/model/MRequestType.java SQL error marker

---

MCost:546
SQL test failed  - ORA-00904: "T"."M_ATTRIBUTESETINSTANCE_ID": invalid identifier
SQL:
SELECT currencyConvert(ol.PriceCost, o.C_Currency_ID, ?, o.DateAcct, o.C_ConversionType_ID, ol.AD_Client_ID, ol.AD_Org_ID), currencyConvert(ol.PriceActual, o.C_Currency_ID, ?, o.DateAcct, o.C_ConversionType_ID, ol.AD_Client_ID, ol.AD_Org_ID) FROM C_OrderLine ol INNER JOIN C_Order o ON (ol.C_Order_ID=o.C_Order_ID) WHERE ol.M_Product_ID=? AND o.IsSOTrx='N' AND t.M_AttributeSetInstance_ID=? ORDER BY o.DateOrdered DESC, ol.Line DESC MCost.java /base/src/org/compiere/model /base/src/org/compiere/model/MCost.java SQL error marker

---

MUOMConversion:397

SQL test failed  - ORA-00908: missing NULL keyword
SQL:
SELECT c.MultiplyRate, uomTo.StdPrecision, uomTo.CostingPrecision FROM C_UOM_Conversion c INNER JOIN C_UOM uomTo ON (c.C_UOM_To_ID=uomTo.C_UOM_ID) WHERE c.IsActive='Y' AND c.C_UOM_ID=? AND c.C_UOM_To_ID=?  AND c.M_Product_ID IS NULLORDER BY c.AD_Client_ID DESC, c.AD_Org_ID DESC MUOMConversion.java /base/src/org/compiere/model /base/src/org/compiere/model/MUOMConversion.java SQL error marker

---

ImportBankStatement:326
_
SQL test failed  - ORA-12704: character set mismatch
ORA-06512: at line 1
SQL:
UPDATE I_Bankstatement SET I_IsImported='E', I_ErrorMsg=COALESCE(I_ErrorMsg,'')||'Err=Duplicate' WHERE I_BankStatement_ID=? AND AD_Client_ID=0 ImportBankStatement.java /base/src/org/compiere/process /base/src/org/compiere/process/ImportBankStatement.java SQL error marker

====================================
client:

---

VTrxMaterial:404

SQL test failed  - ORA-00904: "M_PRODUCTION_ID": invalid identifier
SQL:
SELECT M_Production_ID FROM M_ProductionLine WHERE M_ProductionLine_ID=? VTrxMaterial.java /client/Src/org/compiere/apps/form /client/Src/org/compiere/apps/form/VTrxMaterial.java SQL error marker

_

[Trifon (An ADempiere founder)]

Hello Aivar,

---

GridWindowVO.java: line 69
String sql = "SELECT AD_Window_ID, IsSOTrx, IsReadOnly FROM AD_Menu "
+ "WHERE AD_Menu_ID=? AND Action='W'";
   
ORA-00904: "ISSOTRX": invalid identifier

Column IsSoTrx exists in AD_Menu table.
Does your tool consider capital cases of the column names?

Regards,
Trifon

[Aivar Annamaa]

Hi Trifon!

> Does your tool consider capital cases of the column names?

No, it analyzes SQL string expressions and tests all possible string values against the database (using mainly Connection.prepareStatement). Those cases given in my original post, database (Oracle) responded with exception.

[Aivar Annamaa]

… it's just that Oracle likes to put identifiers uppercase - the tool forwards Oracle's messages to user.

[Aivar Annamaa]

> Column IsSoTrx exists in AD_Menu table.
Well, at least it doesn't exist in this version of database which was created by Compiere 330 installer