On Friday 24 October 2003 10:50, Alexey Loubyansky wrote: Hi,
> as for me, 3.0.8 is wrong and 3.2.2 is correct. Interesting, especially as another app server (Borland's) generates the same SQL as 3.0.8 > Since you use OR between the terms > > > o.contact.surnameUpper = ?2 OR o.vehicle.registrationNumber = ?3 > > I think, the join should be done per the term, i.e. this way: > > (contact.SURNAME_UPPER = ? AND o.CONTACT_ID=contact.ID) > OR (vehicle.REGNUMBER = ? AND o.VEHICLE_ID=vehicle.ID) The problem with this, is the join is only done if the parameter matched, if not it would return all all records from the detail table due to short circuit evaluation. > > and not > > (contact.SURNAME_UPPER = ? OR vehicle.REGNUMBER = ?) > AND (o.VEHICLE_ID=vehicle.ID AND o.CONTACT_ID=contact.ID) This is correct, IMO, as the join is always done, regardless of whether the parameter matches or not. > If you want the SQL that was generated in 3.0.8, you should use the > following EJB-QL query: > > SELECT OBJECT(o) > FROM BaseTask AS o > WHERE > o.callID = ?1 OR > o.orderNumber = ?4 OR > (o.contact.surnameUpper = ?2 OR > o.vehicle.registrationNumber = ?3 OR > o.address.postCode = ?5) > AND o.contact IS NOT NULL > AND o.vehicle IS NOT NULL > AND o.address IS NOT NULL > > SELECT > o.CALL_ID > FROM > TASK_BASE o, > TASKS_PART_VEHICLE vehicle, > TASKS_PART_CONTACT contact, > TASKS_PART_ADDRESS address > > WHERE > (o.CALL_ID=?) OR > (o.ORDER_NUM=?) OR > ( > (contact.SURNAME_UPPER = ? OR > vehicle.REGNUMBER = ? OR > address.POSTCODE = ?) > AND > o.VEHICLE_ID=vehicle.ID > AND > o.CONTACT_ID=contact.ID > AND > o.ADDRESS_ID=address.ID > AND > o.VEHICLE_ID IS NOT NULL > AND > o.CONTACT_ID IS NOT NULL > AND > o.ADDRESS_ID IS NOT NULL) Which is still wrong as it brings back all the records from the detail tables that don't match the 'id' in the master. Phil -- 5:19pm up 48 days, 19:29, 1 user, load average: 0.20, 0.13, 0.10 ICQ: 760757 | AIM: pjshrimpton | Y!: pjshrimpton | [EMAIL PROTECTED] ------------------------------------------------------- This SF.net email is sponsored by: The SF.net Donation Program. Do you like what SourceForge.net is doing for the Open Source Community? Make a contribution, and help us add new features and functionality. Click here: http://sourceforge.net/donate/ _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
