Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 17 Nov 2011 23:37:51 +0000 (UTC)
From: "Dag H. Wanvik (Updated) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1538223465.40856.1321573071746.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <1408627039.27229.1321290591561.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Updated] (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.atlassia=
n.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-5501:
---------------------------------

    Attachment: derby-5501-1.stat
                derby-5501-1.diff

Uploading a patch which lifts this restriction for EXISTS/NOT EXISTS and ad=
ds a test fixture to NestedWhereSubqueryTest. I haven't thought through the=
 ramifications about allowing this, but right now I can't see why lifting t=
his restriction would cause any problems: internally the select list is rew=
ritten to a "SELECT TRUE FROM.." in any case. Interestingly, the case "sele=
ct *" is already allowed, cf. the tests added.

Running regressions.
               =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.8.2.2
>         Environment: Max/Linux
>            Reporter: Sandeep Dixit
>         Attachments: derby-5501-1.diff, derby-5501-1.stat, derby-5501-rep=
ro.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