The problem is that the original QL compiler which is the default one even now is based on SQL-89 joins. Now try to come up with an SQL for you EJBQL query using SQL-89.
You have to solutions:
1. use DISTINCT. Result should be correct but the SQL query would be inefficient.
2. use SQL92 compiler in 3.2.4 like this
<jbosscmp-jdbc> <defaults> <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler> </defaults> ...
thanks, i actually do not understand why, but it seems to solve the problem with 4.0 - but unfortunately it seems that it is not possible to change the ql-compiler for 3.2.3 with your method.
SQL92 compiler is available since 3.2.4.