Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Tue, 22 Nov 2011 19:50:40 +0000 (UTC)
From: "Dag H. Wanvik (Commented) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1898545047.3634.1321991440327.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <1408627039.27229.1321290591561.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Commented] (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.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-5501?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1315=
5374#comment-13155374 ]=20

Dag H. Wanvik commented on DERBY-5501:
--------------------------------------

I'm on board with that, Knut. I discussed briefly with Rick offline if we m=
ight need a way to call out improvement fixes from the "noise" of errors fi=
xes in the release notes when they aren't quite new features, but we decide=
d to keep things as they are, so adding it to the feature list is fine, alt=
hough probably stretching the definition of feature. Still better than to c=
omplicate things further..
               =20
> Subquery is only allowed to return a single column - When using derby wit=
h hibernate (or JPA) queries are created per JPA spec. For tables with mult=
i-column PK, subqueries are created with two columns in select clause.
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------------------------------------------------------
>
>                 Key: DERBY-5501
>                 URL: https://issues.apache.org/jira/browse/DERBY-5501
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 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
>         Environment: Max/Linux
>            Reporter: Sandeep Dixit
>            Assignee: Dag H. Wanvik
>             Fix For: 10.9.0.0
>
>         Attachments: derby-5501-1.diff, derby-5501-1.stat, derby-5501-2.d=
iff, derby-5501-2.diff, derby-5501-3.diff, derby-5501-3.stat, derby-5501-re=
pro.diff
>
>
> 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 ar=
e created with two columns in select clause (see select colofassig6_.activi=
tyID, 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_, acti=
vitybe0_.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 ac=
tivity8_69_, activitybe0_.campaignID as campaignID69_, activitybe0_.comment=
s as comments69_, activitybe0_.description as descrip11_69_, activitybe0_.i=
nputID as inputID69_, activitybe0_.inputTypeID as inputTy13_69_, activitybe=
0_.name as name69_, activitybe0_.notes as notes69_, activitybe0_.organizati=
onID as organiz16_69_, activitybe0_.parentActivityTypeID as parentA17_69_ f=
rom Activity activitybe0_, Activity activitybe1_ inner join ActivitySchedul=
e colofactiv2_ on activitybe1_.activityID=3Dcolofactiv2_.activityID inner j=
oin ActivityScheduleStatus colofactiv3_ on colofactiv2_.activityScheduleID=
=3Dcolofactiv3_.activityScheduleID inner join ActivityScheduleStatusType ac=
tivitysc4_ on colofactiv3_.activityScheduleStatusTypeID=3Dactivitysc4_.acti=
vityScheduleStatusTypeID, ActivityTypeHierarchy activityty5_ where activity=
be0_.activityTypeHierarchyID=3Dactivityty5_.activityTypeHierarchyID and act=
ivityty5_.activityTypeHierarchyID=3D? and  not (exists (select colofassig6_=
.activityID, colofassig6_.assigneeID from Assignment colofassig6_ where act=
ivitybe0_.activityID=3Dcolofassig6_.activityID)) and (activitybe0_.activity=
ID<>activitybe1_.activityID or activitysc4_.name<>'Route')
> 2011-11-14 11:41:13,413 ERROR [org.hibernate.util.JDBCExceptionReporter] =
(EJB-Timer-1321288405420[target=3Djboss.j2ee:ear=3Doecrm1.6.3RC1-derby-jbos=
s.ear,jar=3Dbuilder-ejb.jar,name=3DWorkflowActivatorBean,service=3DEJB3]) S=
ubquery is only allowed to return a single column.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrato=
rs: https://issues.apache.org/jira/secure/ContactAdministrators!default.jsp=
a
For more information on JIRA, see: http://www.atlassian.com/software/jira