<style>
/* Changing the layout to use less space for mobiles */
@media screen and (max-device-width: 480px), screen and (-webkit-min-device-pixel-ratio: 2) {
#email-body { min-width: 30em !important; }
#email-page { padding: 8px !important; }
#email-banner { padding: 8px 8px 0 8px !important; }
#email-avatar { margin: 1px 8px 8px 0 !important; padding: 0 !important; }
#email-fields { padding: 0 8px 8px 8px !important; }
#email-gutter { width: 0 !important; }
}
</style>
<div id="email-body">
<table id="email-wrap" align="center" border="0" cellpadding="0" cellspacing="0" style="background-color:#f0f0f0;color:#000000;width:100%;">
<tr valign="top">
<td id="email-page" style="padding:16px !important;">
<table align="center" border="0" cellpadding="0" cellspacing="0" style="background-color:#ffffff;border:1px solid #bbbbbb;color:#000000;width:100%;">
<tr valign="top">
<td bgcolor="#3e4c4e" style="background-color:#3e4c4e;color:#ffffff;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;line-height:1;"><img src="https://www.jboss.org/dms/hibernate/images/jira/jiraheader_hibernate.png" alt="" style="vertical-align:top;" /></td>
</tr><tr valign="top">
<td id="email-banner" style="padding:32px 32px 0 32px;">
<table align="left" border="0" cellpadding="0" cellspacing="0" width="100%" style="width:100%;">
<tr valign="top">
<td style="color:#505050;font-family:Arial,FreeSans,Helvetica,sans-serif;padding:0;">
<div id="email-action" style="padding: 0 0 8px 0;font-size:12px;line-height:18px;">
<a class="user-hover" rel="ringerc" id="email_ringerc" href="https://hibernate.onjira.com/secure/ViewProfile.jspa?name=ringerc" style="color:#6c797f;">Craig Ringer</a>
edited a comment on <img src="https://hibernate.onjira.com/images/icons/bug.gif" height="16" width="16" border="0" align="absmiddle" alt="Bug"> <a style='color:#6c797f;text-decoration:none;' href='https://hibernate.onjira.com/browse/HHH-7611'>HHH-7611</a>
</div>
<div id="email-summary" style="font-size:16px;line-height:20px;padding:2px 0 16px 0;">
<a style='color:#6c797f;text-decoration:none;' href='https://hibernate.onjira.com/browse/HHH-7611'><strong>Incorrect GROUP BY query produced using JPA Criteria API with PostgreSQL</strong></a>
</div>
</td>
</tr>
</table>
</td>
</tr>
<tr valign="top">
<td id="email-fields" style="padding:0 32px 32px 32px;">
<table border="0" cellpadding="0" cellspacing="0" style="padding:0;text-align:left;width:100%;" width="100%">
<tr valign="top">
<td id="email-gutter" style="width:64px;white-space:nowrap;"></td>
<td>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr valign="top">
<td colspan="2" style="color:#000000;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:12px;padding:0 0 16px 0;width:100%;">
<div class="comment-block" style="background-color:#edf5ff;border:1px solid #dddddd;color:#000000;padding:12px;"><p>Argh, JIRA won't let me edit the description. Sorry, I wanted to elaborate.</p>
<p>The test case is one the original reporter (<a href="https://hibernate.onjira.com/secure/ViewProfile.jspa?name=windy">https://hibernate.onjira.com/secure/ViewProfile.jspa?name=windy</a>) greatly simplified down from the real-world problem, as explained in the linked Stack Overflow report.</p>
<p>For testSimplyfiedGroupByObject in the test case (now re-attached here):</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java"><span class="code-keyword">public</span> void testSimplyfiedGroupByObject() {
EntityManager entityManager = buildEntityManager();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<DBObject> query = builder.createQuery(DBObject.class);
Root<DBObjectAccessCounter> from = query.from(DBObjectAccessCounter.class);
Path<DBObject> object = from.get(DBObjectAccessCounter_.object);
query.groupBy(object).select(object);
entityManager.createQuery(query).getResultList();
}</pre>
</div></div>
<p>Hibernate is producing the query:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">select dbobject1_.id as id0_ from DBObjectAccessCounter dbobjectac0_ <span class="code-keyword">inner</span> join DBObject dbobject1_ on dbobjectac0_.object_id=dbobject1_.id group by dbobjectac0_.object_id;</pre>
</div></div>
<p>which fails to execute with:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">ERROR: column <span class="code-quote">"dbobject1_.id"</span> must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select dbobject1_.id as id0_ from DBObjectAccessCounter dbob...</pre>
</div></div>
<p>The correct formulation is:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">select dbobject1_.id as id0_ from DBObjectAccessCounter dbobjectac0_ <span class="code-keyword">inner</span> join DBObject dbobject1_ on dbobjectac0_.object_id=dbobject1_.id group by dbobject1_.id;</pre>
</div></div>
<p>The only change I've made to correct it is that the GROUP BY references the other side of the join condition, the side mentioned in the SELECT list.</p>
<p>This remains the case even if I rephrase the test to explicitly group by the DBObject side:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">Path<DBObject> object = from.get(DBObjectAccessCounter_.object);
Path<<span class="code-object">Long</span>> objectId = object.get(DBObject_.id);
query.groupBy(objectId).select(object);</pre>
</div></div>
<p>which seems dead wrong. Looks like a bug to me.</p>
<p>Maybe some RDBMSes infer that the two fields are the same because of the `INNER JOIN` and implicitly transform the first statement into a `GROUP BY` on the `id` of `DBObject`? Or maybe this is just a Pg dialect bug.</p></div>
<div style="color:#505050;padding:4px 0 0 0;"> </div>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td><!-- End #email-page -->
</tr>
<tr valign="top">
<td style="color:#505050;font-family:Arial,FreeSans,Helvetica,sans-serif;font-size:10px;line-height:14px;padding: 0 16px 16px 16px;text-align:center;">
This message is automatically generated by JIRA.<br />
If you think it was sent incorrectly, please contact your JIRA administrators<br />
For more information on JIRA, see: <a style='color:#6c797f;' href='http://www.atlassian.com/software/jira'>http://www.atlassian.com/software/jira</a>
</td>
</tr>
</table><!-- End #email-wrap -->
</div><!-- End #email-body -->