[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-7116) Ordered Criteria query that joins with an ordered mapped collection results in incorrect overall ordering

[Tommy Knowlton (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-7116?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45676#comment-45676 ] 

Tommy Knowlton commented on HHH-7116:
-------------------------------------

In Hibernate Core 3.6.9.Final, the "first" issue could be corrected by editing org.hibernate.loader.JoinWalker#orderBy on line 820 (I'll prepare a patch Real Soon)

> Ordered Criteria query that joins with an ordered mapped collection results in incorrect overall ordering
> ---------------------------------------------------------------------------------------------------------
>
>                 Key: HHH-7116
>                 URL: https://hibernate.onjira.com/browse/HHH-7116
>             Project: Hibernate ORM
>          Issue Type: Bug
>          Components: core, query-criteria
>    Affects Versions: 3.6.10, 4.1.0
>            Reporter: Tommy Knowlton
>              Labels: criteria, query
>
> A criteria query for Items that joins (with sub-criteria or alias) Bids, where mapped Bids collection is ordered [e.g., @OrderBy("bidamount desc")] and where the criteria query is ordered by Item descriptions [c.addOrder(Order.asc("description"))] will result in generated SQL having incorrect overall ordering: the ordering specified by the mapped association is output first, followed by the ordering specified on the criteria. Using the example, it ends up looking like "order by bid3_.AMOUNT desc, this_.DESCRIPTION asc".
> There are actually two issues here. The first is that the association's ordering is overriding the criteria's ordering. Instead of having Items ordered alphabetically by description, and each Item's bids ordered by decreasing bid amounts, we have Items ordered from those having highest bids, down to those having lower bids, with alike bids ordered by description, alphabetically. It ends up being a simple matter to fix this issue in the "JoinWalker" base class.
> The second issue is that in cases where a mapped association has an ordering specified, there is really an implied ordering by primary (or natural) key of the owning entity that has higher precedence than the association ordering. In the Items/Bids example, think about what happens to the criteria query if two items happen to have the same description text, and each has many bids: the result rows will be interleaved. It probably ends up being an implementation detail of the result transformer whether that matters or not, but I think that a "more correct" or at least less-surprising behavior would be for the "JoinWalker" to output orderby subclauses in such a way as to make the implied ordering (that keeps owning entities together in the resultset) explicit.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira