[eclipselink-users] Potential Bug in [EL 2.0] CriteriaQuery with in-oper

[Michael Simons <michael.simons@xxxxxxxxxxx>]

Hello,

EclipseLink 2.0 generates wrong SQL from a CriteriaQuery that contains an in-operator.

The model in use:
Scenario <1:*> Vehicle <*:1> Claim

A Scenario contains one or more Vehicles. A Vehicle has assigned no or one Claim.

I need a query to return the vehicles within a given scenario having either no claim assigned or
on that is in a given list of claims.

The first CriteriaQuery I wrote was
  CriteriaBuilder cfab = em.getCriteriaBuilder ();
  CriteriaQuery<Vehicle> cq = cfab.createQuery (Vehicle.class);
  Root<Vehicle> vehicle = cq.from (Vehicle.class);
  cq.where (cfab.and (cfab.equal (vehicle.get("scenario"), scenario),
    cfab.or (vehicle.get ("claim").in (claims), vehicle.get("claim").isNull ())));
  List<Vehicle> result = em.createQuery(cq).getResultList();

That did not work, I got the following exception:
Exception [EclipseLink-6075] (Eclipse Persistence Services - 2.0.0.v20091127-r5931):
org.eclipse.persistence.exceptions.QueryException
Exception Description: Object comparisons can only use the equal() or notEqual() operators.
Other comparisons must be done through query keys or direct attribute level comparisons.

So I changed the "where"-line to:
cq.where (cfab.and (cfab.equal (vehicle.get("scenario"), scenario),
    cfab.or (vehicle.get ("claim").get("id").in (claimkees), vehicle.get("claim").isNull ())));
// claimkkees contains only the integer keys of the claims, now

This time it works but it's not correct, because the join of the claim table does not take into
account.
SELECT t1.vehicle_id, ...
FROM claim t0, vehicle t1
WHERE (((t1.scenario_id = ?)
  AND ((t0.claim_id IN (?, ...))
   OR (t1.claim_id IS NULL)))
  AND (t0.claim_id = t1.claim_id))

So either I'm not correctly building the condition or EL does not map it correctly.
Can anybody tell me what's the matter, please?


Kind Regards, Michael