Regression since 7.1.28: ORDER BY on a @Formula attribute uses another attribute's column when it has the same name as that column

Hibernate ORM
1

Hi,

I think I found a regression in how ORDER BY is rendered for a @Formula attribute. It started in 7.1.28 and is still present in 7.4.15.

Mapping

The entity maps one column twice: a regular attribute with the stored value, and a @Formula that computes the value to sort by. The formula attribute has the same name as the column (state):

@Entity(name = "Item")
@Table(name = "item")
public static class Item {
    @Id
    Long id;

    @Column(name = "state")
    String savedState;            // the stored value

    @Column(name = "deleted_at")
    Instant deletedAt;

    @Formula("CASE WHEN deleted_at IS NOT NULL THEN 'REMOVED' ELSE state END")
    String state;                 // the value to sort by

    @Formula("CASE WHEN deleted_at IS NOT NULL THEN 'REMOVED' ELSE state END")
    String shownState;            // the same formula under another name
}

With the rows (1, 'NEW', null), (2, 'DONE', null) and (3, 'DONE', <a date>), ordering by the formula should give 2 (DONE), 1 (NEW), 3 (REMOVED).

What happens

When the query selects the entity, the ORDER BY uses the column of savedState instead of the formula:

select i from Item i order by i.state, i.id

-- 7.1.28 and later: returns [2, 3, 1]
select i1_0.id,i1_0.deleted_at,i1_0.state,CASE WHEN i1_0.deleted_at IS NOT NULL THEN 'REMOVED' ELSE i1_0.state END
from item i1_0 order by i1_0.state,i1_0.id

-- 6.6.53, 7.0.10, 7.1.27: returns [2, 1, 3]
select ... from item i1_0
order by CASE WHEN i1_0.deleted_at IS NOT NULL THEN 'REMOVED' ELSE i1_0.state END,i1_0.id

The formula is still in the select list; only the ORDER BY resolves i.state to the column named state.

The order is correct when:

  • the query selects only the id: select i.id from Item i order by i.state, i.id
  • the formula attribute has a name that is not a column name: select i from Item i order by i.shownState, i.id

We see the same with Criteria queries and Spring Data derived queries that select the entity.

Versions

I bisected it with the orm/hibernate-orm-7 module of hibernate-test-case-templates (H2) and the test below:

  • correct: 6.6.53, 7.0.0, 7.0.10, 7.1.0, 7.1.18, 7.1.27
  • wrong: 7.1.28, 7.1.29, 7.1.31, 7.1.36, 7.2.24, 7.3.13, 7.4.14, 7.4.15

The only formula-related fix in 7.1.28 seems to be HHH-20133 (@Formula and @JoinFormula with {p.*} expansion in native queries), so it may be related.

Test (ORMUnitTestCase in the template)

@DomainModel(annotatedClasses = ORMUnitTestCase.Item.class)
@ServiceRegistry(settings = @Setting(name = AvailableSettings.SHOW_SQL, value = "true"))
@SessionFactory
class ORMUnitTestCase {

    private static final List<Long> FORMULA_ORDER = List.of( 2L, 1L, 3L );

    @BeforeEach
    void createItems(SessionFactoryScope scope) {
        scope.inTransaction( session -> {
            session.persist( new Item( 1L, "NEW", null ) );
            session.persist( new Item( 2L, "DONE", null ) );
            session.persist( new Item( 3L, "DONE", Instant.parse( "2026-01-01T00:00:00Z" ) ) );
        } );
    }

    @AfterEach
    void dropItems(SessionFactoryScope scope) {
        scope.dropData();
    }

    @Test
    void orderByFormulaWithEntitySelected(SessionFactoryScope scope) {
        scope.inTransaction( session -> {
            var ids = session.createSelectionQuery( "select i from Item i order by i.state, i.id", Item.class )
                    .getResultList().stream().map( item -> item.id ).toList();
            assertThat( ids ).isEqualTo( FORMULA_ORDER ); // fails since 7.1.28: [2, 3, 1]
        } );
    }

    @Test
    void orderByFormulaWithIdSelected(SessionFactoryScope scope) {
        scope.inTransaction( session -> {
            var ids = session.createSelectionQuery( "select i.id from Item i order by i.state, i.id", Long.class )
                    .getResultList();
            assertThat( ids ).isEqualTo( FORMULA_ORDER ); // passes
        } );
    }

    @Test
    void orderByFormulaWithOtherNameAndEntitySelected(SessionFactoryScope scope) {
        scope.inTransaction( session -> {
            var ids = session.createSelectionQuery( "select i from Item i order by i.shownState, i.id", Item.class )
                    .getResultList().stream().map( item -> item.id ).toList();
            assertThat( ids ).isEqualTo( FORMULA_ORDER ); // passes
        } );
    }

    // + the Item entity above, with a protected no-arg constructor and
    //   Item(Long id, String savedState, Instant deletedAt)
}

Run with mvn test -Dtest=ORMUnitTestCase -Dversion.org.hibernate.orm=<version>.

Is this a bug?