[jira] [Updated] (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 updated OPENJPA-2539:
-----------------------------------
    Attachment: OPENJPA-2539-2.1.x.test
                schema_and_testing_sql.txt

Hey Rick!  Benson sent to me a "test" by other means than this JIRA.  I've ran his test as-is (a simply JSE test packaged with necessary jars).  I was NOT able to recreate the issue described on my Windows machine.  As I understand it, Benson is not able to recreate on Windows either, rather he can occasionally recreate on AIX.  I've taken Benson's test and put it into a standard OpenJPA test which you can apply.  I'm also including an SQL script which you can run to set up the tables and necessary data.  I've ran the SQL against my DB2.  This is what Benson is using as well.  

Benson - can you verify that you can only recreate on AIX??

Thanks,

Heath

> 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
>            Priority: Critical
>         Attachments: OPENJPA-2539-2.1.x.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)