[
https://issues.apache.org/jira/browse/OPENJPA-2539?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Benson So updated OPENJPA-2539:
-------------------------------
Component/s: (was: jpa)
sql
Description:
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
was:
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
> 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
>
> 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)
