1 Reply Latest reply on Aug 1, 2003 5:31 AM by pward

    Error in jbossQL -> SQL conversion

    pward Newbie

      This is on both 3.2.1 and 3.2.2RC2 using the MS SQL Server 2000 mapping. The task entity has a many-to-one relationship with a print schedule entity. ID is the name of the PK field for both entities. For some reason the jbossQL -> SQL conversion appears to be using inner joins when all relationships in jbossCMP are required to allow null values.

      Hopefully I'm just missing something here because I'm starting to get flat forehead syndrome from beating my head against a wall. I've started trying to use a custom finder, but I'm having problems doing this with the MS SQL driver (another story) and didn't really want to use the non-compliant custom finder feature in the first place.

      Any help would be greatly appreciated. My only alternative at this point is to drag most of the tasks across the network and compare them locally. There are potentially tens of thousands of records in this set and I was hoping to keep the selection process happening on the db server.

      What I send to ejbSelectGeneric
      -------------------------------
      SELECT OBJECT(t)
      FROM task t
      WHERE (t.printSchedule IS NULL) OR
      ((t.printSchedule.iD = ?1) AND (t.modifyDate <= ?2)) OR
      ((t.printSchedule.iD = ?3) AND (t.modifyDate <= ?4)) OR
      ((t.printSchedule.iD = ?5) AND (t.modifyDate <= ?6))

      What gets executed
      ------------------
      SELECT t0_t.ID
      ... Other Task Fields ...
      FROM TASK t0_t
      ,PRINT_SCHEDULE t1_t_printSchedule
      WHERE ((t1_t_printSchedule IS NULL) OR
      ((t1_t_printSchedule.ID = ?) AND (t0_t.MODIFY_DATE <= ?)) OR
      ((t1_t_printSchedule.ID = ?) AND (t0_t.MODIFY_DATE <= ?)) OR
      ((t1_t_printSchedule.ID = ?) AND (t0_t.MODIFY_DATE <= ?))) AND
      (t0_t.PRINT_SCHEDULE_ID=t1_t_printSchedule.ID)


      What I hope to be executed
      --------------------------
      SELECT t0_t.ID
      ... Other Task Fields ...
      FROM TASK t0_t
      WHERE (t1_t_printSchedule_iD IS NULL) OR
      ((t1_t_printSchedule_iD = ?) AND (t0_t.MODIFY_DATE <= ?)) OR
      ((t1_t_printSchedule_iD = ?) AND (t0_t.MODIFY_DATE <= ?)) OR
      ((t1_t_printSchedule_iD = ?) AND (t0_t.MODIFY_DATE <= ?))

      Not ideal but acceptable since foreign keys are always nullable in JBossCMP
      ---------------------------------------------------------------------------
      SELECT t0_t.ID
      ... Other Task Fields ...
      FROM TASK t0_t
      WHERE (((t1_t_printSchedule IS NULL) OR
      ((t1_t_printSchedule.ID = ?) AND (t0_t.MODIFY_DATE <= ?)) OR
      ((t1_t_printSchedule.ID = ?) AND (t0_t.MODIFY_DATE <= ?)) OR
      ((t1_t_printSchedule.ID = ?) AND (t0_t.MODIFY_DATE <= ?)))) AND
      ((t1_t_printSchedule.ID IS NULL) OR (t0_t.PRINT_SCHEDULE_ID=t1_t_printSchedule.ID))

        • 1. Re: Error in jbossQL -> SQL conversion
          pward Newbie

          Oops, managed to bork a couple of the queries in my post.

          What I hope to be executed
          --------------------------
          SELECT t0_t.ID
          ... Other Task Fields ...
          FROM TASK t0_t
          WHERE (t0_t.print_schedule_id IS NULL) OR
          ((t0_t.print_schedule_id = ?1) AND (t0_t.MODIFY_DATE <= ?2)) OR
          ((t0_t.print_schedule_id = ?3) AND (t0_t.MODIFY_DATE <= ?4)) OR
          ((t0_t.print_schedule_id = ?5) AND (t0_t.MODIFY_DATE <= ?6))


          Not ideal but acceptable since foreign keys are always nullable in JBossCMP
          ---------------------------------------------------------------------------
          SELECT t0_t.ID
          ... Other Task Fields ...
          FROM TASK t0_t
          ,PRINT_SCHEDULE t1_t_printSchedule
          WHERE (((t1_t_printSchedule.ID IS NULL) OR
          ((t1_t_printSchedule.ID = ?1) AND (t0_t.MODIFY_DATE <= ?2)) OR
          ((t1_t_printSchedule.ID = ?3) AND (t0_t.MODIFY_DATE <= ?4)) OR
          ((t1_t_printSchedule.ID = ?5) AND (t0_t.MODIFY_DATE <= ?6)))) AND
          ((t1_t_printSchedule.ID IS NULL) OR (t0_t.PRINT_SCHEDULE_ID=t1_t_printSchedule.ID))