[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-2893) Criteria api does not allow a groupProperty to _NOT_ be included in the selected columns

[Antony Stubbs (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2893?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Antony Stubbs updated HHH-2893:
-------------------------------

    Attachment: hibernate criterion patch.patch

Woah. Not at all as easy as I thought. 
Quite an interesting look into the guts of Hibernate this time though.

This is a rough cut first attempt at a fix. It works for me for a query that looks like this:
[code]
	    Criteria c2 = s.createCriteria(TankerLog.class);
	    c2.setProjection( Projections.projectionList()
	        .add( Projections.max("id.sampleTimestamp") )
	        .add( Projections.groupProperty("t.tankerRefId",true) )
	        .add( Projections.groupProperty("id.variableId",true) )
	        );
	    c2.createAlias("tankerHeader", "h");	    
	    c2.createAlias("tankerHeader.tanker", "t");
	    c2.add(Restrictions.in("t.tankerRefId", tankerRefIds));
	    c2.add(Restrictions.in("id.variableId", varIds));
[/code]

those 'true' statements indicate that it's a groupProperty which should not be included in the select.

Tomorrow when I get time, I will try running the unit tests. Haven't figured out how they work yet.

Until then!

> Criteria api does not allow a groupProperty to _NOT_ be included in the selected columns
> ----------------------------------------------------------------------------------------
>
>                 Key: HHH-2893
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2893
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.2.5
>         Environment: n/a
>            Reporter: Antony Stubbs
>         Attachments: hibernate criterion patch.patch
>
>   Original Estimate: 2 hours
>  Remaining Estimate: 2 hours
>
> When adding a Projections.groupProperty("prop") to a criteria, the property is added to both the GROUP BY and SELECT parts of the generated SQL query. This is a major problem for using the criteria API for complex subselects that require aggregating by a column while grouping by another column.
> There is presently no way in the criteria API to group by a column and have it _ONLY_ in the GROUP BY clause.
> i.e.
> currently Projections.groupProperty("propOne") causes:
> SELECT propOne, MAX(propTwo) FROM table GROUP BY prop
> desired option is to have
> SELECT MAX(propTwo) FROM table GROUP BY prop
> There have been several people talking about this, but there appears to have been 'no comment'.
> http://forum.hibernate.org/viewtopic.php?t=939071&highlight=values+groupproperty
> http://forum.hibernate.org/viewtopic.php?t=950803&highlight=groupproperty+subquery
> http://forum.hibernate.org/viewtopic.php?t=964512&highlight=groupproperty+subquery
> http://forum.hibernate.org/viewtopic.php?t=957671&highlight=groupproperty+subquery
> http://forum.hibernate.org/viewtopic.php?p=2317841#2317841
> http://forum.hibernate.org/viewtopic.php?t=961617&highlight=groupproperty+subquery
> Find a 1st cut patch on it's way - I'm just itching my scratch now...
> Great work on Hibernate guys!

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