[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3096) Count operator with idenfication variable w/ composite primary key produces bad sql

[Bob Tiernay (JIRA)]

Count operator with idenfication variable w/ composite primary key produces bad sql 
------------------------------------------------------------------------------------

                 Key: HHH-3096
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3096
             Project: Hibernate3
          Issue Type: Bug
            Reporter: Bob Tiernay


@EmbeddedId
   private Id id;

	@Embeddable
	public static class Id implements Serializable {
		private static final long serialVersionUID = 6475618094377929936L;

		@Column(name="TG_ID", nullable = false)
		private Integer groupId;

		@Column(name="MBR_ID", nullable = false)
	    private Integer memberId;

        public Id() {
        	// Empty
        }

	    public Id(Integer groupId, Integer memberId) {
	    	super();

	    	this.groupId = groupId;
	    	this.memberId = memberId;
	    }

	    @Override
		public boolean equals(Object other) {
			if (other == this) {
				return true;
			} else if (other instanceof Id) {
				return groupId.equals(((Id) other).groupId)
						&& memberId.equals(((Id) other).memberId);
			}
			return false;
		}
	    
		@Override
		public int hashCode() {
			return groupId.hashCode() ^ memberId.hashCode();
		}		    
	}

@NamedQuery(name = "GroupMember.getCountByGroup", query = "SELECT COUNT(gm) FROM GroupMember gm WHERE gm.id.groupId = :groupId")


produces 

Hibernate: /* named HQL query GroupMember.getCountByGroup */ select count((groupmembe0_.TG_ID, groupmembe0_.MBR_ID)) as col_0_0_ from GROUP_MEMBER groupmembe0_ where groupmembe0_.TG_ID=?

and the following hibernate exception

Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis
 
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
        at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
        at org.hibernate.loader.Loader.doQuery(Loader.java:674)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        at org.hibernate.loader

Which is consistent with what Oracle produces by manual query execution.  The problem is the extra set of parenthesis around the composite primary key columns which appear to be invalid (at least for Oracle)

The recommend fix would be to use COUNT(*) or count(groupmembe0_.TG_ID, groupmembe0_.MBR_ID) in yhis case

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