[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5041) @ManyToOne not working with @Formula

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5041?page=c... ] Sharath Reddy commented on HHH-5041: ------------------------------------ You should be posting this in the forums first. Check if this works for you: @Entity @Table(name="product") public class Product { @Id public Integer id; @Column(name="product_idnf", length=18, nullable=false, unique=true, columnDefinition="char(18)") public String productIdnf; @Column(name="description", nullable=false) public String description; @ManyToOne @JoinColumnsOrFormulas( { @JoinColumnOrFormula(formula=@JoinFormula(value="SUBSTR(product_idnf, 1, 3)", referencedColumnName="product_idnf")) }) @Fetch(FetchMode.JOIN) private Product productFamily; public Product getProductFamily() { return productFamily; } } Hibernate generates the following SQL: select product0_.id as id9_1_, product0_.description as descript2_9_1_, product0_.product_idnf as product3_9_1_, SUBSTR(product0_.product_idnf, 1, 3) as formula13_1_, product1_.id as id9_0_, product1_.description as descript2_9_0_, product1_.product_idnf as product3_9_0_, SUBSTR(product1_.product_idnf, 1, 3) as formula13_0_ from product product0_ left outer join product product1_ on SUBSTR(product0_.product_idnf, 1, 3)=product1_.id where product0_.id=? The test case still fails for me - I am testing against HSQL database, and invoking the SUBSTR function within the join condition does not seem to work, but MAY work with your database...which one are you using? I get the error: 01:40:47,246 WARN JDBCExceptionReporter:100 - SQL Error: -64, SQLState: 37000 01:40:47,247 ERROR JDBCExceptionReporter:101 - not allowed in OUTER JOIN condition -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira