Hi all, I'm currently hanging in a EJB QL which doesn't do what I expect.
This is the query: | java.util.Collection findAllBySubNetwork(SubNetworkLocal subNetwork, long date) | SELECT OBJECT(o) FROM ChargingPoint AS o, IN(o.history) AS h | WHERE h.isActive = true | AND h.beginDate <= ?2 AND (h.endDate IS NULL OR h.endDate > ?2) | AND (h.downstreamSubNetwork = ?1 OR h.upstreamSubNetwork = ?1) | ORDER BY o.identifier I clear words, I'm looking for an object whose history object is valid and where the history's downstreamSubNetwork and/or upstreamSubNetwork equals to my parameter. This is a simple left join from SQL perspective. Not as simple is because the subnetworks are objects, so it must create a secondary left join from history to subnetwork. The resulting SQL statement for MySQL looks as this: SELECT t0_o.PK, t0_o.IDENTIFIER | FROM UMSPEISE_PUNKTE t0_o, | CHARGING_POINT_HIST t1_h, | TEIL_NETZE t2_h_downstreamSubNetwork, | TEIL_NETZE t3_h_upstreamSubNetwork | WHERE | ((t1_h.IS_ACTIVE = 1 AND t1_h.BEGIN_DATE <= ? | AND (t1_h.END_DATE IS NULL OR t1_h.END_DATE > ?) | AND ((t2_h_downstreamSubNetwork.PK=?) OR (t3_h_upstreamSubNetwork.PK=?)) | AND t1_h.FK_DOWNSTREAM_SUB_NETWORK=t2_h_downstreamSubNetwork.PK | AND t1_h.FK_UPSTREAM_SUB_NETWORK=t3_h_upstreamSubNetwork.PK)) | AND t0_o.PK=t1_h.FK_CP_HISTORY_HEAD | ORDER BY t0_o.IDENTIFIER ASC; As far as it looks to me in the bold marked lines JBoss made an inner join of my EJB QL statement. In result I can't see rows/objects) where one of history's FK_UPSTREAM_SUB_NETWORK or FK_DOWNSTREAM_SUB_NETWORK is NULL. Using JBoss 4.02, J2EE 1.4, MySQL 4.1.10-nt. Is this a bug of Jboss or do I have to improve my EJB QL statement? Suggestions? Thank you for help! Kai View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3966716#3966716 Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3966716 _______________________________________________ jboss-user mailing list [email protected] https://lists.jboss.org/mailman/listinfo/jboss-user
