Helo All,
I am facing a problem with Hibernate and PostgreSql. The query generated by Hibernate is giving error while running on postgre sql.
The application is using PostgreSql 9.0 and hibernate 3.0. All classes are mapped through JPA .
We have a class called Message which have some associations with another classes.
While loading the association of message class, an exception is thrown. I debugged the generate SQL an found this query being run

select * from (line 1)
Table1 alias1 left outer join Table2 alias2 on alias1.colNam=alias2.colName (line 2)
and alias3.account_id = 1005 (line 3)
left outer join Table3 alias3 on alias3.colName=alias2.colName (line 4)

In the log, the error is logged as : from clause missing on Table3

I manually run this query on db and I got the same error. The reason for error is alias 3 is used (line 3) before it is being declared (line 4). If I modify this sql and move line 4 above line 3 then query is running fine.
I have set the dialect of PostgreSql in hibernate config file but it has no affect.
The Message class has a filter attribute on account class that's why line line 3 is added.
Please provide your valuable suggestions on this.
Can we by anyway change the order of generated sql so that line 4 comes before line 3?

Thanks

have anyone experienced this type of problem before? any idea how can we fixed it.
I am thinking of running this hibernate generated query in mysql db to check whether this issue is local to PostgreSql or not.
One more question, @filter tag should add conditions at where clause but in my case it is being added at the third line and not after all join statements.

Any advice and pointers are welcome.