summary

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:

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:

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:

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 ...)"):

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

5.0_confirmed_bug

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

query-criteria

Affects versions

4.3.11

5.0.2

4.2.21

Priority

Major

Configure