Re: [eclipselink-users] Potential bug in EclipseLink

[Michael Simons <michael.simons@xxxxxxxxxxx>]

Hello Chris,

I already tried an outer join using any of two possibilities:
() join within the query
() by a query hint

The resulting SQL query was always the same.

The way we are now going is to hold both the id itself as well as the object reference, e.g.
class Depot {

  @ManyToOne(fetch=FetchType.LAZY, cascade=CascadeType.MERGE)
  @JoinColumn(name="claim_id", nullable=true)
  private Claim claim;

  @Column(name="claim_id", insertable=false, updatable=false, nullable=true)
  private Integer claimid;
}

This way we can rewrite the query to
"SELECT x FROM Depot x WHERE x.scenario = :currentScenario AND (claimid is null OR claimid in (%s))"

What do you think about this idea?

Kind Regards,
Michael

Am 07.09.2010 15:08, schrieb Christopher Delahunt:
> Hello Michael,
> 
> Because you are using x.claim.id, this causes an inner join to be used.  You will need to
> specify an outer join in the from clause:
> 
> SELECT x FROM Depot x outer join x.claim claim WHERE x.scenario = :currentScenario and (claim is
> null or claim.id in(%s))
> 
> Best Regards, Chris
> 
> 
> 
> On 07/09/2010 8:13 AM, Michael Simons wrote:
>> Hello,
>>
>> JPQL-Query:
>> SELECT x FROM Depot x WHERE x.scenario = :currentScenario and (x.claim is null or x.claim.id in
>> (%s))
>>
>> is transalted to the following SQL:
>> SELECT t1.depot_id, t1.bot_pump_rate, t1.available, t1.ext_id, t1.jdo_version, t1.name3,
>> t1.avg_wait, t1.top_pump_rate, t1.nme, t1.name2, t1.alt_ex_id, t1.depot_type_id, t1.claim_id,
>> t1.schedule_id, t1.corporate_id, t1.measure_id, t1.lcation_id, t1.scenario_id, t0.claim_id,
>> t0.description, t0.nme
>> FROM claim t0, depot t1
>> WHERE (((t1.scenario_id = ?)
>>   AND ((t1.claim_id IS NULL) OR (t0.claim_id IN (?, ?, ?, ?, ?, ?))))
>>   AND (t0.claim_id = t1.claim_id))
>>
>> This is not correct because the natural join of the claim table suppresses the lines in t1 with
>> a claim_id of NULL, but these lines must also be selected.
>>
>> Do I fail or did EL fail here?
>>
>> Kind Regards,
>> Michael
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>   
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>