jpa 2.0 - Difference in where clause beetwen JPQL and CriteriaBuilder -

jpa 2.0 - Difference in where clause beetwen JPQL and CriteriaBuilder -

i have 2 separate entity class: job , joborgunitcfg below set 2 pieces of code create same select on objects.

first query in jpql:

query joborgunitcfgquery = entitymanager.createquery( "select c joborgunitcfg c c.orgid = :orgid , c.schedulernextactivation < current_timestamp , c.active = :active , " + " not exists (select j job j j.orderid = c.orderid , j.orgid = c.orgid , j.status <> :jobstatus)"); joborgunitcfgquery.setparameter("orgid", orgid); joborgunitcfgquery.setparameter("jobstatus", jobstatusenum.end); joborgunitcfgquery.setparameter("active", boolean.true); homecoming joborgunitcfgquery.getresultlist();

and sec query build criteriabuilder:

criteriabuilder cb = entitymanager.getcriteriabuilder(); criteriaquery<joborgunitcfg> criteria = cb.createquery(joborgunitcfg.class); root<joborgunitcfg> joborgunitcfgroot = criteria.from(joborgunitcfg.class); subquery<job> subquery = criteria.subquery(job.class); root<job> jobroot = subquery.from(job.class); subquery.where( cb.and( cb.equal(jobroot.get(job_.orderid), joborgunitcfgroot.get(joborgunitcfg_.orderid)), cb.equal(jobroot.get(job_.orgid), joborgunitcfgroot.get(joborgunitcfg_.orgid)), cb.not(cb.equal(jobroot.get(job_.status), jobstatusenum.end)) ) ); predicate = cb.and(cb.equal(joborgunitcfgroot.get(joborgunitcfg_.orgid), orgid), cb.lessthan(joborgunitcfgroot.get(joborgunitcfg_.schedulernextactivation), cb.currenttimestamp()), cb.equal(joborgunitcfgroot.get(joborgunitcfg_.active), boolean.true), cb.not(cb.exists(subquery)) ); criteria.where(where); typedquery<joborgunitcfg> query = entitymanager.createquery(criteria); homecoming query.getresultlist();

first generate wrong clause (duplicate table s_jobs_org_unit_cfg within subselect):

select ... s_jobs_org_unit_cfg t0 ((((t0.org_id = ?) , (t0.scheduler_next_activation < sysdate)) , (t0.active = ?)) , not exists (select ? s_jobs_org_unit_cfg t2, s_jobs t1 ((((t1.order_id = t2.order_id) , (t1.org_id = t2.org_id)) , (t1.status <> ?)) , (t0.order_id = t2.order_id))) )

sql criteriabuilder right:

select ... s_jobs_org_unit_cfg t0 ((((t0.org_id = ?) , (t0.scheduler_next_activation < sysdate)) , (t0.active = ?)) , not exists (select ? s_jobs t1 (((t1.order_id = t0.order_id) , (t1.org_id = t0.org_id)) , not ((t1.status = ?)))) )

can explain me wrong in jpql ?

i think bug in jpql added table join. should not impact result.

it should fixed in latest release or build of eclipselink.

jpa-2.0 eclipselink jpql criteria-api