0 Replies Latest reply on Mar 9, 2004 10:18 AM by mike andrews

    CMR finder bug

    mike andrews Newbie

      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