ejbql finder misbehaving

[mikea-xoba]

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

[aloubyansky]

This is fixed. Thanks.

[aloubyansky]

in 3.2.4RC1 and 4.0.0DR3

[mikea-xoba]

thanks a million! i really appreciate it. now i'll go back to my original ejb-ql finder problem (earlier in this forum) and see what further progress i can make. BTW, i'm going to make an effort to understand the CMP part of the jboss code so i can be more helpful and productive if or when i do find further issues. mike.

[mikea-xoba]

one more quick question:

was it fixed yesterday or earlier, or just this morning? because a few minutes ago i downloaded a fresh jboss-3.2-snapshot from sourceforge, built it, and still saw the bug; namely, that for both ejb-ql phrases 'IS NULL' and 'IS NOT NULL' on my CMR's, jboss is executing the same 'NOT EXISTS' subquery for Hypersonic --- the same bug i reported at the start of this topic.

rather (and this is probably what the bugfix was), i believe it should use the sql 'NOT EXISTS' for 'IS NULL' ejb-ql, and 'EXISTS' (?) for 'IS NOT NULL'. or something like that. (i mainly use mysql, so i'm not too familiar with the 'EXISTS' construct yet).

so i'm guessing you fixed it this morning, which is why it didn't get into the jboss-3.2-snapshot which was produced at 2 am pacific time last night? just checking.

thanks again,
mike

[mikea-xoba]

... please disregard my last question --- i just checked out the latest jboss-3.2 and see the problem is solved there --- so it must have been fixed this morning. thanks again! mike