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

[Dima Gutzeit (JIRA)]

@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: Bug
    Affects Versions: 3.3.0.ga
            Reporter: Dima Gutzeit


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.

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