4 Replies Latest reply on Aug 23, 2006 9:59 AM by Kai Hackemesser

    JBoss 4.0.2, EJB QL and Left join problem

    Kai Hackemesser Newbie

      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

        • 1. Re: JBoss 4.0.2, EJB QL and Left join problem
          Joe DeStefano Newbie

          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
          



          • 2. Re: JBoss 4.0.2, EJB QL and Left join problem
            Kai Hackemesser Newbie

            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:

            "," ...

            • 3. Re: JBoss 4.0.2, EJB QL and Left join problem
              Joe DeStefano Newbie

              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.



              • 4. Re: JBoss 4.0.2, EJB QL and Left join problem
                Kai Hackemesser Newbie

                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