Hibernate does change the result set structure (puts Objects[]) into the result set when changing DESC to ASC on a many to many sorted column

Question

I have the following two different HQL statements.

My Data Structure looks like this:

User

@Entity (name = "User")
public class User
{

    @Id
    @GeneratedValue
    @Column (name = "id")
    private int id;
    @Column (name = "user_name")
    private String username;
    @Column (name = "password")
    private String password;
    @Column (name = "enabled")
    private boolean enabled;
    @ManyToMany (targetEntity = Role.class, cascade =
    {
        CascadeType.ALL
    })
    @JoinTable (name = "user_role", joinColumns =
    {
        @JoinColumn (name = "user_id")
    }, inverseJoinColumns =
    {
        @JoinColumn (name = "role_id")
    })
    private Set<Role> roles;

    /* getters and setters)
}

To cut it short the only difference between the two queries is that one is ASC the other is DESC

@NamedQuery (name = "user.getUsersOrderByRoleAsc",
    query = "FROM User as u left outer join u.roles roles WHERE u.username like :username ORDER BY roles.name ASC"),
@NamedQuery (name = "user.getUsersOrderByRoleDesc",
    query = "FROM User as u left outer join u.roles roles WHERE u.username like :username ORDER BY roles.name DESC"),

The query for ASC returns: A list of Users -> As I would expect.

The query of DESC returns: An List of Object[], and in each object the [0] is the User, while the [1] is just another null object.

That does not make any sense to me. How can simply changing ASC to DESC change the structure of the result set ?

I am using Hibernate 4.3.6.Final.

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

The fastest way to determin, what went wrong is to set the show_sql flag to true in you hibernate configuration file. This will log every rendered query.

See Hibernate show real SQL

edited May 23 '17 at 10:26

Community

1
1

answered Sep 18 '14 at 06:15

Hannes

2,018
25
32

I already did that, the SQL output does look EXACTLY the same (except for the ordering part of course) no matter if I try the ASC or DESC query. – seba.wagner Sep 18 '14 at 21:50
Taking a look at the hibernate test classes (`ASTParserLoadingOrderByTest`) I see that order by is allways used with aliases. Line 558 smells like a work around... – Hannes Sep 19 '14 at 12:23

score 0 · Answer 2 · answered Sep 18 '14 at 06:16

Probably there is some Hibernate bug you bumped into, but because you are join fetching a one to many children collections, it's safer to use distinct as well:

@NamedQuery (name = "user.getUsersOrderByRoleAsc",
query = "select distinct u FROM User as u left outer join u.roles roles WHERE u.username like :username ORDER BY roles.name ASC"),
@NamedQuery (name = "user.getUsersOrderByRoleDesc",
query = "select distinct u FROM User as u left outer join u.roles roles WHERE u.username like :username ORDER BY roles.name DESC")

Hibernate does change the result set structure (puts Objects[]) into the result set when changing DESC to ASC on a many to many sorted column

2 Answers2