CMP - wrong data with jboss > 3.2.1

[idocoding]

hi folks,

i have a problem that occurs only with jboss >= 3.2.3 (including 4.0) and my molimo (http://www.molimo.de) application (source available) - jboss 3.2.1 works fine.
there is a entity bean called ThingBean, with two 1:n relation to itself (one in attribute father, one in attribute type)

it has the followin query method defined:


<query-method>
<method-name>ejbSelectChildren</method-name>
<method-params>
<method-param>de.molimo.server.ThingLocal</method-param>
</method-params>
</query-method>
<ejb-ql>SELECT Object(t) FROM ThingBean AS t WHERE t.father=?1 OR t.type=?1</ejb-ql>


here's the relationship for type (analog for father)

<ejb-relation>
<ejb-relation-name>Thing - Type</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Thing - Type.thing</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>ThingBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>type</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Thing - Type.type</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>ThingBean</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>


now the problem:

imagine a thing has one child (one thing with father==this exists, verified in database), so a call to
ejbSelectChildren(ctx.getEJBLocalObject())
should deliver a collection with one child (it does this with 3.2.1)
but beginning with 3.2.3 it does return a collection of 254-times the same thing.

hope this helps you folks -

thanks,

marcus

[aloubyansky]

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>
...

[idocoding]

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.
thanks, marcus

[aloubyansky]

SQL92 compiler is available since 3.2.4.