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

["Heath Thomann (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OPENJPA-2539?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Heath Thomann resolved OPENJPA-2539.
------------------------------------
       Resolution: Fixed
    Fix Version/s: 2.4.1
                   2.2.3
                   2.2.1.1
                   2.1.2

> 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: sql
>    Affects Versions: 2.1.0
>         Environment: WebSphere v8.0
>            Reporter: Benson So
>            Assignee: Heath Thomann
>            Priority: Critical
>             Fix For: 2.1.2, 2.2.1.1, 2.2.3, 2.4.1
>
>         Attachments: OPENJPA-2539-2.1.x.test, OPENJPA-2539-2.1.x_v2.test, OPENJPA-2539-2.1.x_v3.test, schema_and_testing_sql.txt
>
>
> 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.
> JPQL 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)