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

[Hardy Ferentschik (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/ANN-625?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Hardy Ferentschik updated ANN-625:
----------------------------------

    Fix Version/s:     (was: 3.4.0.CR2)

Moved out of release 3.4.0.CR2. Nothing can be done until the problem is fixed in core. Steve said that this might happen in version 4. Once the problem in core is fixed CollectionBinder.buildOrderByClauseFromHql(String hqlOrderBy, PersistentClass associatedClass, String role) has to be revisited.

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