I want to run a criteria with count and distinct at the same time.
I have the code bellow:
final EntityManager entityManager = getEntityManagerFactory().createEntityManager();
final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class);
final Root<Manufacturer> root = criteriaQuery.from(Manufacturer.class);
criteriaQuery.select(criteriaBuilder.count(root));
final Join productJoin = root.join("products");
productJoin.join("nickNames");
criteriaQuery.distinct(true);
final TypedQuery<Long> countQuery = entityManager.createQuery(criteriaQuery);
System.out.println(countQuery.getSingleResult());
Is anything wrong with my criteria? Or is a OpenJPA bug? If I remove the distinct the query executes without a problem.
With the criteria above I am receiving the following exception message:
Exception in thread "main" <openjpa-2.3.0-r422266:1540826 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Failed to execute query "null". Check the query syntax for correctness. See nested exception for details.
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:872)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:794)
at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542)
at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:275)
at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:291)
at org.apache.openjpa.persistence.QueryImpl.getSingleResult(QueryImpl.java:319)
at com.uaihebert.test.Main.main(Main.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: unexpected token: FROM {SELECT DISTINCT FROM (SELECT DISTINCT t0.id AS t0_id FROM Manufacturer t0 INNER JOIN Manufacturer_Product t1 ON t0.id = t1.MANUFACTURER_ID INNER JOIN Product t2 ON t1.PRODUCTS_ID = t2.id INNER JOIN Product_NickName t3 ON t2.id = t3.PRODUCT_ID INNER JOIN NickName t4 ON t3.NICKNAMES_ID = t4.id)} [code=-5581, state=42581]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:199)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:59)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:251)
at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:133)
at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140)
at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:133)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1643)
at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:122)
at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:508)
at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:488)
at org.apache.openjpa.jdbc.sql.SelectImpl.prepareStatement(SelectImpl.java:481)
at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:422)
at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:384)
at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94)
at org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.open(QueryImpl.java:2070)
at org.apache.openjpa.kernel.QueryImpl.singleResult(QueryImpl.java:1320)
at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1242)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1007)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:863)
... 11 more
Try using CriteriaBuilder#countDistinct() in your code.
// ...
criteriaQuery.select(criteriaBuilder.countDistinct(root));
final Join productJoin = root.join("products");
productJoin.join("nickNames");
//criteriaQuery.distinct(true); -- commented u=out
// ...
Under Hibernate 4.3.5 your query works well, but it may be vendor specific behaviour (not necessarly OpenJPA's bug).
I cannot tell how count(..) and then distinct(true) differs from countDistinct(..) but logically it can be the same difference as writing DISTINCT(COUNT(..)) and COUNT(DISTINCT(..)).
update
After checking resulting SQLs, I can confirm that with separate count() and distinct() the resulting query is SELECT DISTINCT COUNT(..) .. and for countDistinct() it is SELECT COUNT(DISTINCT(..) ...