[eclipselink-users] Native Query throws weird exception

[Michael Simons <michael.simons@xxxxxxxxxxx>]

Hello,

Model: Vehicle<1:*>Tour : A vehicle has associated one or more trips (tours) that are driven
with that vehicle.

In the following query I want to select the vehicles in a certain scenario granted to the curent
user (that's what claim is for) and their tours, but also vehicles that have no tours
associated, so I use a left join:
  Query query = em.createNativeQuery ("SELECT a.*, coalesce(b.tour_id, 1), b.active, ... "+
    "FROM vehicle a "+
    "LEFT JOIN tour b on b.vehicle_id = a.vehicle_id "+
    "WHERE a.scenario_id = ? "+
    "  AND (a.claim_id is null or a.claim_id IN "+
      " (SELECT claim_id from login_user_claim z where z.login_user_id = ?))",
    "VehicleTourMapping");
    // The ... stands for the rest of fields of tour

The build-in MySQL "coalesce(...)" function returns the first of its arguments that's not null.
But when running the query an exception (see below) is thrown. The same exception is thrown when
not using coalesce(), of course.

The reduced query
  "SELECT * "+
    "FROM vehicle a "+
    "LEFT JOIN tour b on b.vehicle_id = a.vehicle_id "+
    "WHERE a.scenario_id = ? "+
    "  AND (a.claim_id is null or a.claim_id IN "+
      " (SELECT claim_id from login_user_claim z where z.login_user_id = ?))",
works, however.

Does anybody have any hints why my "trick" with "coalesce" doesn't work?

Kind Regards, Michael


Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.0.1.v20100213-r6600):
org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [DatabaseRecord(
	tour.tour_id => null
...more fields...
	tour.login_user_id => 1)] during the execution of the query was detected to be null.  Primary
keys must not contain null.
Query: ResultSetMappingQuery(referenceClass=Tour sql="SELECT a.*, coalesce(b.tour_id, 1),
b.active, ...more fields...  FROM vehicle a JOIN tour b on b.vehicle_id = a.vehicle_id where
a.scenario_id = ? and (a.claim_id is null or a.claim_id IN (SELECT claim_id from
login_user_claim z where z.login_user_id = ?))")
	at
org.eclipse.persistence.exceptions.QueryException.nullPrimaryKeyInBuildingObject(QueryException.java:863)
	at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:470)