Automated Business Logic / Tickets / #36 SQL exception using recompute

Automated Business Logic - 2012-10-24

Well, this sum is for Purchaseorder, but the where clause is for Customer. Could you try specifying it as:

entityCriteria.put("buslogicdemo.data.pojo.Purchaseorder", "p.customer.name = 'Alpha and Sons'");

because p is the alias we use in the HQL query. If this does not work for you, let me know and we'll figure something out.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Anonymous
  
  Add attachments
  Cancel
  You seem to have CSS turned off. Please don't fill out this field.
  
  You seem to have CSS turned off. Please don't fill out this field.

Hi,

We have changed the criteria but we have gotten the same exception. To get more information, we've activated the HQL log4Java logger, and we were able to see the HQL code generated by Recompute.

We have following HQL sentence with the old criteria:


org.hibernate.hql.ast.QueryTranslatorImpl - parse() - HQL: update buslogicdemo.data.pojo.Purchaseorder as p set amountTotal = coalesce((select sum(c.amount) from p.lineitems as c ), 0) WHERE p.amountTotal IS NULL AND coalesce((select sum(c.amount) from p.lineitems as c ), 0) <> 0 AND (customer.name = 'Alpha and Sons')

With your suggested criteria, we have this HQL sentence:


org.hibernate.hql.ast.QueryTranslatorImpl - parse() - HQL: update buslogicdemo.data.pojo.Purchaseorder as p set amountTotal = coalesce((select sum(c.amount) from p.lineitems as c ), 0) WHERE p.amountTotal IS NULL AND coalesce((select sum(c.amount) from p.lineitems as c ), 0) <> 0 AND (p.customer.name = 'Alpha and Sons')

But the SQL generated by Hibernate is the same in both cases:


org.hibernate.SQL - 
update
    Purchaseorder 
set
    amount_total=coalesce((select
        sum(lineitems1_.amount) 
    from
        lineitem lineitems1_ 
    where
        Purchaseorder.order_number=lineitems1_.order_number),
    0) 
where
    (
        amount_total is null
    ) 
    and coalesce((select
        sum(lineitems2_.amount) 
    from
        lineitem lineitems2_ 
    where
        Purchaseorder.order_number=lineitems2_.order_number), 0)<>0 
    and purchaseor0_.customer_name='Alpha and Sons'

Cheers,
Sira

Anonymous

Add attachments
Cancel
You seem to have CSS turned off. Please don't fill out this field.

You seem to have CSS turned off. Please don't fill out this field.

Hi,

I was reviewing this bug and I think I get the cause of the exception: It seems Hibernate doesn't generate the SQL sentence in right way from Recompute HQL sentence.

When we use Recompute with the following criteria:


entityCriteria.put("buslogicdemo.data.pojo.Purchaseorder", "customer.name = 'Alpha and Sons'")

Recompute generates this HQL sentence:


update buslogicdemo.data.pojo.Purchaseorder as p 
set amountTotal = coalesce((select sum(c.amount) from p.lineitems as c ), 0) 
WHERE p.amountTotal IS NULL 
    AND coalesce((select sum(c.amount) from p.lineitems as c ), 0) <> 0 
    AND (customer.name = 'Alpha and Sons')

Then Hibernate generates the following SQL sentence:


update
    Purchaseorder 
set
    amount_total=coalesce((select
        sum(lineitems1_.amount) 
    from
        lineitem lineitems1_ 
    where
        Purchaseorder.order_number=lineitems1_.order_number),
    0) 
where
    (
        amount_total is null
    ) 
    and coalesce((select
        sum(lineitems2_.amount) 
    from
        lineitem lineitems2_ 
    where
        Purchaseorder.order_number=lineitems2_.order_number), 0)<>0 
    and purchaseor0_.customer_name='Alpha and Sons'

When Hibernate executes that SQL sentence, it will get the following Exception:


org.hibernate.util.JDBCExceptionReporter - User lacks privilege or object not found: PURCHASEOR0_.CUSTOMER_NAME

...

I did a test executing the Recompute's HQL sentence in an independent code but I allways got the above exception. Then I changed the order of conditions, putting the Recompute criteria condition first and I get the following HQL sentence:


update buslogicdemo.data.pojo.Purchaseorder as p 
set amountTotal = coalesce((select sum(c.amount) from p.lineitems as c ), 0) 
WHERE (customer.name = 'Alpha and Sons')
    AND  p.amountTotal IS NULL
AND coalesce((select sum(c.amount) from p.lineitems as c ), 0) <> 0

I don't know why but Hibernate generates a different SQL sentence to this HQL and it doesn't throw any exception. This is the generated SQL without exceptions:


update
    Purchaseorder 
set
    amount_total=coalesce((select
        sum(lineitems1_.amount) 
    from
        lineitem lineitems1_ 
    where
        Purchaseorder.order_number=lineitems1_.order_number),
    0) 
where
    customer_name='Alpha and Sons' 
    and (
        amount_total is null
    ) 
    and coalesce((select
        sum(lineitems2_.amount) 
    from
        lineitem lineitems2_ 
    where
        Purchaseorder.order_number=lineitems2_.order_number), 0)<>0


This is the main method used to check the HQL sentence in our persistence unit. This method will throw an exception but if you change the order of HQL conditions it will run fine:

public static void main(String[] args){

    EntityManagerFactory entityManagerFactory = 
        Persistence.createEntityManagerFactory("OntimizeLogicTesting");
    EntityManager em = entityManagerFactory.createEntityManager();

    Session session = (Session) em.getDelegate();
Transaction t = session.beginTransaction();

    String finalHql =   "update buslogicdemo.data.pojo.Purchaseorder as p set " +
        " amountTotal = coalesce((select sum(c.amount) from p.lineitems as c ), 0) " +
        " WHERE p.amountTotal IS NULL " +
        " AND coalesce((select sum(c.amount) from p.lineitems as c ), 0) <> 0 " +
        " AND (customer.name = 'Alpha and Sons')";

    org.hibernate.Query q = session.createQuery(finalHql);
    int numBadRows = q.executeUpdate();
    t.commit();

}


I hope this will help you to check the bug.

Cheers, Sira

Anonymous

Add attachments
Cancel
You seem to have CSS turned off. Please don't fill out this field.

You seem to have CSS turned off. Please don't fill out this field.

SQL exception using recompute

Transaction logic engine for JPA/Hibernate

Milestone

Searches

Help

#36 SQL exception using recompute

Discussion