Bugs item #783453, was opened at 2003-08-05 06:46 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=783453&group_id=22866
Category: JBossCMP Group: v3.2 Status: Open Resolution: None Priority: 5 Submitted By: Paul W. Ward (pward) Assigned to: Nobody/Anonymous (nobody) 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)) ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=783453&group_id=22866 ------------------------------------------------------- This SF.Net email sponsored by: Free pre-built ASP.NET sites including Data Reports, E-commerce, Portals, and Forums are available now. Download today and enter to win an XBOX or Visual Studio .NET. http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01 _______________________________________________ JBoss-Development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
