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

Criteria API generates invalid JPQL for condition based on count subquery

Description

My model has three entities:

  • Taxi

    • shift : Shift

  • Driver

    • taxi : Taxi

  • Shift

    • shiftsPerDay : int

The following query in JPQL works fine:

1 SELECT t FROM Taxi t WHERE (SELECT COUNT(d) FROM Driver d where d.taxi = t) < t.shift.shiftsPerDay

however if I try to use the JPA criteria API:

1 2 3 4 5 6 7 8 9 10 11 return getEntityManager().createQuery( cq.distinct(true).where( cb.not( cb.greaterThanOrEqualTo( cb.count(driverSubquery.select(drivers).where( cb.equal(drivers.get("taxi"), taxi_))), taxi_.get("shift").get("shiftsPerDay") ) ) ) ).getResultList();

it results in invalid JPQL:

1 select distinct generatedAlias0 from taxi_fleet.Taxi as generatedAlias0 where count((select generatedAlias1 from taxi_fleet.Driver as generatedAlias1 where generatedAlias1.taxi=generatedAlias0))<generatedAlias0.shift.shiftsPerDay

whereas this would be correct (note "select(count(...))" instead of "count(select ...)"):

1 select distinct generatedAlias0 from taxi_fleet.Taxi as generatedAlias0 where (select count(generatedAlias1) from taxi_fleet.Driver as generatedAlias1 where generatedAlias1.taxi=generatedAlias0)<generatedAlias0.shift.shiftsPerDay

Environment

None

Status

Assignee

Unassigned

Reporter

Rafael Chaves

Fix versions

None

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.0.2
4.3.11
4.2.21

Priority

Major