Community
Participate
Working Groups
Build ID: n/a Steps To Reproduce: 1. Issue a query containing "where entity in (subquery") 2. EclipseLink will translate this into an invalid SQL statement 3. The database will throw an obscure error More information: EclipseLink should detect the fact that it doesn't support "entity in (subquery)" and throw a clearer error message. It should suggest that developers use "entity.id in (subquery)" instead.
Here's the original message exchange. Andrei Ilitchev wrote: > > The spec. doesn't allow using entities directly with IN (see spec. 4.6.8 > and > 4.14), so instead of > "select image from Image as image where image in (select imageToTag0.image > ... > use something like: > "select image from Image as image where image.id in (select > imageToTag0.image.id ... > > ----- Original Message ----- > From: "cowwoc" <cowwoc@bbs.darktech.org> > To: <eclipselink-users@eclipse.org> > Sent: Wednesday, December 31, 2008 3:34 PM > Subject: [eclipselink-users] EclipseLink converts query wrong > > >> >> Hi, >> >> I invoke EntityManager.createQuery("select image from Image as image >> where >> image in (select imageToTag0.image from ImageToTag as imageToTag0 where >> imageToTag0.tag = :tag0) and image in (select imageToTag1.image from >> ImageToTag as imageToTag1 where imageToTag1.tag = :tag1) order by >> image.id"); >> >> EclipseLink 1.0.2 converts this to: >> >> "t0.ID, t0.VERSION, t0.creationDate, t0.parentId, t0.ownerId, t0.dataId, >> t0.contentTypeId, t0.specificationId, t0.overlayId FROM image t0 WHERE ( >> IN >> (SELECT t1.ID, t1.VERSION, t1.creationDate, t1.parentId, t1.ownerId, >> t1.dataId, t1.contentTypeId, t1.specificationId, t1.overlayId FROM >> image_to_tag t2 LEFT OUTER JOIN image t1 ON (t1.ID = t2.IMAGE_ID) WHERE >> (t2.TAG_ID = ?)) AND IN (SELECT t3.ID, t3.VERSION, t3.creationDate, >> t3.parentId, t3.ownerId, t3.dataId, t3.contentTypeId, t3.specificationId, >> t3.overlayId FROM image_to_tag t4 LEFT OUTER JOIN image t3 ON (t3.ID = >> t4.IMAGE_ID) WHERE (t4.TAG_ID = ?))) ORDER BY t0.ID ASC" under MySQL. >> >> Notice how it produces this malformed SQL: "WHERE ( IN". Any ideas? >> >> Gili >> --
targetting for 1.1X since the bug list for 1.1 is finalized. This will make it a candiate for the next patch release.
This bug is being moved to Future where we can properly sort them all together based on community votes and severity. We will then assign them accordingly to future patch sets and releases.
Changing the priority of the bugs that have been recently triaged to future. Targetting them to P2 will differentiate them from the P3s that have been triaged into future earlier.
Fixed this so it works. object can now be used with in and sub-selects, or parameters.
See, Bug#314025
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
