Re: [JBoss-user] 3.2.2 : Incorrect SQL generation

Alexey Loubyansky Fri, 24 Oct 2003 16:23:56 -0700

Hello Phil,

as for me, 3.0.8 is wrong and 3.2.2 is correct.

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)

and not

(contact.SURNAME_UPPER = ? OR vehicle.REGNUMBER = ?)
AND (o.VEHICLE_ID=vehicle.ID AND o.CONTACT_ID=contact.ID)

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

This will result an SQL query like:

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)

Though, IS NOT NULL are redundant it is a correct translation.

alex

Phil Shrimpton wrote:

Hi,

Consider this EJB-QL..

SELECT OBJECT(o) FROM BaseTask AS o WHERE o.callID = ?1 OR
o.contact.surnameUpper = ?2 OR o.vehicle.registrationNumber = ?3 OR
o.orderNumber = ?4 OR o.address.postCode = ?5

In Jboss 3.0.8, the following SQL is generated...

SELECT t0_o.CALL_ID
FROM TASKS_BASE t0_o, TASKS_PART_VEHICLE t2_o_vehicle, TASKS_PART_CONTACT
t1_o_contact, TASKS_PART_ADDRESS t3_o_address
WHERE
 (t0_o.CALL_ID = ? OR t1_o_contact.SURNAME_UPPER = ? OR
t2_o_vehicle.REGNUMBER = ? OR t0_o.ORDER_NUM = ? OR t3_o_address.POSTCODE =
?)
 AND (t0_o.VEHICLE_ID=t2_o_vehicle.ID AND t0_o.CONTACT_ID=t1_o_contact.ID
AND t0_o.ADDRESS_ID=t3_o_address.ID)

..which is correct, but Jboss 3.2.2 generates the following SQL...

SELECT t0_o.CALL_ID
FROM TASKS_BASE t0_o, TASKS_PART_VEHICLE t4_o_vehicle, TASKS_PART_CONTACT
t2_o_contact, TASKS_PART_ADDRESS t7_o_address
WHERE (t0_o.CALL_ID = ?)
OR (t2_o_contact.SURNAME_UPPER = ? AND t0_o.CONTACT_ID=t2_o_contact.ID)
OR (t4_o_vehicle.REGNUMBER = ? AND  t0_o.VEHICLE_ID=t4_o_vehicle.ID)
OR (t0_o.ORDER_NUM = ?)
OR (t7_o_address.POSTCODE = ? AND  t0_o.ADDRESS_ID=t7_o_address.ID)

..which is incorrect, and returns incorrect results, due to not 'joining'
the tables correctly.

I can't get on to SF to see if this has been reported.

Phil


-------------------------------------------------------
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

Re: [JBoss-user] 3.2.2 : Incorrect SQL generation

Reply via email to