mysql ejb-ql query with collection comparison

[chris_pollentier]

Hi,

We are moving our project from a postgres database to a mysql database.
I encountered ad problem with one of our finders:

There are two tables: 'Kp' and 'scanner'. The scanner table has a foreign key to the Kp table. I defined a one-to-many relation between scanner to kp

I defined the following ejb-ql to get all Kp records with a scanner record pointing to it:

SELECT DISTINCT OBJECT(o) FROM Kp o WHERE o.countryId = ?1 AND o.scanners IS NOT EMPTY

When I trace the SQL statements of the cmp engine, I see the following in the log file:

2004-04-29 11:36:58,695 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Kp#findByCountryIdAndHasScanner] Executing SQL: SELECT DISTINCT t0_o.kp_pk FROM kp t0_o WHERE (t0_o.country_fk = ? AND t1_o_scanners.scanner_pk IS NOT NULL)
2004-04-29 11:36:58,696 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Kp#findByCountryIdAndHasScanner] Find failed
java.sql.SQLException: General error, message from server: "Unknown table 't1_o_scanners' in where clause"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)


In the SQL query that is executed, the table 'scanner' is missing in the FROM part.

Does anyone know if this a bug related to mysql (it worked fine with postgres), or is there an error in my EJQ-QL query ?

jbosscmp-jdbc.xml snippet:

<ejb-relation>
<ejb-relation-name>kp-cmp20-scanner-cmp20</ejb-relation-name>
<foreign-key-mapping/>

<ejb-relationship-role>
<ejb-relationship-role-name>scanner-cmp20-has-kp-cmp20</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields/>

</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>kp-cmp20-has-scanner-cmp20</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>kpId</field-name>
<column-name>kp_fk</column-name>
</key-field>
</key-fields>

</ejb-relationship-role>
</ejb-relation>

<ejb-relation>
<ejb-relation-name>parcel-cmp20-barcode-cmp20</ejb-relation-name>
<foreign-key-mapping/>

<ejb-relationship-role>
<ejb-relationship-role-name>barcode-cmp20-has-parcel-cmp20</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields/>

</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>parcel-cmp20-has-barcode-cmp20</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>parcelId</field-name>
<column-name>parcel_fk</column-name>
</key-field>
</key-fields>

</ejb-relationship-role>
</ejb-relation>


ejb-jar snippet:

<ejb-relation >
<ejb-relation-name>parcel-cmp20-barcode-cmp20</ejb-relation-name>

<ejb-relationship-role >
<ejb-relationship-role-name>barcode-cmp20-has-parcel-cmp20</ejb-relationship-role-name>
Many
<relationship-role-source >
<ejb-name>Barcode</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>parcel</cmr-field-name>
</cmr-field>
</ejb-relationship-role>

<ejb-relationship-role >
<ejb-relationship-role-name>parcel-cmp20-has-barcode-cmp20</ejb-relationship-role-name>
One
<relationship-role-source >
<ejb-name>Parcel</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>barcodes</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>

</ejb-relation>



<ejb-relation >
<ejb-relation-name>kp-cmp20-scanner-cmp20</ejb-relation-name>

<ejb-relationship-role >
<ejb-relationship-role-name>scanner-cmp20-has-kp-cmp20</ejb-relationship-role-name>
Many
<relationship-role-source >
<ejb-name>Scanner</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>kp</cmr-field-name>
</cmr-field>
</ejb-relationship-role>

<ejb-relationship-role >
<ejb-relationship-role-name>kp-cmp20-has-scanner-cmp20</ejb-relationship-role-name>
One
<relationship-role-source >
<ejb-name>Kp</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>scanners</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>

</ejb-relation>

[chris_pollentier]

Forgot to mention:

The version of mysql = mysql-standard-4.0.18
Jboss version = 3.2.3
Both running on Red Hat Linux release 9 (Shrike)

[triathlon98]

Which version of JBoss are you using?

[triathlon98]

Oops, was too quick there.
Could you also test using 3.2.4RC1 (or even the latest version from CVS) ? There have been a few improvements recently.

[chris_pollentier]

I managed to get it working by changing the ejb-ql :

SELECT DISTINCT OBJECT(o) FROM Kp o, IN (o.scanners) sc WHERE o.countryId = ?1