OpenJPA
  1. OpenJPA
  2. OPENJPA-2289

Additional SQL alias generated for query with subquery causes incorrect # of rows returned - Oracle only

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.1, 2.1.1, 2.2.0, 2.3.0
    • Fix Version/s: 2.0.3, 2.1.2, 2.2.2, 2.2.1.1, 2.3.0
    • Component/s: sql
    • Labels:
      None
    • Patch Info:
      Patch Available

      Description

      .createQuery("SELECT e FROM MaxQueryEntity e, MaxQueryMapEntity map "
      + "WHERE map.selectCriteria = 'B3' AND map.refEntity = e "
      + " AND e.revision = ( SELECT MAX(e_.revision)"
      + " FROM MaxQueryEntity e_"
      + " WHERE e_.domainId = e.domainId )"
      + " AND map.revision = ( SELECT MAX(map_.revision)"
      + " FROM MaxQueryMapEntity map_"
      + " WHERE map_.refEntity = map.refEntity )");

      On Oracle we generate SQL like this on 2.0.x+:

      SELECT t1.id, t1.domainId, t1.revision FROM OPENJPA_MAXQUERY_MAPENTITY t0, OPENJPA_MAXQUERY_ENTITY t1, OPENJPA_MAXQUERY_MAPENTITY t4 WHERE (t0.selectCriteria = ? AND t0.refEntity = t1.id AND t1.revision = (SELECT MAX(t2.revision) FROM OPENJPA_MAXQUERY_ENTITY t2 WHERE (t2.domainId = t1.domainId)) AND t0.revision = (SELECT MAX(t3.revision) FROM OPENJPA_MAXQUERY_MAPENTITY t3 WHERE (t3.refEntity = t4.refEntity))) [params=(String) B3]

      The additional alias "OPENJPA_MAXQUERY_MAPENTITY t4" caused more unexpected rows to return.

        Activity

        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Resolved Resolved
        160d 20h 10m 1 Albert Lee 10/Apr/13 15:51
        Resolved Resolved Closed Closed
        6s 1 Albert Lee 10/Apr/13 15:51
        Albert Lee made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Albert Lee made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Albert Lee made changes -
        Fix Version/s 2.0.3 [ 12317143 ]
        Fix Version/s 2.1.2 [ 12317142 ]
        Fix Version/s 2.3.0 [ 12319463 ]
        Fix Version/s 2.2.2 [ 12323356 ]
        Fix Version/s 2.2.1.1 [ 12323484 ]
        Albert Lee made changes -
        Patch Info Patch Available [ 10042 ]
        Albert Lee made changes -
        Attachment OPENJPA-2289.20x.patch [ 12551610 ]
        Albert Lee made changes -
        Attachment oracle.alias.20x.patch [ 12551609 ]
        Albert Lee made changes -
        Field Original Value New Value
        Attachment oracle.alias.20x.patch [ 12551609 ]
        Albert Lee created issue -

          People

          • Assignee:
            Albert Lee
            Reporter:
            Albert Lee
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development