[JBoss-dev] [ jboss-Bugs-705732 ] Incorrect behaviour of WHERE clause using relations

[SourceForge.net <no...@so...>]

Bugs item #705732, was opened at 2003-03-18 17:43
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=705732&group_id=22866

Category: JBossCMP
Group: v3.2
Status: Open
Resolution: None
Priority: 5
Submitted By: David Calvente (davidcalvente)
Assigned to: Nobody/Anonymous (nobody)
Summary: Incorrect behaviour of WHERE clause using relations

Initial Comment:
Using this EJB-QL statement:

SELECT DISTINCT OBJECT(cli)
FROM customer AS cu
WHERE     (cu.companyKey = ?2 )
 AND (cu.city.stateKey = ?1 OR cu.city IS NULL)

The container generates an SQL statement like this:

SELECT DISTINCT (...some fields..)
FROM customerTable AS cu, cityTable as ct
WHERE     (cu.companyKey = '12') 

AND (ct.stateKey = '3' OR cu.cityKey IS NULL)
... here is the problem....
AND (cu.cityKey = ct.cityKey)

This sentence has a potential missmatch when cu.city 
(a CMR) is NULL due to the fact that JBossCMP 
allways post a final AND in the WHERE clause with all 
the table relations needed.

The correct statement should be:


SELECT DISTINCT  (...some fields..)
FROM customerTable AS cu, cityTable as ct
WHERE     (cu.companyKey = '12') 

AND ((ct.stateKey = '3'  AND ct.cityKey = cy.cityKey) 
OR (ct.cityKey IS NULL))




----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=705732&group_id=22866