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.
