Explore property to SQL column name mapping for using `Sort.by(…)` with native queries

[onbao165]

I'm encountering an error Unknown column 'createdAt' in 'order clause' error when passing "createdAt" property with Spring Data's PageRequest. The createdAt field is inherited from a @MappedSuperclass. I'm correctly using the entity property name, not the column name. The strange part is that the error often goes away after a few application restarts, only to reappear later. Why does Spring Data JPA/Hibernate intermittently fail to resolve the correct column name for an inherited property during sorting, and what is the correct way to prevent this from happening?

Problematic Code:

Pageable pageable = PageRequest.of(request.getPageNumber(), request.getPageSize(), Sort.by("createdAt").ascending());
Page<Submission> page = submissionRepo.findAll(pageable);

The Error:

[http-nio-8080-exec-2] DEBUG org.hibernate.SQL - SELECT * FROM submissions WHERE user_id = ? order by createdAt asc limit ?
[http-nio-8080-exec-2] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1054, SQLState: 42S22
[http-nio-8080-exec-2] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Unknown column 'createdAt' in 'order clause'

Entity class and its Parent

@EqualsAndHashCode(callSuper = true)
@Entity
@Table(name = "submissions")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@SQLRestriction("is_deleted = false")
public class Submission extends BaseAuditableEntity<UUID> {
    @Column(name = "full_name", nullable = false)
    private String fullName;

    @Column(name = "order_id")
    private String orderId;

    @Column(name = "phone_number", nullable = false)
    private String phoneNumber;

    @Column(name = "address", nullable = false, columnDefinition = "TEXT")
    private String address;

    @Column(name = "registration_email", nullable = false)
    private String registrationEmail;

    @Column(name = "id_card_back_path", nullable = false)
    private String idCardBackPath;

    @Column(name = "id_card_front_path", nullable = false)
    private String idCardFrontPath;

    @Column(name = "portrait_photo_path", nullable = false)
    private String portraitPhotoPath;

    @Column(name = "status", nullable = false, length = 20)
    @Enumerated(EnumType.STRING)
    private SubmissionStatus status;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private AppUser user;
}


@MappedSuperclass
@Data
@EqualsAndHashCode(callSuper = true)
@EntityListeners(AuditingEntityListener.class)
public class BaseAuditableEntity<K> extends BaseEntity<K> implements Auditable, SoftDeletable {
    @CreatedDate
    @Column(name = "created_at", updatable = false, nullable = false)
    private LocalDateTime createdAt;

    @LastModifiedDate
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

    @Column(name = "is_deleted")
    private boolean isDeleted;

    @Column(name = "deleted_at")
    private LocalDateTime deletedAt;
}

[onbao165]

For the version, I'm using spring-data-jpa 3.5.2 as part of spring-boot-starter-data-jpa:3.5.4

[mp911de]

Care to provide a reproducer? This seems a rather specific bug. While not necessarily the cause, the rendered SQL mentions the submissions table while you've included the AppUser entity.

[onbao165]

Care to provide a reproducer? This seems a rather specific bug. While not necessarily the cause, the rendered SQL mentions the submissions table while you've included the AppUser entity.

I'm so sorry for the confusion, I've edited the issue to include the right entity class definition. Also for the reproducer: Prerequisites:

• Java Development Kit (JDK) 17 or higher
• Apache Maven 3.6+
• MySQL for database or you can config to use H2

Source code: I pushed a part of my project that is related to the problem to this repo. I’m not sure under what exact conditions the error triggers sadly. I just happen to encounter this error when I run and test the GET /submissions API. All I know is when the error occurs, I tried to change the sort property into something like created_at, this gives an expected unknown column error, then I switch back to createdAt and it just works.

[onbao165]

Um, so actually, I think the problem might be that I called this native query

    @Query(value = "SELECT * FROM submissions WHERE user_id = :userId AND is_deleted = false",
            countQuery = "SELECT COUNT(*) FROM submissions WHERE user_id = :userId AND is_deleted = false",
            nativeQuery = true)
    Page<Submission> findByUserIdNative(@Param("userId") UUID userId, Pageable pageable);

So the property createdAt is not defined for such native query, changing to created_at would works for this query but then fails for the auto-generated findAll() query by JPA, so this is a "me" problem!

[mp911de]

Alright, thanks a lot for looking into it.

I’ve seen a few tickets regarding reference to property names in the context of native queries and the need (or expectation) of translating property names to actual column names.

I wonder whether there is a lean way to obtain the column name from the JPA provider for a particular entity type. Am 4. Sept. 2025, 17:18 +0200 schrieb barooon165 @.***>:

onbao165 left a comment (spring-projects/spring-data-jpa#3980) Um, so actually, I think the problem might be that I called this native query @Query(value = "SELECT * FROM submissions WHERE user_id = :userId AND is_deleted = false", countQuery = "SELECT COUNT() FROM submissions WHERE user_id = :userId AND is_deleted = false", nativeQuery = true) Page<Submission> @.("userId") UUID userId, Pageable pageable); So the property createdAt is not defined for such native query, changing to created_at would works for this query but then fails for the auto-generated findAll() query by JPA, so this is a "me" problem! — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were assigned.Message ID: @.*>

[onbao165]

Alright, thanks a lot for looking into it.

I’ve seen a few tickets regarding reference to property names in the context of native queries and the need (or expectation) of translating property names to actual column names.

I wonder whether there is a lean way to obtain the column name from the JPA provider for a particular entity type. Am 4. Sept. 2025, 17:18 +0200 schrieb barooon165 @.***>: …

Thanks for looking into this! I’d also be very interested in any updates or approaches regarding that topic.