4 Replies Latest reply on Mar 29, 2004 5:35 AM by aloubyansky

    BUG: EJB-QL OR operator fails with nulls in inner join

    erik777

      I think I found a bug in EJB-QL. Please correct me if I am doing things incorrectly.

      I'm very much aware of the limits in EJB-QL today with regards to NULLs, and have had to create many queries to handle instances where parameters would otherwise NULL values. There's nothing more I'd like to see fixed in J2EE than today's limitations to EJB-QL. However, this is different as it does not depend on parameters at all. Instead, it is based solely on the values in the database.

      Basically, I have a case where a user session (s) includes a domain ID, which may be null. Similarly, each user role is tied to a domain, which may be null. The null values represent a concept I call "GLOBAL" and are valid.

      In EJB-QL, I am tryting to say the following:

      - If the session domain Id is NULL, give me all roles belonging to the user. Otherwise...
      - only give me user roles that match the sesion's domain Id.

      This is the logic that fails wiht EJB-QL:

      s.domainId IS NULL OR
      s.domainId = rm.role.domain.id

      In a test case, a user has 3 roles, and one of them is GLOBAL (null domain). For the GLOBAL one, the following assertions are true:

      s.domainId IS NULL
      rm.role.domain IS NULL

      The latter assertion is where I believe JBoss is having trouble handling the OR propertly. If I only include

      s.domainId IS NULL

      in the EJB-QL, then I get back all three roles if the domainId is null, which is correct. If I only include

      s.domainId = rm.role.domain.id

      in the query, then I only get back the two matching roles if s.domainId is not null. This is correct, since rm.role.domain is null in this case, making it impossible to obtain the id of the domain.

      However, when you combine them with an OR statement, I suspect that JBoss is incorrectly restricting the results to this inner join even when s.domainId IS NULL . Thus, it is not correctlyapplying the OR statement, which should in effect return a "unique union" of the two result sets on both sides of the OR.

      Here's is the complete EJQ-QL that fails to ever return roles with a null domain:

       SELECT DISTINCT OBJECT(rm)
       FROM rolemodules AS rm,
       IN (rm.role.userRoles) ur,
       IN (ur.user.sessions) s
       WHERE s.id = ?1 AND s.randomId = ?2 AND
       (s.domainId IS NULL OR
       s.domainId = rm.role.domain.id) AND
       rm.module.application.name = ?3 AND
       rm.module.name = ?4
      

      Has this been fixed in 3.2.4? If not, how do we submit bug reports? I've been using JBoss since 2.4.0, and honestly don't know how to submit bug reports. LOL

      Thank you,

      Erik
      http://as.JoshuaBranch.com
      application security today


        • 1. Re: BUG: EJB-QL OR operator fails with nulls in inner join
          triathlon98

          What version of JBoss are you using ? There was a problem with OR handing which (if I remember correctly) was fixed in 3.2.3.

          To make it easier to determine what the problem is, please enable DEBUG logging for the org.jboss.ejb.plugins.cmp package. The SQL which is used will then be logged. Post this SQL here.

          Joachim

          • 2. Re: BUG: EJB-QL OR operator fails with nulls in inner join
            erik777

            I am using JBoss 3.2.3. I'll report back when I have time to post the SQL.

            • 3. Re: BUG: EJB-QL OR operator fails with nulls in inner join
              erik777

              Here's the DEBUG output:

              Executing SQL:

              SELECT DISTINCT t0_rm.id
               FROM rolemodules t0_rm,
               userroles t12_ur,
               sessions t1_s,
               roles t5_rm_role,
               domains t2_rm_role_domain,
               modules t4_rm_module,
               applications t3_rm_module_application,
               users t13_ur_user
               WHERE
               ( (t1_s.id = ? AND t1_s.randomId = ? AND
               (t1_s.domainId IS NULL OR
               t1_s.domainId = t2_rm_role_domain.id) AND
               t3_rm_module_application.name = ? AND t4_rm_module.name = ? AND
               t0_rm.roleId=t5_rm_role.id AND t5_rm_role.domainId=t2_rm_role_domain.id AND
               t0_rm.moduleId=t4_rm_module.id AND
               t4_rm_module.applicationId=t3_rm_module_application.id)) AND
               t13_ur_user.id=t1_s.userId AND t12_ur.userId=t13_ur_user.id AND
               t5_rm_role.id=t12_ur.roleId AND t0_rm.roleId=t5_rm_role.id
              


              The problem appears to be that t1_s.domainId = t2_rm_role_domain.id creates an inner join limited to domains entries that match t1_s.domainId (session.domainId), thus excluding all sessions with a null domainId.

              Can you think of another way to write the EJB-QL to accomplish this? After looking at the SQL I think I'd probably consider a subquery or union to avoid the inner join if I were doing this in SQL. Of course, MySQL is adding these features into its newer versions, so I'm not even sure they are options.

              I'm not too sure how the J2EE spec handles this. My guess is that it doesn't address it. The question is, does JBoss need to use some sort of DISTINCT UNIONs or subquerires (simuluated of driver or database doesn't support) to handle OR operations containing an inner join on one side of the OR? Does it perhaps need a level of abstraction to complete queries in object form instead of converting them entirely to a single SQL statement? I'm not familiar enough with the details of CMP 2.0 requirements to really understand what all is involved.

              Of the least, I hope JBoss CMP developers are involved in the JCP process. We sure do need them there. There's nothing I want to see more than CMP 3.0 become available with dramatic improvements to EJB-QL.



              • 4. Re: BUG: EJB-QL OR operator fails with nulls in inner join
                aloubyansky

                In EJBQL paths that appear in the FROM should be inner-joined. Other paths should be outer-joined. Current compiler in 3.2 does not support outer joins and has some hucks in this regards.
                There is a new QL compiler in HEAD that is INNER/OUTER based. It might be ported to 3.2 one day.