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

COUNT DISTINCT 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: Hibernate Core Issue Type: Bug Affects Versions: 3.5.1 Reporter: Bob Tiernay Assignee: Gail Badner Fix For: 3.5.2, 3.6.0.Beta1 Attachments: HHH-3096.ZIP @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