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

[Nicolas Malin (Jira)]

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.

[Nicolas Malin (Jira)]

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

[Nicolas Malin (Jira)]

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

[Nicolas Malin (Jira)]

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

David E. Jones closed OFBIZ-1122.
---------------------------------

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