openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Fay Wang (JIRA)" <j...@apache.org>
Subject [jira] Updated: (OPENJPA-1547) NOT IN with MEMBER OF returns syntax error
Date Tue, 02 Mar 2010 19:25:27 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-1547?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Fay Wang updated OPENJPA-1547:
------------------------------

    Attachment: OPENJPA-1547.patch

The patch will process the following JPQL:

SELECT a.name FROM EntityA a 
WHERE a.id not in 
   (select b.id FROM EntityB b where ?1 MEMBER OF b.nickNames)

and generate the following SQL:

SELECT t0.name FROM EntityA t0 WHERE 
(NOT (t0.id IN (
    SELECT t1.id FROM EntityB t1 
        INNER JOIN EntityB_nickNames t2 ON t1.id = t2.ENTITYB_ID 
	WHERE (t2.element = ?) )))  [params=(String) token1]


> NOT IN with MEMBER OF returns syntax error
> ------------------------------------------
>
>                 Key: OPENJPA-1547
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-1547
>             Project: OpenJPA
>          Issue Type: Bug
>            Reporter: Fay Wang
>            Assignee: Fay Wang
>             Fix For: 2.1.0
>
>         Attachments: OPENJPA-1547.patch
>
>
> With the following entities:
> @Entity
> public class EntityA {
> 	@Id
> 	int id;
> 	int age;
> 	String name;
> }
> @Entity
> public class EntityB {
> 	@Id
> 	int id;
> 	int age;
>        @PersistentCollection(fetch=FetchType.LAZY)
> 	String[] nickNames;
> }
> OpenJPA generates wrong SQL for the JPQL:
>         String jpql = "SELECT a.name FROM EntityA a WHERE a.id not in " + 
>         "(select b.id FROM EntityB b where ?1 MEMBER OF b.nickNames)";
>  
> SELECT t0.name FROM EntityA t0 
> WHERE (0 = (SELECT COUNT(*) FROM  
>      WHERE (t0.id IN (
>           SELECT t1.id 
> 	       FROM EntityB t1 
> 	       INNER JOIN EntityB_nickNames t2 ON t1.id = t2.ENTITYB_ID 
> 	       WHERE (t2.element = ?) )) ))  [params=(String) token1]
> Note that if the JPQL is:
>         String jpql = "SELECT a.name FROM EntityA a WHERE a.id in " + 
>         "(select b.id FROM EntityB b where ?1 MEMBER OF b.nickNames)";
> The push-down sql is correct:
> SELECT t0.name FROM EntityA t0 
>       WHERE (t0.id IN 
>           (SELECT t1.id 
> 	        FROM EntityB t1 
> 		INNER JOIN EntityB_nickNames t2 ON t1.id = t2.ENTITYB_ID 
> 		WHERE (t2.element = ?) ));

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message