Description
Hello,
spring-data-jpa, version: since 2.2.x
Description
The issue concerns queries with field aliases that are later used in the order by clause.
When there are no blank spaces between the select fields, the aliases of those fields are not correctly recognized while applying sorting.
For example the following query has two aliases (alias1 and alias2) with no space between the select fields:
@Query("select 1 as alias1,2 as alias2 from MyEntity mnt")
When the sorting Sort.by(Direction.ASC, "alias2") is applied, the order clause is wrongly populated. Result query:
select 1 as alias1,2 as alias2 from MyEntity mnt order by mnt.alias2 asc
instead of
select 1 as alias1,2 as alias2 from MyEntity mnt order by alias2 asc
Unit tests
I have created two unit tests in a fork reproducing the issue
1621c93
The first test that has a space after comma passes
@Test
void discoversAliasInQueryWithSpacesAfterComma() {
String query = "select 1 as alias1, 2 as alias2 from MyEntity mnt";
Sort sort = Sort.by(Direction.ASC, "alias2");
System.out.println(applySorting(query, sort));
assertThat(QueryUtils.applySorting(query, sort))
.endsWith("order by alias2 asc");
}
Result:
select 1 as alias1, 2 as alias2 from MyEntity mnt order by alias2 asc
The second test that has no space after comma fails
@Test
void discoversAliasInQueryWithoutSpacesAfterComma() {
String query = "select 1 as alias1,2 as alias2 from MyEntity mnt";
Sort sort = Sort.by(Direction.ASC, "alias2");
System.out.println(applySorting(query, sort));
assertThat(QueryUtils.applySorting(query, sort))
.endsWith("order by alias2 asc");
}
Result:
FAILURE!
Expecting actual:
"select 1 as alias1,2 as alias2 from MyEntity mnt order by mnt.alias2 asc"
to end with:
"order by alias2 asc"
Cause
My understanding is that the root cause of the issue is the regex used for discovering fields alias, more specifically here: org.springframework.data.jpa.repository.query.QueryUtils#FIELD_ALIAS_PATTERN
The regex used is
builder.append("\\s+"); // at least one space
builder.append("[^\\s\\(\\)]+"); // No white char no bracket
builder.append("\\s+[as|AS]+\\s+(([\\w\\.]+))"); // the potential alias
which requires the field declaration to start with a space.
Additionally, there is a problem with the regex [as|AS] term. This term intention seem to be to match as or AS but it actually matches a or s or | or A or S. The correct expression would be (as|AS)
I would be happy to provide a pull request fixing this issue.