What is the error you are getting?
BTW, the first query could be rewritten to:
SELECT DISTINCT OBJECT(m) FROM Machine m, IN (m.machineClasses) mc, IN (mc.permissions) p WHERE p.user = ?1
I'm not getting an error, but I'm also not getting the desired results. A permission has a machine class and either a User or a UserGroup but not both. Users can belong to UserGroups. Machines belong to MachineClasses.
The two possible paths of relation are:
User -> Permission -> MachineClass -> Machine
User -> UserGroup -> Permission -> MachineClass -> Machine
I'm trying to write a query that will return all machines to which a user is related. When I run the first or second query I posted earlier, I get the correct lists of machines. When I run the combined query I posted however, an empty set is returned.
I'm thinking that maybe I've written the third query so that it will only return a Machine if both a User and a UserGroup the User belongs to are associated with the same Permission, which should never be the case.
Looks weird to me either. Could you please increase log level to DEBUG for category org.jboss.ejb.plugins.cmp in server/default/conf/log4j.xml file and check the generated sql statement for this query?
Then we'll see were the problem is.
Here is the generated SQL for query 1:
2002-10-14 18:39:16,340 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.blah/User.ejbSelectMachinesByUserTest1] Executing SQL: SELECT DISTINCT t0_m.id FROM machines t0_m, machine_classes t2_mc, machine_class_membership t3_m_machineClasses_RELATION_TAB, machine_class_permissions t4_p, users t1_p_user WHERE ((t1_p_user.id=?)) AND (t0_m.id=t3_m_machineClasses_RELATION_TAB.machine_id AND t2_mc.id=t3_m_machineClasses_RELATION_TAB.machine_class_id AND t4_p.user_id=t1_p_user.id AND t2_mc.id=t4_p.machine_class_id)
Here is the generated sql for query 2:
2002-10-14 18:39:16,424 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.blah/User.ejbSelectMachinesByUserTest2] Executing SQL: SELECT DISTINCT t0_m.id FROM users t1_u, machines t0_m, machine_classes t4_mc, machine_class_membership t5_m_machineClasses_RELATION_TAB, machine_class_permissions t6_p, user_groups t2_u_userGroups, user_group_membership t7_u_userGroups_RELATION_TABLE, user_groups t3_p_userGroup WHERE ((t1_u.id=?) AND (t2_u_userGroups.id = t3_p_userGroup.id)) AND (t1_u.id=t7_u_userGroups_RELATION_TABLE.user_id AND t2_u_userGroups.id=t7_u_userGroups_RELATION_TABLE.user_group_id AND t0_m.id=t5_m_machineClasses_RELATION_TAB.machine_id AND t4_mc.id=t5_m_machineClasses_RELATION_TAB.machine_class_id AND t6_p.user_group_id=t3_p_userGroup.id AND t4_mc.id=t6_p.machine_class_id)
Here's the generated sql for the combined query:
2002-10-14 18:39:16,264 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.blah/User.ejbSelectMachinesByUser] Executing SQL: SELECT DISTINCT t0_m.id FROM users t1_u1, users t3_u2, machines t0_m, machine_classes t6_mc, machine_class_membership t7_m_machineClasses_RELATION_TAB, machine_class_permissions t8_p, user_groups t5_p_userGroup, users t2_p_user, user_groups t4_u2_userGroups, user_group_membership t9_u2_userGroups_RELATION_TABLE WHERE (((t1_u1.id=?) AND (t2_p_user.id = t1_u1.id)) OR ((t3_u2.id=?) AND (t4_u2_userGroups.id = t5_p_userGroup.id))) AND (t0_m.id=t7_m_machineClasses_RELATION_TAB.machine_id AND t6_mc.id=t7_m_machineClasses_RELATION_TAB.machine_class_id AND t8_p.user_group_id=t5_p_userGroup.id AND t8_p.user_id=t2_p_user.id AND t3_u2.id=t9_u2_userGroups_RELATION_TABLE.user_id AND t4_u2_userGroups.id=t9_u2_userGroups_RELATION_TABLE.user_group_id AND t6_mc.id=t8_p.machine_class_id)
Do you have some sql client? Can you run the problematic query in it?
It's difficult for me to read this huge sql and tell you what's wrong in it not even knowing the db structure.
If you believe it's a bug, please, post a simple test case that I can deploy and figure it out.
Well, I finally tracked down the issue and determined that it's just not feasible to write the query I need in EJB-QL. I have now switched to using <declared-sql> and everything is working great. Thanks for all your help!