Bugs item #912827, was opened at 2004-03-09 10:53 Message generated for change (Tracker Item Submitted) made by Item Submitter You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=912827&group_id=22866
Category: JBossCMP Group: v3.2 Status: Open Resolution: None Priority: 5 Submitted By: mike andrews (xoba) Assigned to: Nobody/Anonymous (nobody) Summary: CMR finder bug Initial Comment: 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. 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 ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=912827&group_id=22866 ------------------------------------------------------- This SF.Net email is sponsored by: IBM Linux Tutorials Free Linux tutorial presented by Daniel Robbins, President and CEO of GenToo technologies. Learn everything from fundamentals to system administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click _______________________________________________ JBoss-Development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
