OpenJPA
  1. OpenJPA
  2. OPENJPA-1483

count (Distinct e) in JPQL gives wrong result when the id field is a compound primary key

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0-beta
    • Fix Version/s: 1.2.3, 1.3.0, 2.0.0-beta2
    • Component/s: None
    • Labels:
      None
    • Patch Info:
      Patch Available

      Description

      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.

      1. OPENJPA-1483.patch
        13 kB
        Fay Wang
      2. OPENJPA-1483-1.2.x.patch.txt
        26 kB
        Heath Thomann
      3. OPENJPA-1483-1.3.x.patch.txt
        25 kB
        Heath Thomann
      4. OPENJPA-1483-2.patch
        17 kB
        Fay Wang

        Activity

        No work has yet been logged on this issue.

          People

          • Assignee:
            Unassigned
            Reporter:
            Fay Wang
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development