Ver. 330 has some incorrect SQL statements

2011-05-20
2013-05-02
  • Aivar Annamaa
    Aivar Annamaa
    2011-05-20

    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

    _

     
  • 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
    Aivar Annamaa
    2011-05-20

    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
    Aivar Annamaa
    2011-05-20

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

     
  • Aivar Annamaa
    Aivar Annamaa
    2011-05-31

    > 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