[JPA] Unexpected field mapping during native query

[tiarebalbi]

I'm seeing a weird behaviour for this query.

For the given entity:

@Entity
@Table(name = "pesquisas")
data class Pesquisa(
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "pesquisa_id")
    val id: Long? = null,
    @Column(name = "termo", nullable = false)
    val termo: String,
    @Column(name = "aprovado", nullable = false)
    val aprovado: Boolean = false,
    @Column(name = "data_registro", nullable = false)
    val dataRegistro: LocalDateTime = LocalDateTime.now()
) : Serializable

I'm trying to run the following query as nativeQuery:

@Repository
interface PesquisaRepository : JpaRepository<Pesquisa, Long> {
    @Query(
        nativeQuery = true,
        value = "SELECT DISTINCT ON (termo) * FROM pesquisas"
    )
    fun findDistinctByTermo(pageable: Pageable): Page<Pesquisa>
}

SELECT DISTINCT ON (termo) * FROM pesquisas order by dataRegistro desc fetch first ? rows only

The output of this query SELECT DISTINCT ON (termo) * FROM pesquisas is not looking right (logs below)...
The pageable didn't have any sort attribute and the query was using the attribute name instead of the alias defined in the @column annotation.

2023-10-25T22:58:24.287-07:00 DEBUG [mts-app,653a00006683e010b8102aa428c0eaf3,a0774ec5dcbb7188] 83428 --- [nio-8080-exec-7] o.s.web.servlet.DispatcherServlet        : GET "/core/v1/pesquisa?page=0&size=10", parameters={masked}

....

2023-10-25T22:55:54.054-07:00 DEBUG [mts-app,6539ff69f456cbdab604746ce16b27de,3810741ccedbf6fb] 83428 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Failed to complete request: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [SELECT DISTINCT ON (termo) * FROM pesquisas order by dataRegistro desc fetch first ? rows only] [ERROR: column "dataregistro" does not exist
  Hint: Perhaps you meant to reference the column "pesquisas.data_registro".
  Position: 54] [n/a]; SQL [n/a]

https://gist.github.com/tiarebalbi/4068250d99022b853b2e54d423a86463

Versions:

• Spring boot: 3.1.5
• spring.dependency-management = 1.1.3
• Java 17

[mp911de]

order by dataRegistro desc fetch first ? rows only is being added as result of using Pageable. Spring Data JPA doesn't evaluate any column mappings. Please check the query creation at AbstractStringBasedJpaQuery.createJpaQuery(…) and inspect the Sort object. Any query rewriting for appending Sort happens in the same method.

[tiarebalbi]

@mp911de , thanks for sharing the details, tracing the method I managed to go up and I just realised I was using the @PageableDefault in the controller.

Initially I was looking only the http request and the query executed.