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

[Arthur Hupka (JIRA)]

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

Arthur Hupka commented on ANN-625:
----------------------------------

I am facing the same issue on Oracle with Hibernate 3.2.4sp1 and HibernateAnnotations 3.3.0ga

the produced sql is the following:
---------------------------------- 
* load one-to-many de.innovations.teamcare.model.PlanPackage.activities */ select
        activities0_.planningPackage_fk as planning9_1_,
        activities0_.id as id1_,
        activities0_.id as id0_0_,
        activities0_.controllingID as controll3_0_0_,
        activities0_.empdaydate as empdaydate0_0_,
        activities0_.days as days0_0_,
        activities0_.employee_fk as employee8_0_0_,
        activities0_.actComment as actComment0_0_,
        activities0_.planningPackage_fk as planning9_0_0_ 
    from
        employeeday activities0_ 
    where
        activities0_.planningPackage_fk=? 
    order by
        employeeday.empdaydate desc
----------------------------------------------
and should be:
---------------------------------------------
....
order by activities0_empdaydate desc
----------------------------------------------

Its a bad one, because @javax.persistance.OrderBy is unusable at the moment.

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