Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Sat, 19 Nov 2011 01:03:51 +0000 (UTC)
From: "Dag H. Wanvik (Updated) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1958466362.45932.1321664631120.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-2.diff
                derby-5501-2.diff

Uploading version 2 of the patch. I adjusted the logic in verifySelectStarS=
ubquery and setResultToBooleanTrueNode, although I wasn't able to make any =
example fail *without* adjusting them.

1) If the test in verifySelectStarSubquery which throws SQLState.LANG_EXPOS=
ED_NAME_NOT_FOUND is not made, the error will be caught later:

Caused by: ERROR 42X10: 'FOO' is not an exposed table name in the scope in =
which it appears.
=09at org.apache.derby.iapi.error.StandardException.newException(StandardEx=
ception.java:278)
=09at org.apache.derby.impl.sql.compile.FromList.expandAll(FromList.java:51=
4)
=09at org.apache.derby.impl.sql.compile.ResultColumnList.expandAllsAndNameC=
olumns(ResultColumnList.java:1737)
=09at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Re=
sultColumnList.java:825)
=09at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNo=
de.java:555)
=09at org.apache.derby.impl.sql.compile.SelectNode.bindTargetExpressions(Se=
lectNode.java:734)
=09at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(Subquer=
yNode.java:532)

so the check may be redundant. But I did adjust it so it catches the follow=
ing bad in column 2 in the check in verifySelectStarSubquery (foo is bogus)=
:

select i from t5501b t1 where not exists (select t2.*,foo.* from t5501a t2 =
where t1.i=3Dt2.i)

2) As for setResultToBooleanTrueNode, i adjusted it so the following would =
now (also) be rewritten:

   select true, .. from ..  -> select true from ..

although not doing so didn't seem to matter.

3) I also adjusted a negative test case in lang/subquery.sql to becoming a =
positive one.=20

Rerunning 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
>            Assignee: Dag H. Wanvik
>         Attachments: derby-5501-1.diff, derby-5501-1.stat, derby-5501-2.d=
iff, derby-5501-2.diff, derby-5501-repro.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