[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5998) Illegal parenthesis in generated Oracle SQL SELECT clause (when using subselect)

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).