Issue Type: Bug Bug
Affects Versions: 4.1.8
Assignee: Unassigned
Components: core
Created: 20/Dec/12 9:56 AM
Description:

Here is my Entities :

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

select
    distinct coupon 
from
    coupon Coupon   
left join
    coupon.encasements as encasement 
order by
    coupon.chequeNumber asc

And here is the generated SQL :

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 :

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 :

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
Project: Hibernate ORM
Labels: formula sqlserver distinct orderby
Priority: Major Major
Reporter: Anthony Ogier
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira