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

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: Minor

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