8:02 a.m.
Illegal parenthesis in generated Oracle SQL SELECT clause (when using subselect)
--------------------------------------------------------------------------------
Key: HHH-5998
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5998
Project: Hibernate Core
Issue Type: Bug
Components: query-hql
Affects Versions: 3.6.0
Environment: Hibernate 3.6.0.Final (shipped as part of JBoss runtime environment
6), Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Reporter: Thomas Zangerl
Hi,
when using an HQL subselect with EXISTS where the selected object in the subselect is an
entity with a composite primary key (mapping a join-table), Hibernate adds parenthesis to
the query-list with the selected columns. Oracle does not accept SELECT statements with
parenthesis around the query-list and fails.
Consider the following entities (non-relevant members omitted):
{code:java}
@Entity
@Table(name = "DEMANDE_MODULES")
public class ModulesRequest implements Serializable {
/**
* generated serialVersionUID
*/
private static final long serialVersionUID = -9083049226182530936L;
private Long id;
@Id
@Column(name = "DMD_ID")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="demandeSeq")
@SequenceGenerator(name="demandeSeq", sequenceName="S_EVA_DMD_ID",
allocationSize = 1)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
}
{code}
{code:java}
@Entity
@Table(name = "EVALUATION")
public class Evaluation implements Serializable {
/**
* generated serialVersionUID
*/
private static final long serialVersionUID = -7258988774554733242L;
private Long id;
@Id
@Column(name = "EVA_ID")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator="evaluationSeq")
@SequenceGenerator(name="evaluationSeq", sequenceName="S_EVA_ID",
allocationSize = 1)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
}
{code}
{code:java}
@Entity
@Table(name = "DEMANDE_EVALUATION")
@AssociationOverrides({
@AssociationOverride(name = "pk.request", joinColumns=@JoinColumn(name =
"DMD_ID")),
@AssociationOverride(name = "pk.evaluation", joinColumns=@JoinColumn(name =
"EVA_ID"))
})
public class EvaluationRequest implements Serializable {
/**
* generated serialVersionUID
*/
private static final long serialVersionUID = -7511793462359852575L;
private EvaluationRequestId pk;
public EvaluationRequest() {
this.pk = new EvaluationRequestId();
}
@EmbeddedId
public EvaluationRequestId getPk() {
return pk;
}
public void setPk(EvaluationRequestId pk) {
this.pk = pk;
}
}
{code}
{code:java}
@Embeddable
public class EvaluationRequestId implements Serializable {
/**
* generated serialVersionUID
*/
private static final long serialVersionUID = -2708732283791960577L;
private ModulesRequest request;
private Evaluation evaluation;
public void setRequest(ModulesRequest request) {
this.request = request;
}
@ManyToOne
@ForeignKey(name="FK_DMD_EVA_DMD_EVALUATIONS")
public ModulesRequest getRequest() {
return request;
}
public void setEvaluation(Evaluation evaluation) {
this.evaluation = evaluation;
}
@ManyToOne
@ForeignKey(name="FK_DMD_EVA_EVALUATION")
public Evaluation getEvaluation() {
return evaluation;
}
}
{code}
EvaluationRequest is a join table between Evaluation and ModulesRequest, hence the primary
key is the composite of the primary keys of Evaluation and ModulesRequest.
If I now execute the following EJB-QL query
{code:java}
String testQuery = "SELECT request " +
"FROM ModulesRequest request " +
"WHERE EXISTS (" +
" SELECT evRequest " +
" FROM request.evaluationRequests evRequest) ";
Query testq = em.createQuery(testQuery);
testq.getResultList();
{code}
Hibernate generates the following SQL command from the above EJB-QL:
{code:sql}
select
modulesreq0_.DMD_ID as DMD1_145_,
modulesreq0_.COMMENTAIRE as COMMENTA2_145_,
modulesreq0_.SFP_ID as SFP3_145_,
modulesreq0_.SEM_ID as SEM4_145_
from
DEMANDE_MODULES modulesreq0_
where
exists (
select
(evaluation1_.EVA_ID,
evaluation1_.DMD_ID)
from
DEMANDE_EVALUATION evaluation1_
where
modulesreq0_.DMD_ID=evaluation1_.DMD_ID
)
{code}
This SQL fails in the second SELECT because Oracle does not accept parantheses in a select
statement:
{code}
14:42:41,706 ERROR [org.hibernate.util.JDBCExceptionReporter] ORA-00907: missing right
parenthesis
{code}
The parantheses do not get included if the EJBQL selects directly on EvalauationRequest
(and not as a part of a subselect in EXISTS).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira