[
https://issues.apache.org/jira/browse/DERBY-5501?page=com.atlassian.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
verifySelectStarSubquery 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_EXPOSED_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.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:278)
at
org.apache.derby.impl.sql.compile.FromList.expandAll(FromList.java:514)
at
org.apache.derby.impl.sql.compile.ResultColumnList.expandAllsAndNameColumns(ResultColumnList.java:1737)
at
org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(ResultColumnList.java:825)
at
org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:555)
at
org.apache.derby.impl.sql.compile.SelectNode.bindTargetExpressions(SelectNode.java:734)
at
org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(SubqueryNode.java:532)
so the check may be redundant. But I did adjust it so it catches the following
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=t2.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.
Rerunning regressions.
> 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.
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 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.diff,
> 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 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
