0 Replies Latest reply on May 17, 2003 6:00 PM by Sverker Abrahamsson

    Problem with OR in query with relations

    Sverker Abrahamsson Novice

      I have a EJB query that spans over 4 different entity beans. The query is (I've reformated it to make it more readable)

      SELECT DISTINCT OBJECT(cost)
      FROM CostEntity cost
      WHERE cost.smscAccount.id=?1 AND
      (cost.country.id=?2 OR cost.carrier.country.id=?2)

      The issue is that it contains a OR spanning over two different entities.

      The resulting query sent to the database (MySQL) is as follows:

      SELECT DISTINCT t0_cost.Id
      FROM cost t0_cost,
      carrier t4_cost_carrier,
      country t3_cost_carrier_country,
      smscaccount t1_cost_smscAccount,
      country t2_cost_country
      WHERE (t1_cost_smscAccount.Id = 1 AND
      (t2_cost_country.Id = 3 OR
      t3_cost_carrier_country.Id = 3)) AND
      (t0_cost.Carrier_FK=t4_cost_carrier.Id AND
      t4_cost_carrier.Country_FK=t3_cost_carrier_country.Id AND
      t0_cost.SmscAccount_FK=t1_cost_smscAccount.Id AND t0_cost.Country_FK=t2_cost_country.Id)

      The problem is in the part with the relation foreign keys are since all are connected with AND's, no OR's that reflect the original question.

      This is obviously a bug so what I'm looking for is a pointer of where to search in the source to solve it.
      /Sverker