CODEWITHSUNDEEP
javamysqlspringhibernate
Chol Nhial
Chol Nhial

Reputation: 1397

Hibernate: MySQL error unknown column in having clause although column exists

I have a JPQL query in a repository that is the equivalent of the MySQL query below:

SELECT DISTINCT ji.* FROM tracker_job_item AS ji
JOIN tracker_work AS w ON ji.id=w.tracker_job_item_id
JOIN tracker_work_quantity AS wq on w.id=wq.tracker_work_id
WHERE w.work_type = 'CUTTING' AND ji.is_finished=0
GROUP BY wq.tracker_work_id
HAVING ji.quantity != SUM(wq.received_quantity)

The MySQL version works just fine, but the JPQL equivalent gives an exception: Unknown column 'jobitem0_.quantity' in 'having clause'

The JPQL query is like below:

@Query("select distinct ji from JobItem ji" +
        "   join Work w on ji.id=w.jobItem.id" +
        "   join WorkQuantity wq on w.id=wq.work.id" +
        "   where w.workType='CUTTING' and ji.isFinished=false and ji.jobItemName like %:search%" +
        "   group by ji.id" +
        "   having ji.quantity != sum(wq.receivedQuantity)")
    Page<JobItem> findAllActiveCuttingJobs(Pageable pageable, @Param("search") String search);

Please help me with why I'm getting the error even though the field quantity exists in JobItem.

Upvotes: 1

Views: 961

Answers (1)

PaulNUK
PaulNUK

Reputation: 5229

You can't reference a column in the having clause that isn't in the group by clause, definitely in JPA and (normally at least) in SQL. Looks like MySQL is letting you get away with it but JPA isn't.

See here:

http://learningviacode.blogspot.co.uk/2012/12/group-by-and-having-clauses-in-hql.html

Upvotes: 2

Related Questions