title: JPQL query with LEFT JOIN and GROUP BY
body: <p>I'm learning JPQL using simple Hibernate 5.0.4, Spring 4.2.3, Maven 3.3.3 based project on Oracle 11g XE. Full source code can be found on my <a href="https://github.com/pduleba/hibernate/tree/branch/Query_JPQL_Problem" rel="nofollow noreferrer">GitHub branch</a>. </p>
  
  <p>I've got 2 models:</p>
  
  <pre><code>import java.util.Date;
  import java.util.LinkedList;
  import java.util.List;
  
  import javax.persistence.CascadeType;
  import javax.persistence.Column;
  import javax.persistence.Entity;
  import javax.persistence.EnumType;
  import javax.persistence.Enumerated;
  import javax.persistence.FetchType;
  import javax.persistence.GeneratedValue;
  import javax.persistence.GenerationType;
  import javax.persistence.Id;
  import javax.persistence.OneToMany;
  import javax.persistence.SequenceGenerator;
  import javax.persistence.Table;
  import javax.persistence.Temporal;
  import javax.persistence.TemporalType;
  
  import lombok.Data;
  import lombok.NoArgsConstructor;
  
  @Entity
  @Table(name = "T_OWNER")
  @NoArgsConstructor
  public @Data class OwnerModel {
  
      public OwnerModel(String firstName, String lastName, Integer age, OwnerType type) {
          super();
          this.firstName = firstName;
          this.lastName = lastName;
          this.type = type;
          this.since = new Date(System.currentTimeMillis());
          this.age = age;
      }
  
      @Id
      @GeneratedValue(generator = "owner-sequence-generator", strategy = GenerationType.SEQUENCE)
      @SequenceGenerator(name = "owner-sequence-generator", sequenceName = "OWNER_SEQ", initialValue = 1, allocationSize = 20)
      private Long id;
  
      @Column(name = "FIRST_NAME")
      private String firstName;
  
      @Column(name = "LAST_NAME")
      private String lastName;
  
      @Column(name = "TYPE")
      @Enumerated(EnumType.STRING)
      private OwnerType type;
  
      @Column(name = "SINCE")
      @Temporal(TemporalType.TIME)
      private Date since;
  
      @Column(name = "AGE")
      private Integer age;
  
      @OneToMany(mappedBy = "owner", cascade = CascadeType.ALL, fetch = FetchType.EAGER, targetEntity = CarModel.class)
      private List&lt;CarModel&gt; cars = new LinkedList&lt;&gt;();
  
      public void addCar(CarModel car) {
          cars.add(car);
          car.setOwner(this);
      }
  }
  
  import java.sql.Blob;
  import java.sql.Clob;
  import java.util.Date;
  
  import javax.persistence.CascadeType;
  import javax.persistence.Column;
  import javax.persistence.Entity;
  import javax.persistence.FetchType;
  import javax.persistence.GeneratedValue;
  import javax.persistence.GenerationType;
  import javax.persistence.Id;
  import javax.persistence.JoinColumn;
  import javax.persistence.Lob;
  import javax.persistence.ManyToOne;
  import javax.persistence.SequenceGenerator;
  import javax.persistence.Table;
  import javax.persistence.Temporal;
  import javax.persistence.TemporalType;
  
  import lombok.AllArgsConstructor;
  import lombok.Data;
  import lombok.NoArgsConstructor;
  import lombok.ToString;
  
  @Entity
  @Table(name = "T_CAR")
  @NoArgsConstructor
  @AllArgsConstructor
  @ToString(exclude = "owner")
  public @Data class CarModel {
  
      public CarModel(String name, Integer wheelsNumber, Clob spec, Blob image) {
          super();
          this.name = name;
          this.wheelsNumber = wheelsNumber;
          this.spec = spec;
          this.image = image;
          this.createdIn = new Date(System.currentTimeMillis());
      }
  
      @Id
      @GeneratedValue(generator = "car-sequence-generator", strategy = GenerationType.SEQUENCE)
      @SequenceGenerator(name = "car-sequence-generator", sequenceName = "CAR_SEQ", initialValue = 1, allocationSize = 20)
      private Long id;
  
      @Column(name = "NAME")
      private String name;
  
      @Column(name = "CREATED_IN")
      @Temporal(TemporalType.TIMESTAMP)
      private Date createdIn;
  
      @Column(name = "WHEELS_NUMBER")
      private Integer wheelsNumber;
  
      @Lob
      @Column(name = "SPEC")
      private Clob spec;
  
      @Lob
      @Column(name = "IMAGE")
      private Blob image;
  
      @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER, targetEntity = OwnerModel.class)
      @JoinColumn(name = "ID_OWNER")
      private OwnerModel owner;
  }
  </code></pre>
  
  <p>They were used to prepare some data in my DB. When I execute such DAO located  JPQL query:</p>
  
  <pre><code>    @Override
  public List&lt;?&gt; executeSelectWithGroupBy() {
      return (List&lt;?&gt;) getSession().createQuery("select o, COUNT(c) from OwnerModel o LEFT JOIN o.cars c GROUP BY o").list();
  }
  </code></pre>
  
  <p>I've got error as below :</p>
  
  <pre><code>Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet
      at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:2116)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1899)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1875)
      at org.hibernate.loader.Loader.doQuery(Loader.java:919)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
      at org.hibernate.loader.Loader.doList(Loader.java:2611)
      at org.hibernate.loader.Loader.doList(Loader.java:2594)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2423)
      at org.hibernate.loader.Loader.list(Loader.java:2418)
      at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
      at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:226)
      at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1268)
      at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
      at com.pduleba.spring.dao.OwnerDaoImpl.executeSelectWithGroupBy(OwnerDaoImpl.java:57)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:497)
      at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
      at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
      at com.sun.proxy.$Proxy39.executeSelectWithGroupBy(Unknown Source)
      at com.pduleba.spring.services.OwnerServiceImpl.executeSelectWithGroupBy(OwnerServiceImpl.java:40)
      at com.pduleba.spring.controller.QueryControllerImpl.executeQueries(QueryControllerImpl.java:39)
      at com.pduleba.hibernate.Main.execute(Main.java:50)
      at com.pduleba.hibernate.Main.main(Main.java:27)
  Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression
  
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
      at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
      at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
      ... 32 more
  </code></pre>
  
  <p>As far as I understand it is caused by error in Hibernate generated SQL :</p>
  
  <pre><code>select
      ownermodel0_.id as col_0_0_,
      count(cars1_.id) as col_1_0_,
      ownermodel0_.id as id1_1_,
      ownermodel0_.AGE as AGE2_1_,
      ownermodel0_.FIRST_NAME as FIRST_NAME3_1_,
      ownermodel0_.LAST_NAME as LAST_NAME4_1_,
      ownermodel0_.SINCE as SINCE5_1_,
      ownermodel0_.TYPE as TYPE6_1_ 
  from
      hibernate.T_OWNER ownermodel0_ 
  left outer join
      hibernate.T_CAR cars1_ 
          on ownermodel0_.id=cars1_.ID_OWNER 
  group by
      ownermodel0_.id
  </code></pre>
  
  <p>where <code>GROUP BY</code> clause should include all columns (not just id). In my opinion hibernate should generate such SQL :</p>
  
  <pre><code>select
      ownermodel0_.id as col_0_0_,
      count(cars1_.id) as col_1_0_,
      ownermodel0_.id as id1_1_,
      ownermodel0_.AGE as AGE2_1_,
      ownermodel0_.FIRST_NAME as FIRST_NAME3_1_,
      ownermodel0_.LAST_NAME as LAST_NAME4_1_,
      ownermodel0_.SINCE as SINCE5_1_,
      ownermodel0_.TYPE as TYPE6_1_ 
  from
      hibernate.T_OWNER ownermodel0_ 
  left outer join
      hibernate.T_CAR cars1_ 
          on ownermodel0_.id=cars1_.ID_OWNER 
  group by
      ownermodel0_.id,
      ownermodel0_.AGE,
      ownermodel0_.FIRST_NAME,
      ownermodel0_.LAST_NAME,
      ownermodel0_.SINCE,
      ownermodel0_.TYPE; 
  </code></pre>
  
  <p>However (as far as I understand) <a href="https://en.wikibooks.org/wiki/Java_Persistence/JPQL#GROUP_BY_Clause" rel="nofollow noreferrer">here</a> and <a href="https://stackoverflow.com/questions/4750371/how-to-make-a-jpa-query-with-left-outer-join">here</a> are shown exactly the same JPQL Queries comparing to mine. </p>
  
  <p><strong>Is it a bug in Hibernate or just a hidden bug in my code?</strong></p>
  
  <p>I appreciate your help and advice.</p>