[jira] [Created] (OPENJPA-2539) JPQL interpret wrongly for inner join table (without mapped relation)

["Benson So (JIRA)" <ji...@apache.org>]

Benson So created OPENJPA-2539:
----------------------------------

             Summary: JPQL interpret wrongly for inner join table (without mapped relation)
                 Key: OPENJPA-2539
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2539
             Project: OpenJPA
          Issue Type: Bug
          Components: jpa
    Affects Versions: 2.1.0
         Environment: WebSphere v8.0
            Reporter: Benson So


I found the following issue after a few months of production of my client.

I write a java batch job to process some account data for my client. 

And I found out that the JPQL interpret wrong randomly and it is not always having the same result for the same of data and code.

JPA statement:
SELECT B
FROM EntityA_PO A, EntityB_PO B  
WHERE A.deletingDateTime IS NULL and A.statusCode= :app and A.someId in (:ids)  
and B.deletingDateTime IS NULL and B.statusCode= :app2 and A.headerId = B.headerId  
ORDER BY B.someId


translated to unexpected SQL:

SELECT t1.HEADER_ID, t1.SOME_ID 
FROM EntityB t0 JOIN EntityB t1 ON (1 = 1) 
WHERE (t0.DELETING_DATE_TIME IS NULL AND t0.STATUS_CODE = ? AND t0.SOME_ID IN (?)
AND t1.DELETING_DATE_TIME IS NULL AND t1.STATUS_CODE = ? AND t1.HEADER_ID = t0.HEADER_ID) 
ORDER BY t1.SOME_ID ASC


the correct translated SQL:

SELECT t1.HEADER_ID, t1.SOME_ID 
FROM EntityA t0 JOIN EntityB t1 ON (1 = 1) 
WHERE (t0.DELETING_DATE_TIME IS NULL AND t0.STATUS_CODE = ? AND t0.SOME_ID IN (?) 
AND t1.DELETING_DATE_TIME IS NULL AND t1.STATUS_CODE = ? AND t1.HEADER_ID = t0.HEADER_ID) 
ORDER BY t1.SOME_ID ASC 

Detail:
The two EntityA_PO and EntityB_PO do not have any mapped relation. They are joined by a column called headerId in where clause only. 

The problem and correct generated native SQL is different by one is to=Entity A , and other is to=Entity B.

I don't know why the wrong one joining Entity B itself without any Entity A involvement. This situation seems to be existed randomly. I can't catch the pattern of it.

If there is any fix or workaround for this case, please let me know. Thanks



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)