JPQL SELECT Distinct Query with Lob Column works fine on MySQL, but SQLServer 2005 doesn't

Question

I'm Using Hibernate 3.6.10 based JPA2.0 , I created two objects which are Many-to-Many relation.

@Entity
@Table(name="Project")
public class Project implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String topic;
    @Lob
    @Basic(fetch=FetchType.LAZY)
    private String content;
    @ManyToMany(fetch=FetchType.EAGER)
    @JoinTable(name="TrProj_Area",joinColumns=@JoinColumn(name="TrProj_ID"), inverseJoinColumns=@JoinColumn(name="Area_ID"))
    private Set<Area> areas = new HashSet<Area>();
    //getters and setters
}

@Entity
@Table(name="Area")
public class Area implements Serializable{
     @Id
     @GeneratedValue(strategy = GenerationType.AUTO)
     private Integer id;
     private String name;
     @ManyToMany(mappedBy="areas")
     private Set<Project> projects;
     //getters and setters
}

The Project entity has a content property,It is Lob and Lazy Fetch, When I created a Query like this:

select distinct o from Project o join o.areas a where a.id in(?,?)

It works fine on MySQL 5.5,but SQLServer 2005 doesn't.SQLServer error log is below:

Error code 306, SQL state S0001: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

I known this issue is caused by the content property,it seems that the lazy fetch config doesn't work , the text column can not include in the select distinct statement, but I can't remove it from my Entity, how can I solve this issue?

Added at 20120722

I have trace the MySQL(mysql-connector-java-5.1.10-bin.jar as driver) generated SQL in hibernate, is below:

Hibernate: 
select
    distinct proj0_.id as id1_,
    proj0_.content as content1_,
    proj0_.topic as topic1_,
  from
    Project proj0_ 
inner join
    Proj_Area areas1_ 
        on proj0_.id=areas1_.Proj_ID 
inner join
    Area area2_ 
        on areas1_.Area_ID=area2_.id 
where
    area2_.id in (
            ? , ?
        )
order by
    proj0_.id desc limit ?

and this is generated by SQLServer2005(sqljdbc4.jar as driver),I'm concerning the error is caused by the group by statement

Hibernate: 
    WITH query AS (select
        ROW_NUMBER() OVER (
    order by
       proj0_.id desc) as __hibernate_row_nr__,
       proj0_.id as id1_,
       proj0_.content as content1_,
       proj0_.topic as topic1_,
    from
       project proj0_ 
    inner join
       proj_area areas1_ 
           on proj0_.id=areas1_.proj_id 
    inner join
       area area2_ 
           on areas1_.area_id=area2_.id 
    where
       area2_.id in (? , ?)) 
    group by
        proj0_.id,
        proj0_.content,
        proj0_.topic) SELECT
        * 
    FROM
        query 
    WHERE
        __hibernate_row_nr__ BETWEEN ? AND ?

Surprising. Given that the content is marked as lazy, I would have expected it to work fine. Have you instrumented the byte-code at build time, as explained in the doc? http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#performance-fetching-lazyproperties — JB Nizet, Jul 21 '12 at 16:48
You meant cglib-2.2.jar and javassist-3.12.0.GA.jar ? I have checked it again , these are both in my buil time lib.Honestly,I have considered the Lazy property fetch doesn't work before. — hank511, Jul 21 '12 at 17:48
No, I mean what is written in the doc I linked to: *Lazy property loading requires buildtime bytecode instrumentation. If your persistent classes are not enhanced, Hibernate will ignore lazy property settings and return to immediate fetching.* and follows an Ant task explaining how to instrument the bytecode during the build. — JB Nizet, Jul 22 '12 at 06:37
org.hibernate.tool.instrument.InstrumentTask is in org.hibernate3.jar.It seems to depend on CGLib/javaassist. Plz follow this link https://forum.hibernate.org/viewtopic.php?f=1&t=955371 tenwit has answered it. — hank511, Jul 22 '12 at 07:55
I would trust the official documentation rather than a forum post. BTW, it seems that the query does load the content, although it's marked as lazy. — JB Nizet, Jul 22 '12 at 08:06
Yes,and it seems just in SQLServer but not in Mysql.And I have import hibernate3.org in WEB-INF\lib , is it still not enough?Your reply will be appreciated. — hank511, Jul 22 '12 at 08:15
Have you checked that the field is lazy-loaded in MySQL? I would guess that it's eagerly-loaded with MySQL, but MySQL doesn't care about distinct on a LOB. Turn SQL logging on, and see what SQL queries are generated. — JB Nizet, Jul 22 '12 at 08:19
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/14237/discussion-between-hank511-and-jb-nizet) — hank511, Jul 22 '12 at 09:00

score 2 · Answer 1 · answered Jul 21 '12 at 16:42

Workaround is to use subquery to select distinct set of id values and use it to limit values returned. It works because applying distinct to anything else than id is superfluous.

SELECT b 
FROM Project b 
WHERE b.id IN(SELECT distinct o.id 
              FROM Project o join o.areas a 
              WHERE a.id IN(?,?))

score 0 · Accepted Answer · edited May 23 '17 at 12:27

Just as JB Nizet say, and refer to http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#performance-fetching-lazyproperties . That's because the fetch lazyproperties feature doesn't work. According to the document, There are two solutions. Finally, I choose a preferred solution as the document wrotes.

A different way of avoiding unnecessary column reads, at least for read-only transactions, is to use the projection features of HQL or Criteria queries. This avoids the need for buildtime bytecode processing and is certainly a preferred solution.

There is some more explain.@Basic(fetch = FetchType.LAZY) does not work?

JPQL SELECT Distinct Query with Lob Column works fine on MySQL, but SQLServer 2005 doesn't

2 Answers2