[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3500) Hibernate fails to resolve the correct orderby column in SQL when using inheritance hierarchy

[Joonas Koivunen (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3500?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=31297#action_31297 ] 

Joonas Koivunen commented on HHH-3500:
--------------------------------------

Having logging set for "org.hibernate" to DEBUG does not reveal anything useful on forming the SQL leading to this error. And for all clearness; the exception thrown on the SQL prepared by hib over here is: 

java.sql.SQLSyntaxErrorException: Column 'ABSTRACTBASECLASS.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of join ....

I've seen other SQL syntax errors while fiddling around with inheritance hierarchies but have not remembered to report them as in the end they were a result of very stupid designing of the relations; however is not a goal to have Hibernate produce always CORRECT sql and detect errors beforehand?

> Hibernate fails to resolve the correct orderby column in SQL when using inheritance hierarchy
> ---------------------------------------------------------------------------------------------
>
>                 Key: HHH-3500
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3500
>             Project: Hibernate Core
>          Issue Type: Bug
>    Affects Versions: 3.3.0.GA
>         Environment: Derby 10.4.2.0 (http://db.apache.org)
>            Reporter: Joonas Koivunen
>   Original Estimate: 2 days
>  Remaining Estimate: 2 days
>
> I have an inheritance hierarchy:
> @Entity("abstractBaseClass")
> AbstractBaseClass
>  - ConcreteClass1
>  - ConcreteClass2
>  - ConcreteClass3
> AbstractBaseClass has:
>  - @Id long id;
>  - String name;
> @Entity("concreteClass1") ConcreteClass1 has a @OneToMany(mappedBy="concreteClass1") List<ConcreteClass2>
> @Entity("concreteClass2") ConcreteClass2 has a @OneToMany(mappedBy="concreteClass2") List<ConcreteClass3> and a reference to @ManyToOne ConcreteClass1.
> @Entity("concreteClass3") ConcreteClass3 has a reference to @ManyToOne ConcreteClass2.
> After adding a @OrderBy("name") annotation to the getter "List<ConcreteClass3> ConcreteClass2.getConcreteClass3s()" and afterwards accessing the association through an ConcreteClass2 instance I get:
> SELECT
>   cc3_concrete.cc2_id,
>   cc3_concrete.id,
>   cc3_base.name,
>   cc3_concrete.cc2_id AS cc2_id_2
> FROM
>   cc3 cc3_concrete
> INNER JOIN
>   abstractBaseClass cc3_base ON cc3_concrete.id = cc3_base.id
> WHERE
>   cc3_concrete.cc2_id = ?
> ORDER BY
>   abstractBaseClass.name asc
> (I edited hibernate's naming of entities in SQL to be a bit more clearer).
> Ordering by "abstractBaseClass.name" causes the problem here; it's obvious that it should had been "cc3_base.name" but for some reason hib just uses the entityname.column_name in the SQL. 
> Another strange thing is that the foreign key column "cc2_id" is being included twice in the results? It is included in the select wheter or not @OrderBy exists.
> Inheritance strategy used is javax.persistence.InheritanceType.JOINED.

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