[jira] Resolved: (OFBIZ-1122) Column aliasing for View Entities generates incorrect SQL

["Cameron Smith (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OFBIZ-1122?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Cameron Smith resolved OFBIZ-1122.
----------------------------------

       Resolution: Duplicate
    Fix Version/s: SVN trunk

My bad. On further investigation I realized that this problem was reported and fixed by OFBIZ-895, I merged sloppily and didn't pick up that alias-view-columns=false is now the default for MySQL

> Column aliasing for View Entities generates incorrect SQL
> ---------------------------------------------------------
>
>                 Key: OFBIZ-1122
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-1122
>             Project: OFBiz
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: Release Branch 4.0
>         Environment: MySQL 5.0, any OS (may affect other RDBMS products, I suspect it does)
>            Reporter: Cameron Smith
>             Fix For: SVN trunk
>
>
> I only noticed this issue when I synced our local,
> customized OFBiz (based on r502669) with the 4.0
> branch (r545673).  But I believe that the issue always
> existed, and it is just that the alterations to some
> of the accounting View Entities in r545673, brought it
> to light.
> When the EE SQL Processor does a query which View
> Entities, it aliases each component table of the
> Entity to a short name within the query, and prefixes
> all column references in the SELECT clause with that
> short name.  This is correct.  However it does NOT
> prefix columns in the WHERE clause with that short
> name.  Or at least, I cannot see how to make it do
> this.   Because of this, MySQL at least rejects the
> query because some of the column names in the WHERE
> clause are ambiguous.
> Here is a real example of a query which worked
> perfectly in r502669, but breaks in r545673, because
> of the extra columns added into the
> AcctgTransAndEntries View Entity.  Once again, I
> stress that r545673 is not the culprit - the SQL
> Processor appears to have always had this limitation,
> it just does not show itself regularly!
> Here is the code to build up the query:
> public List<GenericValue>
> findPostedTransactionsLike(String orgId, String txId)
> throws GenericEntityException
>    {
>       List criteria = exprEqualsAll("isPosted", "Y");
>       criteria.add(new EntityExpr("acctgTransId",
> EntityOperator.LIKE, txId + "%"));
>       List orderBy = UtilMisc.toList("acctgTransId",
> "acctgTransEntrySeqId");
>       return
> _delegator.findByAnd("AcctgTransAndEntries", criteria,
> orderBy);
>    }
> And here is the generated SQL and MySQL's error
> message.  The generated SQL would work perfectly if
> the columns in the WHERE clause were prefixed with the
> respective alias names:
> SELECT ATR.IS_POSTED AS IS_POSTED,
> ATR.GL_FISCAL_TYPE_ID AS GL_FISCAL_TYPE_ID,
> ATR.ACCTG_TRANS_TYPE_ID AS ACCTG_TRANS_TYPE_ID,
> ATR.TRANSACTION_DATE AS TRANSACTION_DATE,
> ATR.DESCRIPTION AS TRANS_DESCRIPTION, ATR.INVOICE_ID
> AS INVOICE_ID, ATR.PAYMENT_ID AS PAYMENT_ID,
> ATR.SHIPMENT_ID AS SHIPMENT_ID, ATR.RECEIPT_ID AS
> RECEIPT_ID, ATR.INVENTORY_ITEM_ID AS
> INVENTORY_ITEM_ID, ATR.WORK_EFFORT_ID AS
> WORK_EFFORT_ID, ATR.PHYSICAL_INVENTORY_ID AS
> PHYSICAL_INVENTORY_ID, ATR.VOUCHER_REF AS VOUCHER_REF,
> ATR.GL_JOURNAL_ID AS GL_JOURNAL_ID, ATE.ACCTG_TRANS_ID
> AS ACCTG_TRANS_ID, ATE.ACCTG_TRANS_ENTRY_SEQ_ID AS
> ACCTG_TRANS_ENTRY_SEQ_ID, ATE.GL_ACCOUNT_ID AS
> GL_ACCOUNT_ID, ATE.PRODUCT_ID AS PRODUCT_ID,
> ATE.DEBIT_CREDIT_FLAG AS DEBIT_CREDIT_FLAG, ATE.AMOUNT
> AS AMOUNT, ATE.CURRENCY_UOM_ID AS CURRENCY_UOM_ID,
> ATE.ORGANIZATION_PARTY_ID AS ORGANIZATION_PARTY_ID,
> GLA.GL_ACCOUNT_TYPE_ID AS GL_ACCOUNT_TYPE_ID,
> GLAC.GL_ACCOUNT_CLASS_ID AS GL_ACCOUNT_CLASS_ID,
> ATE.PARTY_ID AS PARTY_ID, ATE.RECONCILE_STATUS_ID AS
> RECONCILE_STATUS_ID, ATE.ACCTG_TRANS_ENTRY_TYPE_ID AS
> ACCTG_TRANS_ENTRY_TYPE_ID, ATE.DESCRIPTION AS
> DESCRIPTION FROM ACCTG_TRANS ATR INNER JOIN
> ACCTG_TRANS_ENTRY ATE ON ATR.ACCTG_TRANS_ID =
> ATE.ACCTG_TRANS_ID INNER JOIN GL_ACCOUNT GLA ON
> ATE.GL_ACCOUNT_ID = GLA.GL_ACCOUNT_ID INNER JOIN
> GL_ACCOUNT_CLASS GLAC ON GLA.GL_ACCOUNT_CLASS_ID =
> GLAC.GL_ACCOUNT_CLASS_ID WHERE (IS_POSTED = ? AND
> ACCTG_TRANS_ID LIKE ?) ORDER BY ACCTG_TRANS_ID ASC,
> ACCTG_TRANS_ENTRY_SEQ_ID ASC (Column 'ACCTG_TRANS_ID'
> in where clause is ambiguous)

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.