JPQL datamanager maxresult() with Distinct issue

Support
#1

Hi,

When i am trying to execute below query , i am getting column present in distinct must be present in order by. But as seen in below query it is working internal in JPQL.
I am trying to execute query in for loop , i had count of below query and i am trying to fetch and process data in chunk wise like 0 to 5000 the 5001 to 10000 depending on how data size.

One observation i found is if below query has output of more than 5000 data then this throws and error if less than 5000 it works, just an observation.


LogicalCondition condition;

dataManager.load(User.class)
                    .query("SELECT DISTINCT e FROM Cases e " +
                            " LEFT JOIN UserCase a " +
                            " ON e.Id = a.Id "
                    .condition(
                            condition
                    ).maxResults(0).maxResults(5000).list();

Below order by getting printed in query if check in debug mode


ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT null)) OFFSET 0 ROWS FETCH NEXT 5000 ROWS ONLY
#2

Hi

You passing User.class as a parameter to dataManager.load() method, but then querying Cases objects.
Please pass the same class to the load method as you use in your query.

#4

Correction on above question same class is used in load method

dataManager.load(Cases.class)
                    .query("SELECT DISTINCT e FROM Cases e " +
                            " LEFT JOIN UserCase a " +
                            " ON e.Id = a.Id "
                    .condition(
                            condition
                    ).maxResults(0).maxResults(5000).list();
#5

Hi,

Even if don’t use maxResults just use just load the query with distinct i am still getting exception.

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.6-jmix): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Error Code: 145
#7

Hi Adnan
Try to execute this query in MS SQL. They have some strange issues with DISTINCT. This is a MS SQL Problem and not JMIX.

com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
#8

Try turning on sql logging to view the generated query:

# 'debug' level logs SQL generated by EclipseLink ORM
logging.level.eclipselink.logging.sql = debug
#9

Hi,

I resolved this issue by adding order by manually in the jpql query.