[hibernate-issues] [Hibernate-JIRA] Assigned: (ANN-625) @OrderBy usage on a joined classes (when using join table) produces incorred SQL syntax.

@OrderBy usage on a joined classes (when using join table) produces incorred SQL syntax. ---------------------------------------------------------------------------------------- Key: ANN-625 URL: http://opensource.atlassian.com/projects/hibernate/browse/ANN-625 Project: Hibernate Annotations Issue Type: Improvement Components: binder Affects Versions: 3.3.0.ga Reporter: Dima Gutzeit Assignee: Hardy Ferentschik Fix For: 3.4.0.CR2 Please consider the following mapping : @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.LAZY) @IndexColumn(name = "ListIndex") @JoinTable(name = "odp_rulemanipulatorjoin", joinColumns = @JoinColumn(name = "RoutingRuleId"), inverseJoinColumns = @JoinColumn(name = "ManipulatorComponentId")) @OrderBy(value = "priority asc") public List<RoutingManipulationComponent> getManipulators() { if (manipulators == null) { manipulators = new ArrayList<RoutingManipulationComponent>(); } return manipulators; } *********************************** @Entity(name = "RoutingManipulationComponent") @DiscriminatorValue("RoutingManipulationComponent") public abstract class RoutingManipulationComponent extends RoutingComponent implements Initializable { /** * Applies the manipulation to the given context. * * @param context the routing context to manipulate * @return true if any manipulation was applied, false if no manipulation * occurred */ public abstract boolean apply(RoutingContext context); } ****************************************** @Entity(name = "RoutingComponent") @Inheritance(strategy = InheritanceType.SINGLE_TABLE) @DiscriminatorColumn(name = "RoutingComponentType", discriminatorType = DiscriminatorType.STRING) @DiscriminatorValue("RoutingComponent") @Table(name = "odp_routingcomponent") public abstract class RoutingComponent extends DialPlanProvisionalEntity { @Deprecated public RoutingComponent() { // blank } /** * @param name * @param description */ public RoutingComponent(String name, String description) { super(name, description); } } ******************************* @MappedSuperclass @Cache(usage = CacheConcurrencyStrategy.READ_WRITE) public abstract class DialPlanProvisionalEntity extends ProvisionalEntity implements Initializable { private Long entityId; private boolean visible = true; private int priority; /** * Not for direct instantiation - this constructor also serves as public * constructor for hibernate, jax-ws etc. <br> */ @Deprecated public DialPlanProvisionalEntity() { this("(no name set)", "(no description set)"); } /** * @param name * @param description */ public DialPlanProvisionalEntity(String name, String description) { creationDate = GregorianCalendar.getInstance(); // now } @Id @GeneratedValue(strategy = GenerationType.AUTO) @Override public Long getId() { return entityId; } @Override public void setId(Long ruleCollectionId) { this.entityId = ruleCollectionId; } /** * @return Returns the priority. */ public int getPriority() { return priority; } /** * @param priority The priority to set. */ public void setPriority(int priority) { this.priority = priority; } @Override public boolean equals(Object o) { if (this == o) { return true; } if (o == null || Hibernate.getClass(this) != Hibernate.getClass(o)) { return false; } final ProvisionalEntity other = (ProvisionalEntity) o; return MiscUtils.equalOrBothNull(this.getName(), other.getName()); } @Override public int hashCode() { String name = this.getName(); return getClass().getName().hashCode() + 13 * name.hashCode(); } } The above mapping should use the "priority" field to the ordering. SQL that is produced is : select criteria0_.RoutingRuleId as RoutingR1_1_, criteria0_.CriteriaComponentId as Criteria2_1_, criteria0_.ListIndex as ListIndex1_, routingcri1_.id as id73_0_, routingcri1_.creationDate as creation3_73_0_, routingcri1_.description as descript4_73_0_, routingcri1_.lastModificationDate as lastModi5_73_0_, routingcri1_.name as name73_0_, routingcri1_.predefined as predefined73_0_, routingcri1_.status as status73_0_, routingcri1_.priority as priority73_0_, routingcri1_.visible as visible73_0_, routingcri1_.matcher_id as matcher25_73_0_, routingcri1_.criterion_id as criterion27_73_0_, routingcri1_.location_id aslocation26_73_0_, routingcri1_.RoutingComponentType as RoutingC1_73_0_ from odp_rulecriteriajoin criteria0_ left outer join odp_routingcomponent as routingcri1_ on criteria0_.CriteriaComponentId=routingcri1_.id where criteria0_.RoutingRuleId=1 order by odp_routingcomponent .priority asc It is wrong since not the table alias is used in the order by clause, but the real table name. Mysql fails with exception that odp_rulecriteriajoin.priority is unknown table. Changing the query to include "order by criteria0_.priority asc" returns the correct result.