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

[Thomas Zangerl (JIRA)]

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