3 Replies Latest reply on Jan 29, 2003 4:30 AM by Christoph Henrici

    Problems with CMR fields and "IS NULL" keyword

    Vesa Varimo Newbie

      I have following EJB-QL:

      SELECT OBJECT( t ) FROM Phone t
      WHERE t.status = ?1 AND t.phoneType IS NULL

      phoneType is a n:1 CMR field. (Phone may have only one phone type, but PhoneTypes can be attached to multiple phones).

      JBoss 3.0.5 RC 2 build: 20030102 creates following SQL query:

      SELECT t0_t.personId FROM PERSON t0_t WHERE
      (t0_t.status = ? AND t0_t.phoneTypeId IS NULL)
      AND (t0_t.phoneTypeId=t0_t.phoneTypeId)

      The last condition is always false, if phoneTypeId is null, because one can't use '=' comparison with NULL values in SQL. Why JBoss creates this condition, it makes no sense to me...?

      I'm using Oracle 9.0.1.