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

[Milosz Hulboj (JIRA)]

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

Milosz Hulboj commented on HHH-3096:
------------------------------------

I'd also like to know if this issue has really been fixed. I've tried 3.5.2 and 3.6.0.Beta2 and when I try to generate the count (not count distinct) query with Oracle10g dialect I get the following SQL which fails:

select * from ( select count((associatio0_.mac, associatio0_.ts)) as col_0_0_ from stats_association_records associatio0_ ) where rownum <= ?

Names are different, but the idea is the same - there is the composite primary key (mac, ts).

Could anyone comment on this one?

> 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

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