[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2136) HQL generates wrong sql statements

[Gulshanrai BABAJEE (JIRA)]

HQL generates wrong sql statements
----------------------------------

         Key: HHH-2136
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2136
     Project: Hibernate3
        Type: Bug

  Components: query-hql  
    Versions: 3.1.3, 3.2.0.cr5    
 Environment: Hibernate version 3.1.3
postgresql version 8.1.4
    Reporter: Gulshanrai BABAJEE
    Priority: Critical


Hi,
I have created an HQL statement as shown below. It is working very fine:
QUERY 1 (working):
SELECT new Map (ea.entry.reference AS reference, ea.entry.amount AS amount, ea.entry.date AS date, 
ea.account.absoluteBalance AS balance, ea.account.contract.referenceSaga1 AS refSaga1, 
ea.account.contract.reference AS refSaga2, ea.account.contract.subscriber.personalDetail.firstName AS firstName, 
ea.account.contract.subscriber.personalDetail.lastName AS lastName) FROM EntryAccountH ea 

Then I modified the above hql statement by adding 2 more fields (paymentMethod & contractStatus) in my select as shown below:
QUERY 2 (not working):
SELECT new Map (ea.entry.reference AS reference, ea.entry.amount AS amount, ea.entry.date AS date, 
ea.account.absoluteBalance AS balance, ea.account.contract.referenceSaga1 AS refSaga1, 
ea.account.contract.reference AS refSaga2, ea.account.contract.subscriber.personalDetail.firstName AS firstName, 
ea.account.contract.subscriber.personalDetail.lastName AS lastName, 
ea.account.contract.payer.paymentMethod AS paymentMethod, ea.account.contract.status AS contractStatus)
FROM EntryAccountH ea 

The generated sql statement by hiberante (after cleaning) is as follows:
QUERY 3 (not working):
select e.reference, e.amount, e.date, a.absolute_balance, c.reference_saga1, c.reference, p.first_name, p.last_name, 
py.payment_method_id, c.status_id 
from entry_account ea, entry e, account a, contract c, subscriber s, personal_detail p, 
payer py inner join payment_method pm on py.payment_method_id=pm.id 
inner join contract_status cs on c.status_id=cs.id 
where c.payer_id=py.id and s.personal_detail_id=p.id and c.subscriber_id=s.id and a.contract_id=c.id 
and ea.account_id=a.id and ea.entry_id=e.id

When I run query 2 & 3 I got the following error:

ERROR:  invalid reference to FROM-clause entry for table "c"
HINT:  There is an entry for table "c", but it cannot be referenced from this part of the query.

After looking at query 3 carefully, I found that it is because the table 'contract' in the 'from' statement is misplaced. In fact the query should be like this: 
QUERY 4 (working):
select e.reference, e.amount, e.date, a.absolute_balance, c.reference_saga1, c.reference, p.first_name, p.last_name, 
py.payment_method_id, c.status_id 
from entry_account ea, entry e, account a, subscriber s, personal_detail p, 
payer py inner join payment_method pm on py.payment_method_id=pm.id 
contract c inner join contract_status cs on c.status_id=cs.id 
where c.payer_id=py.id and s.personal_detail_id=p.id and c.subscriber_id=s.id and a.contract_id=c.id 
and ea.account_id=a.id and ea.entry_id=e.id

I think that hibernate is wrongly generating the sql statement. 
I've done another test also. I've placed the field contractStatus of my select statement at the first position as shown below :
QUERY 5 (working):
SELECT new Map (ea.account.contract.status AS contractStatus, ea.entry.reference AS reference, ea.entry.amount AS amount, ea.entry.date AS date, 
ea.account.absoluteBalance AS balance, ea.account.contract.referenceSaga1 AS refSaga1, 
ea.account.contract.reference AS refSaga2, ea.account.contract.subscriber.personalDetail.firstName AS firstName, 
ea.account.contract.subscriber.personalDetail.lastName AS lastName, ea.account.contract.payer.paymentMethod AS paymentMethod)
FROM EntryAccountH ea 

The generated sql statement by hiberante (after cleaning) is as follows:
QUERY 6 (working):
select e.reference, e.amount, e.date, a.absolute_balance, c.reference_saga1, c.reference, p.first_name, p.last_name, 
py.payment_method_id, c.status_id 
from entry_account ea, entry e, account a, subscriber s, personal_detail p, 
contract c inner join contract_status cs on c.status_id=cs.id 
payer py inner join payment_method pm on py.payment_method_id=pm.id 
where c.payer_id=py.id and s.personal_detail_id=p.id and c.subscriber_id=s.id and a.contract_id=c.id 
and ea.account_id=a.id and ea.entry_id=e.id

Conclusion: query 5 and 6 run correctly.

Hope you've understood what's the problem and that it can be corrected as soon as possible
Thanks in advance
gulshan

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira