Allias put in order by clause

[ALekseiMokhov]

Good day! We face problems with migration from spring data jpa 2.5.7 to 2.5.8 and higher. Case: an interface projection query with sorting and pagination:

@Query("select` max(resource.name)             as resourceName," +
            " max(resource.id) as id," +
            " max(resource.description) as description," +
            " max(resource.uuid) as uuid," +
            " max(resource.type)        as type," +
            " max(resource.createdOn)  as createdOn," +
            " max(users.firstName)     as authorFirstName," +
            " max(users.lastName)      as authorLastName," +
            " max(file.version)         as version," +
            " max(file.comment)         as comment," +
            " file.deployed             as deployed," +
            " max(log.date)             as modifiedOn" +
            "where" +
            < predicate clause>
            + "and (cast(:startDate as date) is null or resource.latestLogRecord.date between cast(:startDate as date) and cast(:endDate as date)))" +
          "group by resource.id, file.deployed, log.author.firstName, file.comment ")
    Page<Model> getResourceModelFilter(Pageable pageable,
                                               @Param("workspaceId") Long workspaceId,
                                               @Param("name") @Nullable String name,
                                               @Param("types") @Nullable List<ResourceTypeEnum> types,
                                               @Param("comment") @Nullable String comment,
                                               @Param("modifiedBy") @Nullable String modifiedBy,
                                               @Param("startDate") @Nullable @Temporal Date StartDate,
                                               @Param("endDate") @Nullable @Temporal Date EndDate);

All method params are fields of our projection (Model)
and the problem appeares when we pass param to sort like
sortParam = new Sort.Order(sortParam.getDirection(), "uuid");
The result of generated log order by section:
order by lower(uuid) asc limit ?
while the previous version log shows different:
order by lower(col_3_0_) asc limit ?

Exception thrown notes that column 'uuid' is ambiguos, and manipulation with adding order by clause to the query do not change it.

[gregturn]

There is backquote at the top and an excluded , so I'm not quite sure how to proceed. Is there a simplified version of this query for us to look at that repeats the bug you're seeing?

Could you include a link to a reproducer, staged on github, ideally using https://start.spring.io, H2 (or Testcontainers for PostGreSQL, etc.), and the version of Spring Boot aligned with your issue?

[gregturn]

This appears to qualify for #2863, where the parser should recognize that uuid is an aliased column and NOT apply the FROM clauses' alias.

A test case to capture would be something like:

@Test
void orderByAliasedColumn() {

	Sort sortParam = Sort.by(Sort.Direction.DESC, "uuid");

	assertThat(createQueryFor("""
			select
				max(resource.name)             as resourceName,
				max(resource.id) as id,
				max(resource.description) as description,
				max(resource.uuid) as uuid,
				max(resource.type)        as type,
				max(resource.createdOn)  as createdOn,
				max(users.firstName)     as authorFirstName,
				max(users.lastName)      as authorLastName,
				max(file.version)         as version,
				max(file.comment)         as comment,
				file.deployed             as deployed,
				max(log.date)             as modifiedOn
			from Resource resource
			where (
				cast(:startDate as date) is null
				or resource.latestLogRecord.date between cast(:startDate as date) and cast(:endDate as date)
			)
			group by resource.id, file.deployed, log.author.firstName, file.comment
			""", sortParam)).isEqualToIgnoringWhitespace("""
			select
				max(resource.name)             as resourceName,
				max(resource.id) as id,
				max(resource.description) as description,
				max(resource.uuid) as uuid,
				max(resource.type)        as type,
				max(resource.createdOn)  as createdOn,
				max(users.firstName)     as authorFirstName,
				max(users.lastName)      as authorLastName,
				max(file.version)         as version,
				max(file.comment)         as comment,
				file.deployed             as deployed,
				max(log.date)             as modifiedOn
			from Resource resource
			where (
				cast(:startDate as date) is null
				or resource.latestLogRecord.date between cast(:startDate as date) and cast(:endDate as date)
			)
			group by resource.id, file.deployed, log.author.firstName, file.comment
			order by uuid desc
			""".trim());
}

The last line of the expected output is the key.

[gregturn]

If you check 39e12ea, you should see your original query in there as a test case, verifying it now works.

Feel free to check out Spring Data JPA 3.1.0-SNAPSHOT and test it out for yourself.

[gregturn]

Based on this being merged, I'm closing this ticket. If you run into more problems, feel free to open a new ticket.