Re: [eclipselink-users] using IN with a subquery

[bht@xxxxxxxxxxxxx]

Hi,

It appears that a field is missing before the IN clause. I would
recommend to file a bug with a testcase.

Regarding the unexpected table join, please see
https://bugs.eclipse.org/bugs/show_bug.cgi?id=300625

I would recommend to vote for this bug.

Kind Regards,

Bernard


On Thu, 19 May 2011 15:24:28 -0400, you wrote:

>I have 3 entities, User, Account, and Task
>
>this is my query
>
>select t from Task t where t.user = :user or t.account in (select a from 
>User u join u.accounts a where u = :user)
>
>here is the SQL generated
>
>SELECT t0.`id`, t0.`complete_date`, t0.`create_date`, t0.`due_date`, 
>t0.`task_status`, t0.`uuid`, t0.`userid`, t0.`contactid`, t0.`noteid`, 
>t0.`subuserid` FROM `task` t0, `users` t1 WHERE (((t0.`subuserid` = ?) 
>OR  IN (SELECT DISTINCT t2.`userid`, t2.`adminlevel`, t2.`cellno`, 
>t2.`city`, t2.`cityid`, t2.`country`, t2.`disclaimer`, t2.`email`, 
>t2.`firstname`, t2.`lastname`, t2.`managerid`, t2.`office`, 
>t2.`officeid`, t2.`password`, t2.`phoneno`, t2.`region`, t2.`regionid`, 
>t2.`signatureline`, t2.`signaturestatus`, t2.`team`, t2.`teamid`, 
>t2.`title`, t2.`companyid` FROM `subuseraccess` t4, `subusers` t3, 
>`users` t2 WHERE ((? = t3.`subuserid`) AND ((t4.`subuserid` = 
>t3.`subuserid`) AND (t2.`userid` = t4.`userid`))))) AND (t1.`userid` = 
>t0.`userid`))
>     bind => [6, 6]
>
>the JPA query seems to be fine, but the SQL it generates is not.. here 
>is the mysql error
>
>You have an error in your SQL syntax; check the manual that corresponds 
>to your MySQL server version for the right syntax to use near 'IN 
>(SELECT DISTINCT t2.`userid`, t2.`adminlevel`, t2.`cellno`, t2.`city`, 
>t2.`ci' at line 1
>
>
>
>also, why does it include the users table (t1)..  this is what I would 
>HOPE it generated
>
>SELECT t0.`id`, t0.`complete_date`, t0.`create_date`, t0.`due_date`, 
>t0.`task_status`, t0.`uuid`, t0.`userid`, t0.`contactid`, t0.`noteid`, 
>t0.`subuserid` FROM `task` t0 WHERE (((t0.`subuserid` = 6) OR 
>t0.`userid` IN (SELECT DISTINCT t2.`userid` FROM `subuseraccess` t4, 
>`subusers` t3, `users` t2 WHERE ((6 = t3.`subuserid`) AND 
>((t4.`subuserid` = t3.`subuserid`) AND (t2.`userid` = t4.`userid`))))))
>     bind => [6, 6]
>_______________________________________________
>eclipselink-users mailing list
>eclipselink-users@xxxxxxxxxxx
>https://dev.eclipse.org/mailman/listinfo/eclipselink-users