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

[holyjoe]

I have no idea if this will work, or even if it's legal HQL, but the first thing I would try is

SELECT OBJECT(o) FROM ChargingPoint AS o, IN(o.history) AS h RIGHT JOIN SubNetwork n
 WHERE h.isActive = true
 AND h.beginDate <= ?2 AND (h.endDate IS NULL OR h.endDate > ?2)
 AND (h.downstreamSubNetwork = n OR h.upstreamSubNetwork = n)
 AND (n = ?1)
 ORDER BY o.identifier

[schlaufuchs]

Sorry, didn't work:

08:26:01,002 INFO [STDOUT] org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "RIGHT" at line 1, column 62.
Was expecting one of:

"," ...

[holyjoe]

Sorry about that. I'm afraid I don't have the ambition to write a small test app so that I can get the query right, but if you're stuck for ideas you may want to tweak it to get the RIGHT JOIN working (I know that part is legal, anyway). I suspect it is because of the use of IN() instead of INNER JOIN syntax, but I'm not sure. Maybe something like

SELECT OBJECT(o) FROM ChargingPoint AS o JOIN o.history AS h RIGHT JOIN SubNetwork n
 WHERE h.isActive = true
 AND h.beginDate <= ?2 AND (h.endDate IS NULL OR h.endDate > ?2)
 AND (h.downstreamSubNetwork = n OR h.upstreamSubNetwork = n)
 AND (n = ?1)
 ORDER BY o.identifier

Again, though, I don't have a whole lot of hope that the right join will fix the problem anyway, so you may not want to spend too much time on it. Perhaps just issuing two queries won't be too much of a performance problem.

Good luck.

[schlaufuchs]

I think it was the keyword RIGHT which he doesn't like. Seems not to be compatible. In meantime I created two separate finders and then I join the collection. May be a little less efficient but at least it is working.

Ciao!
Kai