WebLogic ejbCompiler weblogic-ejb-ql to SQL translation problems

[Simon]

I have a problem generating correct SQL to match my weblogic-ejb-ql.
The problem is the same in WLS 8.1 SP2 and SP3.

Basically, I can't generate SQL to correctly navigate a 1-1 parent -
child relationship. On the database, root nodes have a null parentId,
so I should be able to find them with the weblogic-ejb-ql query

SELECT OBJECT(root) from TSDPMenuItemEJB root where
root.ejbParentMenuItem IS NULL ORDERBY root.rank

this actually generates the following SQL code in the container class

__WL_query = "SELECT WL0.menuItemId, WL0.BehaviourPhrase,
WL0.displayname, WL0.FilterPhrase, WL0.Rank, WL0.VisibleInProduction,
WL0.parentId , WL0.Rank FROM tsdpmenuitem WL0 WHERE ( WL0.menuItemId
NOT IN ( SELECT WL2.menuItemId FROM tsdpmenuitem WL2, tsdpmenuitem WL1
WHERE WL2.menuItemId = WL1.parentId) ) ORDER BY WL0.Rank ASC ";

Which obviously has nothing to do with what I asked for.

likewise, when to find the children of a particular node, the
weblogic-ejb-ql looks like:

SELECT OBJECT(child) from TSDPMenuItemEJB child, TSDPMenuItemEJB
parent where child.ejbParentMenuItem = parent and parent.menuItemId =
?1 ORDERBY child.rank

but the SQL looks like:

__WL_query = "SELECT WL0.menuItemId, WL0.BehaviourPhrase,
WL0.displayname, WL0.FilterPhrase, WL0.Rank, WL0.VisibleInProduction,
WL0.parentId , WL0.Rank FROM tsdpmenuitem WL0 , tsdpmenuitem WL2 ,
tsdpmenuitem WL1 WHERE ( ( (WL2.menuItemId = WL1.menuItemId)) AND (
WL1.menuItemId = ? ) ) AND WL0.menuItemId = WL2.parentId ORDER BY
WL0.Rank ";

Can someone help me form a weblogic-ejb-ql query which forms the SQL
to do the tasks I'm trying to achieve?

Many thanks in advance;
Simon

PS: I have the following ejbgen annotations in WL Workshop

* @ejbgen:finder ejb-ql="" weblogic-ejb-ql="SELECT OBJECT(root) from
TSDPMenuItemEJB root where root.ejbParentMenuItem IS NULL ORDERBY
root.rank ASC" generate-on="Local" signature="Collection
findRootMenuItems()"
* @ejbgen:relation role-name="TSDPMenuItemEJB-has-TSDPMenuItemEJB"
cmr-field="ejbParentMenuItem" target-ejb="TSDPMenuItemEJB"
multiplicity="One" name="MenuItem-parentMenuItem"
* @ejbgen:relation primary-key-table="tsdpmenuitem"
foreign-key-table="tsdpmenuitem"
role-name="TSDPMenuItemEJB-has-TSDPMenuItemEJB_" fk-column="parentId"
target-ejb="TSDPMenuItemEJB" multiplicity="One"
name="MenuItem-parentMenuItem"
* @ejbgen:finder ejb-ql="" weblogic-ejb-ql="SELECT OBJECT(child) from
TSDPMenuItemEJB child, TSDPMenuItemEJB parent where
child.ejbParentMenuItem = parent and parent.menuItemId = ?1 ORDERBY
child.rank" generate-on="Local" signature="Collection
findChildMenuItems(java.lang.Long parentMenuItemId)"