[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - JBoss 4.0.2, EJB QL and Left join problem

[SchlauFuchs]

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