[
https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Fay Wang updated OPENJPA-1483:
------------------------------
Attachment: OPENJPA-1483.patch
The patch will produce the following sql:
(1) "select count (distinct f1) from F1 f1",
SELECT COUNT(*) FROM (SELECT DISTINCT t0.F1PK, t0.F2PK FROM F1 t0 ) s
optimize for 1 row
(2) "select count (distinct f1.pk) from F1 f1",
SELECT COUNT(*) FROM (SELECT DISTINCT t0.F1PK, t0.F2PK FROM F1 t0 ) s
optimize for 1 row
(3)"SELECT COUNT (DISTINCT f1.g2) FROM F1 f1",
SELECT COUNT(*) FROM (SELECT DISTINCT t1.G1PK, t1.G2PK FROM F1 t0 INNER JOIN
G2 t1 ON t0.G1PK = t1.G1PK AND t0.G2PK = t1.G2PK ) s optimize for 1 row
(4) "SELECT COUNT (DISTINCT f1.g2.pk) FROM F1 f1",
SELECT COUNT(*) FROM (SELECT DISTINCT t1.G1PK, t1.G2PK FROM F1 t0 INNER JOIN
G2 t1 ON t0.G1PK = t1.G1PK AND t0.G2PK = t1.G2PK ) s optimize for 1 row
(5)"select count (f1) from F1 f1",
SELECT COUNT(*) FROM F1 t0 optimize for 1 row
(6)"select count (f1.pk) from F1 f1",
SELECT COUNT(*) FROM F1 t0 optimize for 1 row
(7) "SELECT COUNT (f1.g2) FROM F1 f1",
SELECT COUNT(*) FROM F1 t0 INNER JOIN G2 t1 ON t0.G1PK = t1.G1PK AND t0.G2PK =
t1.G2PK optimize for 1 row
(8)"SELECT COUNT (f1.g2.pk) FROM F1 f1",
SELECT COUNT(*) FROM F1 t0 INNER JOIN G2 t1 ON t0.G1PK = t1.G1PK AND t0.G2PK =
t1.G2PK optimize for 1 row
> count (Distinct e) in JPQL gives wrong result when the id field is a compound
> primary key
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-1483
> URL: https://issues.apache.org/jira/browse/OPENJPA-1483
> Project: OpenJPA
> Issue Type: Bug
> Affects Versions: 2.1.0
> Reporter: Fay Wang
> Fix For: 2.1.0
>
> Attachments: OPENJPA-1483.patch
>
>
> This is a fundamental problem with count when compound primary key is
> involved.
> (1) If no relation navigation is involved:
> String jpql = "SELECT COUNT (DISTINCT e) FROM G2 e";
> With the property below:
> <property name="openjpa.jdbc.DBDictionary"
> value="db2(useWildCardForCount=true)" />
> Openjpa will generate the following sql and return the correct count:
> SELECT COUNT(*) FROM G2 t0 optimize for 1 row
> (2) If there is relation navigation invloved:
> String jpql = "SELECT COUNT (DISTINCT f1.g2) FROM F1 f1";
>
> The property of useWildCardForCount will not generate correct sql with
> right result. However, given the object-relational impedance mismatch, there
> is no corresponding SQL construct for count of multiple primary keys, and
> there is no clean and generic solution to solve this problem. The only
> workaround is to use native SQL with table expression:
> SELECT COUNT(*)
> FROM (SELECT DISTINCT G1.G1PK, G1.G2PK FROM F1 t0 INNER JOIN G2 t1 ON
> t0.G1PK = t1.G1PK AND t0.G2PK = t1.G2PK)) TX;
> Rather than giving a wrong answer, OpenJPA should give an Unsupported
> exception.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
