Bugs item #783453, was opened at 2003-08-05 14:46 Message generated for change (Comment added) made by loubyansky You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=783453&group_id=22866
Category: JBossCMP Group: v3.2 >Status: Closed >Resolution: Fixed Priority: 5 Submitted By: Paul W. Ward (pward) Assigned to: Alexey Loubyansky (loubyansky) Summary: Error in jbossQL -> SQL conversion Initial Comment: 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 (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)) ---------------------------------------------------------------------- >Comment By: Alexey Loubyansky (loubyansky) Date: 2003-10-06 21:20 Message: Logged In: YES user_id=543482 Fixed in 3.2.2RC5. Thanks! ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=783453&group_id=22866 ------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-Development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
