0 Replies Latest reply on Mar 9, 2006 6:11 PM by Goran Ehrsson

    @OrderBy on column in InheritanceType.JOINED root class not

    Goran Ehrsson Newbie

      Hi, I'm running jboss-4.0.4RC1 and have a problem.
      I have a collection of inherited objects that works fine if I don't put in @OrderBy annotation. But I get an exception if I try to sort on a column that is located in a inheritence root table.

      I have isolated the problem with the following entities.
      The root entity.

      @Entity
      @Inheritance(strategy = InheritanceType.JOINED)
      @DiscriminatorColumn(name = "discriminator", length = 1)
      public abstract class Pet implements Serializable {
       @Id
       @GeneratedValue
       public Long id;
      
       @Column(length = 20)
       public String name;
      
       public Integer numLegs; // This is the column I wanna sort on!
      }
      

      A subclass that extends the root entity.
      @Entity
      @Inheritance
      @DiscriminatorValue(value = "C")
      public class Cat extends Pet {
       @ManyToOne(optional = false)
       public CatOwner owner;
      
       @Column(length = 10)
       public String furColor;
      }
      

      An entity that holds a collection with subclass instances.
      @Entity
      public class CatOwner implements Serializable {
       @Id
       @GeneratedValue
       public Long id;
      
       @Column(length = 40)
       public String name;
      
       @OneToMany(mappedBy = "owner", fetch = FetchType.LAZY)
       @OrderBy("numLegs asc")
       public List<Cat> cats;
      }
      


      The exception I get when accessing the collection is:
      org.hibernate.exception.SQLGrammarException: could not initialize a collection: [test.CatOwner.cats#1]
       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
       at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
       at org.hibernate.loader.Loader.loadCollection(Loader.java:1926)
      ...
      Caused by: java.sql.SQLException: Unknown column 'cats0_.numLegs' in 'order clause'
       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847)
      

      The SQL produced is:
      select cats0_.owner_id as owner3_1_, cats0_.id as id1_, cats0_.id as id15_0_, cats0_1_.name as name15_0_, cats0_1_.numLegs as numLegs15_0_, cats0_.owner_id as owner3_18_0_, cats0_.furColor as furColor18_0_ from Cat cats0_ inner join Pet cats0_1_ on cats0_.id=cats0_1_.id where cats0_.owner_id=? order by cats0_.numLegs asc
      

      If I remove the @OrderBy annotation, it works fine.
      If this a bug, or am I doing something wrong here?
      Should I file a JIRA task?

      I tried to change the collection mapping in CatOwner to List and added targetEntity = test.Cat.class to the @OneToMany annotation. But that did not help.