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

[Gulshanrai BABAJEE (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2136?page=comments#action_24760 ] 

Gulshanrai BABAJEE commented on HHH-2136:
-----------------------------------------

Does the error am encounting above have a relation with error No: HHH-1631 ?

> 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

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