I have the following EJB-QL statement :
SELECT OBJECT( o )
FROM User o ,IN(o.positions) p
WHERE ( o.organicPosition.id = ?1 OR p.id = ?2 )
to get the users that have a specific organicPosition X (?1) or contain position Y (?2)
this is translated to the following SQL statement:
SELECT t0_o.id FROM tuser t0_o, tposition t2_p, tuser_position t5_o_positions_RELATION_TABLE, tposition t1_o_organicPosition WHERE (((t1_o_organicPosition.id = ? OR t2_p.id = ?) AND t0_o.position=t1_o_organicPosition.id)) AND t0_o.id=t5_o_positions_RELATION_TABLE.user_id AND t2_p.id=t5_o_positions_RELATION_TABLE.position_id ORDER BY t0_o.rnumber ASC
This EJB-QL statement doesn't return users that don't have an organicPosition even though they contain position Y.
I think that the following SQL statement should be generated:
SELECT t0_o.id
FROM tposition t2_p INNER JOIN
tuser_position t5_o_positions_RELATION_TABLE ON t2_p.id = t5_o_positions_RELATION_TABLE.position_id RIGHT OUTER JOIN
tuser t0_o ON t5_o_positions_RELATION_TABLE.user_id = t0_o.id LEFT OUTER JOIN
tposition t1_o_organicPosition ON t0_o.[position] = t1_o_organicPosition.id
WHERE (t1_o_organicPosition.id = 10) OR (t2_p.id = 10)
Is this correct or there is something wrong with my EJB-QL statement?
I am using JBoss 3.2.3
