Broken count query for native queries with table alias

[hpoettker]

The following repository currently does not work with MySQL:

public interface SomeRepository extends CrudRepository<SomeEntity, Long> {

  @Query(
      value = "SELECT * FROM table_name some_alias",
      nativeQuery = true
  )
  Page<SomeEntity> search(Pageable pageable);

}

The repository worked fine with Spring Data JPA 2.6.3, has been broken since 2.6.4, and is still broken in 2.7.7.

The log on failure is

Hibernate: SELECT * FROM table_name some_alias ?
Hibernate: select count(some_alias) FROM table_name some_alias
WARN --- o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1054, SQLState: 42S22
ERROR --- o.h.engine.jdbc.spi.SqlExceptionHelper : Unknown column 'some_alias' in 'field list'

The problem is the count query that should be select count(*) FROM table_name some_alias with * instead of the alias as parameter to COUNT.

The regression seems to have been introduced with #2389 as the PR contains a test case for the erroneous behavior.

[mp911de]

The query rewrite doesn't distinguish between native an JPQL queries. In JPQL, this is a valid scheme to count on From level while SQL mandates columns or expressions. It's a bug that we need to fix.

[hpoettker]

Thanks for implementing a fix so quickly after I created the issue!

As the issue is assigned to the milestone 2.7.8, this may be a stupid question, but will the fix be backported? I'm asking because the issue is closed but there is no respective commit on the branch 2.7.x.

[mp911de]

Thanks for reaching out. The missing backport was an oversight on our end. The backport is now in the 2.7.x branch and you should be able to verify the fix in the artifact that is being currently built by our CI.