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

[Gail Badner (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=44158#comment-44158 ] 

Gail Badner commented on HHH-5998:
----------------------------------

Is this still an issue in 3.6.7?

> 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.
For more information on JIRA, see: http://www.atlassian.com/software/jira