Sorting doesn't work for field alias [DATAJPA-1061]

[spring-projects-issues]

Sergey Skryabin opened DATAJPA-1061 and commented

    @Query("SELECT c.name AS n FROM cats c")
    List<String> findCats(Sort sort);
...
    findCats(new Sort("n"));

doesn't work ("The state field path 'c.n' cannot be resolved to a valid type."). Although

    @Query("SELECT lower(c.name) AS n FROM cats c")
    List<String> findCats(Sort sort);
...
    findCats(new Sort("n"));

works fine.

http://docs.spring.io/spring-data/jpa/docs/1.11.x/reference/html/#jpa.query-methods.sorting
says: The properties actually used within the Order instances of Sort need to match to your domain model, which means they need to resolve to either a property or an alias used within the query.

---

Affects: 1.11 GA (Ingalls)

Attachments:

• DATAJPA-1061.log (8.10 kB)

Referenced from: pull request #276

Backported to: 2.1.10 (Lovelace SR10), 1.11.23 (Ingalls SR23)

3 votes, 9 watchers

[spring-projects-issues]

Oliver Drotbohm commented

Would you mind adding the complete stack trace? I suspect it's rather a persistence provider specific issue that might not allow aliases for properties that are not backed by a function expression.

Generally speaking, sorting by alias is something you should use only as a last resort as it's absolutely not clear to clients where that n comes from, unless it actually knows about the query, which in turn I argue it shouldn't

[spring-projects-issues]

Sergey Skryabin commented

Log in attachment.
No, i expect problem is within org.springframework.data.jpa.repository.query.QueryUtils, method public static String applySorting(String query, Sort sort, String alias).

Set<String> aliases = getOuterJoinAliases(query);
Set<String> functionAliases = getFunctionAliases(query);

for (Order order : sort) {
	builder.append(getOrderClause(aliases, functionAliases, alias, order)).append(", ");
}

Set<String> fieldAliases = getFieldAliases(query);

is needed.

The real test case is we have complex @Query with 5 joins. And we extract only a few fields from each entity. We don't want client know about joinAliases, so as WA we need make mapping (client known field names (= aliases) to full names (join_alias.field_name)), and remaking Sort

[spring-projects-issues]

Grégoire Druant commented

Hi,

I have provided a pull request for this bug, as I needed a fix for the project I am working on.

Here it is : #276

Regards

[spring-projects-issues]

Dario Seidl commented

We are facing the same problem. I'm pretty sure that the other to comments are correct and this is a bug in the detection of the aliases in QueryUtils. As the code snippet from Sergey Skryabin shows, only join aliases and function aliases are detected. Furthermore the regex for the function aliases only works for exactly one pair of parenthesis.

For example, we have a query with the following select statement:

SELECT DISTINCT(event.id) as id,
event.name as name,
MIN(bundle.base_price_amount) as cheapestBundlePrice,
MIN(DATE(bundle.start)) as earliestBundleStart
FROM event event
LEFT JOIN bundle bundle ON event.id = bundle.event_id
...

In this query, only cheapestBundlePrice (alias for a single function) and event and bundle (aliases for join) are detected correctly.

The name alias is not detected (no function), and the earliestBundleStart alias is also not detected (two functions).

It would be great if GregDrt's pull request could be merged or reviewed. I believe it would fix the detection of aliases for fields, but not the detection of aliases for two or more functions

[spring-projects-issues]

Luca Garofalo commented

Hello,

we have a situation were for a native query the sort is not applied correctly, i'll try to explain below.

 

QueryUtils.applySorting is called also for Native SQL Query in Spring Data JPA. In native SQL there are many situation were the method applySorting fails.
For example considering the following query it fails because the 'order by' used in the listagg function is matched by the ORDER_BY Pattern and the applySorting method doens't add the "order by" clause at the end of the query.

 

 

   SELECT                                                           
        T1.ID                                                 aS C1,
        T1.TYPE                                               As C2,
        T2.COL1                                               as C3,
        SS.COL2                                               AS C4,
        listagg(T1.CODE , ',') within group (order by T1.ID)  as C5,
        sum(T1.CNUM)                                          as C6,
FROM TABLE1S S                                                      
JOIN TABLE2  T2 on T1.ID = T2.ID                                    
JOIN TABLESS SS on SS.ID = T1.ID                                    
GROUP BY T1.ID, T1.TYPE, T2.COL1, SS.COL2                           

 

Possible solution for that is to change the ORDER_BY Pattern regex in the QueryUtils.java file so that it doens't considere 'order by' written inside round brackets (this solve also the case where are nested query with sorting applied, if for any reason there should be a need to do so) :

 

 

private static final Pattern ORDER_BY = Pattern.compile(".*[^\\(]\\s+order\\s+by\\s+.*[^\\)].*", CASE_INSENSITIVE);

 

Moreover for the same query, the method QueryUtils.getFieldAliases extracts the following aliases: w[C3, C4, C5, C6, JOIN, C1, C2, on] when it sould be [C3, C4, C5, C6, C1, C2]

To fix this you can change the FIELD_ALIAS_PATTERN regex ** like this:

 

builder = new StringBuilder();
builder.append("\\s[aA][sS]+\\s+(([\\w\\.]+))");
FIELD_ALIAS_PATTERN = compile(builder.toString());

I tryed all the QueryUtils Unit Tests and they run succesfully, should I provide a pull request for this?

Thank you

 

 

 

[spring-projects-issues]

Jens Schauder commented

Luca Garofalo I think what you describe is DATAJPA-1406. If not and you don't find any other open issue describing your problem please create a new issue.

[spring-projects-issues]

Dario Seidl commented

I have tried it and I can confirm now that the detection of aliases for fields is fixed, but not the detection of aliases for two or more functions.

The FUNCTION_PATTERN in QueryUtils looks like this:

builder = new StringBuilder();
// any function call including parameters within the brackets
builder.append("\\w+\\s*\\([\\w\\.,\\s'=]+\\)");
// the potential alias
builder.append("\\s+[as|AS]+\\s+(([\\w\\.]+))");
FUNCTION_PATTERN = compile(builder.toString());

which only considers a single pair of parenthesis, but not two or more. So, for example, the earlierstBundleStart is not detected in this query:

 

SELECT DISTINCT(event.id) as id,
event.name as name,
event.top_event as topEvent,
event.ranking as ranking,
MIN(bundle.base_price_amount) as cheapestBundlePrice,
MIN(DATE(bundle.start)) as earliestBundleStart,
..

 

 

 

[abccbaandy]

still happen with 2.3.4.RELEASE
I have to use JpaSort.unsafe( "(my_alias)") to prevent Unknown column my_table.my_alias error.

[yalecason]

Looks like using a FUNCTION_PATTERN of (?:\w+\s*\([\w\.,\s'=\(]+\)+)+\s+[as|AS]+\s+(([\w\.]+)) in the QueryUtils class would work in at least the case I've run into today.

Current pattern is: \w+\s*\([\w\.,\s'=]+\)\s+[as|AS]+\s+(([\w\.]+))

[darioseidl]

As per the comments, I don't think this bug was fixed for all cases. Could this issue be reopened?

[schauder]

If there is still a problem please create a new issue.