5 Replies Latest reply on Mar 8, 2004 8:31 PM by mike andrews

    ejbql finder misbehaving

    mike andrews Newbie

      hi folks,

      i found a bug in how jboss-3.2.3 is translating ejb-ql to sql for the Hypersonic database. actually, i think its a more general problem since i usually use mysql with jboss, but was forced to try hypersonic because sql was bombing even more dramatically with mysql (jboss was basically forgetting to declare a table in a 'FROM' clause when doing a table join --- see another topic in this forum for that).

      the problem seems pretty clear: there are two beans, say 'AAA' and 'BBB'. the two have a bidirectional one-to-one relationship, and the foreign key is on BBB's table. my beans actually have other names, but i replaced them with AAA and BBB for clarity here.

      there are two finder methods, one which finds all AAA beans that have a relationship to a BBB bean, and one that finds AAA beans with no relationship to a BBB bean --- here's the ejbql:

      findAllLocalAAAWithoutBBB(): SELECT OBJECT(c) FROM AAA AS c WHERE c.BBB IS NULL

      findAllLocalAAAWithBBB(): SELECT OBJECT(c) FROM AAA AS c WHERE c.BBB is NOT NULL

      now here's what jboss does --- the same thing for both! AAA and BBB each have a primary key field 'id', and fk_AAA is the foreign key in BBB's table:

      2004-03-07 16:13:22,390 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.AAA#findAllLocalAAAWithoutBBB] Executing SQL: SELECT t0_c.id FROM AAA t0_c WHERE ( NOT EXISTS (SELECT t1_c_BBB.id FROM BBB t1_c_BBB WHERE t0_c.id=t1_c_BBB.fk_AAA))

      2004-03-07 16:13:22,312 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.AAA#findAllLocalAAAWithBBB] Executing SQL: SELECT t0_c.id FROM AAA t0_c WHERE ( NOT EXISTS (SELECT t1_c_BBB.id FROM BBB t1_c_BBB WHERE t0_c.id=t1_c_BBB.fk_AAA))

      i'm pretty sure its a bug since jboss should at least be creating different sql for the different ejbql. seems like a pretty serious bug too since i'm not really doing anything all that special, but rather doing some basic CMR. unless i'm doing something silly here and not realizing it, i'm guessing that there may not be automated junit tests to test this sort of thing? a simple test would be to set up a network of CMR-related entity beans, and make sure a wide variety of finder methods all return the right sets of beans.

      BTW, the CMR fields themselves work fine, its just finder methods like the ones above which don't get expressed in sql correctly.

      mike