title: distinct(true) - could not prepare statement
body: <p>I am using hibernate with spring. However, when adding <code>.distinct(true)</code> like <code>criteria.select(cb.construct(Customer.class, root.get("Name"))).distinct(true);</code>. Then I am receiving the following exception:</p>
  
  <pre><code>Exception in thread "AWT-EventQueue-0" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
      at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
      at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
      at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:273)
      at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:254)
      at com.limitCalculator.dao.FilterDaoImpl.getFilter(FilterDaoImpl.java:83)
      at com.limitCalculator.dao.FilterDaoImpl$$FastClassByCGLIB$$a62bf500.invoke(&lt;generated&gt;)
      at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
      at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
      at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
      at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
      at com.limitCalculator.dao.FilterDaoImpl$$EnhancerByCGLIB$$6ca0bd06.getFilter(&lt;generated&gt;)
      at com.limitCalculator.service.FilterServiceImpl.getFilter(FilterServiceImpl.java:37)
      at com.limitCalculator.gui.timerSelection.MainTabPanel.placeSelectionWithButtons(MainTabPanel.java:137)
      at com.limitCalculator.gui.timerSelection.MainTabPanel.createLayout(MainTabPanel.java:111)
      at com.limitCalculator.gui.timerSelection.MainWindow.createTabBar(MainWindow.java:132)
      at com.limitCalculator.gui.timerSelection.MainWindow.makeLayout(MainWindow.java:182)
      at com.limitCalculator.gui.timerSelection.MainWindow.access$1(MainWindow.java:172)
      at com.limitCalculator.gui.timerSelection.MainWindow$4.run(MainWindow.java:197)
      at java.awt.event.InvocationEvent.dispatch(Unknown Source)
      at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
      at java.awt.EventQueue.access$400(Unknown Source)
      at java.awt.EventQueue$2.run(Unknown Source)
      at java.awt.EventQueue$2.run(Unknown Source)
      at java.security.AccessController.doPrivileged(Native Method)
      at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.run(Unknown Source)
  Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
      at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
      at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:188)
      at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159)
      at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1854)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1831)
      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
      at org.hibernate.loader.Loader.doQuery(Loader.java:899)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
      at org.hibernate.loader.Loader.doList(Loader.java:2516)
      at org.hibernate.loader.Loader.doList(Loader.java:2502)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
      at org.hibernate.loader.Loader.list(Loader.java:2327)
      at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
      at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
      at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1268)
      at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
      at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
      ... 33 more
  Caused by: java.sql.SQLSyntaxErrorException: invalid ORDER BY expression
      at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
      at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
      at org.hsqldb.jdbc.JDBCPreparedStatement.&lt;init&gt;(Unknown Source)
      at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
      at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
      at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
      at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:161)
      at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
      ... 49 more
  </code></pre>
  
  <p>My hibernate query looks like that:    </p>
  
  <p><strong>Hibernate:</strong> </p>
  
  <pre><code>select distinct customer0_.Name as col_0_0_ 
  from Customer customer0_ 
  where (upper(customer0_.Name) like ?) and (upper(customer0_.City) like ?) and
      (upper(customer0_.Country) like ?) 
  order by customer0_.customerNr asc
  </code></pre>
  
  <p>Thats the code I am using:</p>
  
  <pre><code>@Transactional
  @SuppressWarnings("all")
  public List&lt;Customer&gt; getFilter(String city, String country) {
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery&lt;Customer&gt; criteria = cb.createQuery(Customer.class);
      //which table we want to fetch
      final Root root = criteria.from(Customer.class);
      List criteriaList = createWhereCritera(city, country, cb, root);
  
      //create statement
      criteria.select(cb.construct(Customer.class, root.get("Name"))).distinct(true);
      // Pass the criteria list to the where method of criteria query
      criteria.where(cb.and((Predicate[]) criteriaList.toArray(new Predicate[0])));
      // Order by clause
      criteria.orderBy(cb.asc(root.get("customerNr")));
      return em.createQuery(criteria).getResultList();
  }
  
  public List createWhereCritera(String city, String country, CriteriaBuilder cb,
          final Root root) {
      // This list will contain all Predicates (where clauses)
      List criteriaList = new ArrayList();
      // City: 
      Predicate predicateCity = cb.like(cb.upper(root.get("City")),city);
      criteriaList.add(predicateCity); 
      // Country: 
      Predicate predicateCountry = cb.like(cb.upper(root.get("Country")),country);
      criteriaList.add(predicateCountry);
      return criteriaList;
  }   
  </code></pre>
  
  <p>Any recommendation, why this issue occurs and how to get all distinct values?</p>
  
  <p>I appreciate your answer!</p>