Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-5501

Subquery is only allowed to return a single column - When using derby with hibernate (or JPA) queries are created per JPA spec. For tables with multi-column PK, subqueries are created with two columns in select clause.

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2
    • Fix Version/s: 10.9.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Max/Linux
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Seen in production

      Description

      ERROR: Subquery is only allowed to return a single column.
      PROBLEM: When using derby with hibernate (or JPA) queries are created by the JPA engine per JPA spec. For tables with multi-column PK, subqueries are created with two columns in select clause (see select colofassig6_.activityID, colofassig6_.assigneeID from Assignment in the query below).
      Without this support, I can not use Derby with JPA.

      Hibernate: select distinct activitybe0_.activityID as activityID69_, activitybe0_.createdBy as createdBy69_, activitybe0_.createdOn as createdOn69_, activitybe0_.lastModifiedBy as lastModi4_69_, activitybe0_.lastModifiedOn as lastModi5_69_, activitybe0_.activityDate as activity6_69_, activitybe0_.activityTypeHierarchyID as activity7_69_, activitybe0_.activityTypeID as activity8_69_, activitybe0_.campaignID as campaignID69_, activitybe0_.comments as comments69_, activitybe0_.description as descrip11_69_, activitybe0_.inputID as inputID69_, activitybe0_.inputTypeID as inputTy13_69_, activitybe0_.name as name69_, activitybe0_.notes as notes69_, activitybe0_.organizationID as organiz16_69_, activitybe0_.parentActivityTypeID as parentA17_69_ from Activity activitybe0_, Activity activitybe1_ inner join ActivitySchedule colofactiv2_ on activitybe1_.activityID=colofactiv2_.activityID inner join ActivityScheduleStatus colofactiv3_ on colofactiv2_.activityScheduleID=colofactiv3_.activityScheduleID inner join ActivityScheduleStatusType activitysc4_ on colofactiv3_.activityScheduleStatusTypeID=activitysc4_.activityScheduleStatusTypeID, ActivityTypeHierarchy activityty5_ where activitybe0_.activityTypeHierarchyID=activityty5_.activityTypeHierarchyID and activityty5_.activityTypeHierarchyID=? and not (exists (select colofassig6_.activityID, colofassig6_.assigneeID from Assignment colofassig6_ where activitybe0_.activityID=colofassig6_.activityID)) and (activitybe0_.activityID<>activitybe1_.activityID or activitysc4_.name<>'Route')
      2011-11-14 11:41:13,413 ERROR [org.hibernate.util.JDBCExceptionReporter] (EJB-Timer-1321288405420[target=jboss.j2ee:ear=oecrm1.6.3RC1-derby-jboss.ear,jar=builder-ejb.jar,name=WorkflowActivatorBean,service=EJB3]) Subquery is only allowed to return a single column.

        Attachments

        1. derby-5501-3.stat
          0.5 kB
          Dag H. Wanvik
        2. derby-5501-3.diff
          12 kB
          Dag H. Wanvik
        3. derby-5501-2.diff
          11 kB
          Dag H. Wanvik
        4. derby-5501-2.diff
          11 kB
          Dag H. Wanvik
        5. derby-5501-1.stat
          0.2 kB
          Dag H. Wanvik
        6. derby-5501-1.diff
          3 kB
          Dag H. Wanvik
        7. derby-5501-repro.diff
          1 kB
          Dag H. Wanvik

          Activity

            People

            • Assignee:
              dagw Dag H. Wanvik
              Reporter:
              sdixit@ohioedge.com Sandeep Dixit
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: