We're updating the issue view to help you get more done. 

Can't ORDER BY on @Formula with DISTINCT on SQLServer

Description

Here is my Entities :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 @Entity public class Coupon implements Serializable { private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @OneToMany(fetch = FetchType.LAZY, mappedBy = "coupon", cascade={CascadeType.PERSIST, CascadeType.MERGE}) private List<Encasement> facEncaissements = new ArrayList<Encasement>(0); @Formula("(SELECT DISTINCT TOP 1 e.ChequeNumber FROM Encasement e WHERE e.IdCoupon = Id)") private String chequeNumber; /// Getters & Setters ... } @Entity public class Encasement implements Serializable { private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "IdCoupon") private Coupon coupon; private String chequeNumber; /// Getters & Setters ... }

Here is my JPA Query :

1 2 3 4 5 6 7 8 select distinct coupon from coupon Coupon left join coupon.encasements as encasement order by coupon.chequeNumber asc

And here is the generated SQL :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 select distinct TOP(?) coupon0_.Id as Id10_, (SELECT TOP 1 e.ChequeNumber FROM Encasement e WHERE e.IdCoupon = coupon0_.Id) as formula0_ from Coupon coupon0_ left outer join Encasement encasement1_ on coupon0_.Id=encasement1_.IdCoupon order by (SELECT TOP 1 e.ChequeNumber FROM Encasement e WHERE efe.IdCoupon = coupon0_.Id) asc

And here is the error I get from SQLServer :

1 2 16:15:45,026 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-localhost-127.0.0.1-8080-5) SQL Error: 145, SQLState: S0001 16:15:45,028 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-localhost-127.0.0.1-8080-5) ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I think Hibernate should use the formula alias instead of repeating the formula. I've tested directly in SQLServer and that query is working :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 select distinct TOP(?) coupon0_.Id as Id10_, (SELECT TOP 1 e.ChequeNumber FROM Encasement e WHERE e.IdCoupon = coupon0_.Id) as formula0_ from Coupon coupon0_ left outer join Encasement encasement1_ on coupon0_.Id=encasement1_.IdCoupon order by formula0_ asc

Moreover, that error appears only if I use DISTINCT on the first SELECT (and I must have that DISTINCT in my business use case).

Environment

JBoss 7.1.1.Final (hibernate module updated to 4.1.8.Final) using org.hibernate.dialect.SQLServer2008Dialect on SQLServer 2008R2

Status

Assignee

Unassigned

Reporter

Anthony Ogier

Components

Affects versions

4.1.8
5.0.9

Priority

Major