Here's the DEBUG output:
| 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
| ( (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.
<a href="http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3827801#3827801">View the original post</a>
<a href="http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3827801>Reply to the post</a>