EJB Select not working

[stscit04]

Hi,

I have 2 CMP Beans that have a 1-1 relationship
Merchant and MerchantPartnerContract. I need to find
all Merchants that dont have a contract, and to get their
Ids. I trie to do this using the ejb-select

SELECT m.id FROM Merchant AS m WHERE m.merchantPartnerContract IS NULL

JBoss deploys it, but when I call the method, I get the following Exception

Find failed: java.sql.SQLException: General error message from server: "
Unknown table 't1_m_merchantPartnerContract' in where clause";

The table is indeed unknow because it doesnt exist on the database...

I am running Jboss 3.2.2, Mysql 4.0.14 and mysql connector 3.0.16

Thanks in advance for any suggestion,

Stefan

[stscit04]

I set logging to debug and the generated SQL is

Executing SQL: SELECT DISTINCT t0_m.id FROM Merchant t0_m WHERE (t1_m_merchantPartnerContract.id IS NULL)

which doesn't look ok to me...

Regards

Stefan

[aloubyansky]

Try 3.2.6.

[stscit04]

I tried 3.2.6 and the Exception disappeared. But the query still gives
incorrect results (or at least unexpected results, maybe I am thinking
in the wrong way)

The SQL executed under 3.2.6 is:

SELECT DISTINCT t0_m.id FROM Merchant t0_m, MerchantPartnerContract t1_m_merchantPartnerContract WHERE
(t1_m_merchantPartnerContract.id IS NULL AND
t0_m.id=t1_m_merchantPartnerContract.merchantId)

Which doesn't return anything because the id-field of the
merchantPartnerContract is the PK-field and therefore never
NULL.

The query should retun the merchant.id field for all
merchants that don't have a contract (and therefore dont
show up in the merchantParnterContract.merchantId column).

Please let me know how to formulate a EJB QL query that
returns the desired results. Or maybe this IS a bug?

Thanks in advance,

Stefan

[aloubyansky]

Looks like a bug. Try sql92 query compiler
http://www.jboss.org/wiki/Wiki.jsp?page=PlugableQlCompiler

[stscit04]

Hi,

thanks for your help, it works!
Is the SQL92 Compiler scheduled to become the
default compiler for the JBoss 3.x series?

Regards

Stefan

[aloubyansky]

The wiki page explains why it is not the default one. This is our testsuite problem.