CMR finder bug
hi folks,
i think i found a jboss bug (3.2.4RC1) which basically renders my
application dead in the water, and it relates to something pretty
fundamental in ejb-ql CMP/CMR and finder methods. i also posted a
bug report on sourceforge on this.
its basically the next step after my previous topics 'ejbql finder
misbehaving' and 'ejbql finder bug?', if folks want to refer to those
for a little background.
let me start by saying that my application now works fine under
Hypersonic database (which supports sql subqueries) after the most
recent bugfix yesterday. now, however, its (still) broken in for
mysql, which doesn't support subqueries, and i'll explain why.
folks may want to refer to the package http://xoba.com/debug-4.zip,
which contains a debugging application consisting solely of two
CMP/CMR entity beans, and a simple client that creates a few related
beans, queries them with finder methods, and thus clearly illustrates
this bug. but the following description presented here is enough to
identify the bug without referring to that package:
one bean is called 'First', and it is in a bidirectional one-to-one
relationship with another bean called 'Third'. the foreign key in the
database is in the table for the 'Third' bean.
the two finder methods, each with jboss's sql implementation, are:
1. findAllFirstsWithoutThirds(): SELECT OBJECT(c) FROM First AS c
WHERE c.third IS NULL [jboss' sql implementation: SELECT DISTINCT
t0_c.x FROM First t0_c, Third t1_c_third WHERE (t1_c_third.z IS NULL
AND t0_c.x=t1_c_third.fk_first)]
2. findAllFirstsWithThirds(): SELECT OBJECT(c) FROM First AS c WHERE
c.third is NOT NULL [jboss' sql implementation: SELECT DISTINCT t0_c.x
FROM First t0_c, Third t1_c_third WHERE (t1_c_third.z IS NOT NULL AND
t0_c.x=t1_c_third.fk_first)]
and jboss gets the sql implementation of #1 incorrectly ---
findAllFirstsWithoutThirds() --- since First beans which are not in a
relationship with Third beans are *not* represented in the database by
Third beans having a null primary key (which is what the sql implies).
thus, the proper sql implementation would be something like:
SELECT DISTINCT t0_c.x FROM first AS t0_c LEFT JOIN third AS
t1_c_third ON t0_c.x=t1_c_third.fk_first WHERE (t1_c_third.z IS NULL)
since we rather want to see all First beans where there are no
corresponding Third beans --- clearly, a 'left join' or something
similar is called for here; a simple table join will ignore First
beans with no relationship to Third beans, which is *precisely* what
findAllFirstsWithoutThirds() is hoping to find. 'NULL' in the sql with
the 'LEFT JOIN' does not mean the primary key of a Third bean is null,
but rather it means there is no Third bean related to a given First
bean.
since this bug causes jboss to incorrectly implement certain common
types of finder methods involving CMR's, i think its a pretty urgent
one to fix. so please let me know if i can help in any way to speed
the process along! if it can be fixed quickly, i'll just stand by;
otherwise, i may need to start refactoring the parts of my application
that depend on the correct functioning of ejb-ql finder methods like
this, when using databases like mysql with no subquery support.
BTW, finder method #2 --- findAllFirstsWithThirds() --- is a bit
suspect too, because although it gets the right answer, 't1_c_third.z'
will never be NULL because its the primary key of the Third bean.
thanks, mike