db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (Commented) (JIRA)" <j...@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.
Date Fri, 18 Nov 2011 10:10:51 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5501?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13152760#comment-13152760
] 

Knut Anders Hatlen commented on DERBY-5501:
-------------------------------------------

Derby currently implements SQL Feature E061-08, EXISTS PREDICATE,
which only allows an <asterisk> or a single <derived column>. There is
also Feature T501, Enhanced EXISTS predicate, which allows multiple
columns, but that feature is not supported by Derby yet.

SQL:2003, part 2, 8.9 <exists predicate>:

,----
| Conformance Rules
| 
| 1) Without Feature T501, “Enhanced EXISTS predicate”, conforming SQL
| language shall not contain an <exists predicate> that simply contains
| a <table subquery> in which the <select list> of a <query
| specification> directly contained in the <table subquery> does not
| comprise either an <asterisk> or a single <derived column>.
`----

So it seems to me that the changes proposed here are allowed by the
SQL standard.

The patch may need to make some changes to the
verifySelectStarSubquery() and setResultToBooleanTrueNode() overrides
too, as those methods assume that there's only one result column right
now (they only work on resultColumns.elementAt(0)).
                
> 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
>         Attachments: derby-5501-1.diff, derby-5501-1.stat, 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

       

Mime
View raw message