JPA: "SELECT DISTINCT" with BLOB columns

Question

I am trying to run this JPQL query:

SELECT DISTINCT i FROM Table i JOIN i.other o

which is quickly crashing:

"Internal exception: java.sql.SQLException: columns of type" BLOB "cannot be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT operations because comparisons for this type are not supported."

This error makes sense to me, but how do I get around it?

Answer 1

You can save the hash or checksum of the blob object in another column and use your own separate operator for this.

Example:

SELECT i from Table  WHERE id IN (
  SELECT id FROM (
    SELECT MIN(id) AS id, hash_of_i FROM Table GROUP BY hash_of_i
                 ) t
                                )

I'm sure you can write this SQL more elegantly, but it will give you an idea.

- , Distinct ( , ).

2 - , ,

Answer 2

HermanD :

SELECT i FROM Table i WHERE EXISTS (
    SELECT e FROM Table e JOIN e.other o WHERE e.id=i.id
)

Answer 3

BLOB . , BLOB , (, ...)

Answer 4

setResultTransformer (Criteria.DISTINCT_ROOT_ENTITY) .