[jira] Commented: (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:comment-tabpanel#action_12510027 ] 

Cameron Smith commented on OFBIZ-1122:
--------------------------------------

I have discovered where the cause of this problem is.

EntityConditionBase.getColName(Map tableAliases...  has for a while had a chunk of code at the start (lines 78-85) which was never actually exercised because EntityComparisonOperator.addSqlValue always passed a null DatasourceInfo down to it.

So the column name was always calculated via the second chunk of code which prefixed it with the table name/alias, thus avoiding ambiguity.

Then in r528801, jonesde committed a "fairly dramatic change" to allow view-entities to be used in subselects.  This caused the block in EntityConditionBase.getColName( to be exercised for the first time, and at least for MySQL 5.0 the generated SQL is not correct.   This appears to be the same problem which jonesde refers to having with Derby, which is which we now have the alias-view-columns=false in the datasource element in entityengine.xml.

I simply commented out the "never used" clause in our local OFBiz for the moment, and everything worked.  I preferred this to altering alias-view-columns, because we have been using it for ages with alias-view-columns=true and everything has worked fine.

> 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
>
> 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.