Sorting Does Not Work in Data Grid for Table-Per-Class Inheritance

[mariodavid]

Environment

Jmix version: 2.4.1
Database: PostgreSQL

Bug Description

Sorting on columns in a Payment list view (polymorphic query) does not return any results.

Steps To Reproduce

1. Create a Payment hierarchy using Table-Per-Class Inheritance:
   • Payment (Superclass)
   • Subclasses: CashPayment, CreditCardPayment, InsurancePayment
2. Create a list view for Payment that executes a polymorphic query on all subclasses.
3. Sort the list view by any column in Payment.

Current Behavior

• Sorting results in no data being displayed in the list view.

The following test case proofs that it is not directly a problem of Jmix, but of Eclipselink (or it was used wrong):

package io.jmix.petclinic.user;

import io.jmix.core.DataManager;
import io.jmix.core.Sort;
import io.jmix.petclinic.entity.payment.*;
import io.jmix.petclinic.test_support.AuthenticatedAsAdmin;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.transaction.Transactional;
import org.junit.jupiter.api.*;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ExtendWith(AuthenticatedAsAdmin.class)
public class PaymentTest {

    @Autowired
    private DataManager dataManager;

    @PersistenceContext
    private EntityManager entityManager;

    private final List<Object> createEntities = new ArrayList<>();

    @BeforeEach
    void setUp() {

        dataManager.load(Payment.class).all().list().forEach(dataManager::remove);

        InsuranceProvider insuranceProvider = dataManager.create(InsuranceProvider.class);
        insuranceProvider.setName("Insurance Provider");

        createEntities.add(insuranceProvider);

        for (int i = 0; i < 10; i++) {

            Invoice invoice = dataManager.create(Invoice.class);
            invoice.setInvoiceDate(LocalDate.now());
            invoice.setInvoiceNumber(String.valueOf(i));

            createEntities.add(invoice);

            CashPayment cashPayment = dataManager.create(CashPayment.class);
            cashPayment.setPaymentDate(LocalDate.now().atStartOfDay());
            cashPayment.setInvoice(invoice);
            cashPayment.setAmount(new BigDecimal("100.0"));
            cashPayment.setAmountTendered(new BigDecimal("20.0"));
            cashPayment.setChangeGiven(new BigDecimal("20.0"));
            createEntities.add(cashPayment);

            CreditCardPayment creditCardPayment = dataManager.create(CreditCardPayment.class);
            creditCardPayment.setAmount(new BigDecimal("200.0"));
            creditCardPayment.setPaymentDate(LocalDate.now().atStartOfDay());
            creditCardPayment.setInvoice(invoice);
            creditCardPayment.setCardNumber(String.valueOf(i));
            creditCardPayment.setCardHolderName("Card Holder Name");
            createEntities.add(creditCardPayment);

            InsurancePayment insurancePayment = dataManager.create(InsurancePayment.class);
            insurancePayment.setAmount(new BigDecimal("300.0"));
            insurancePayment.setPaymentDate(LocalDate.now().atStartOfDay());
            insurancePayment.setInvoice(invoice);
            insurancePayment.setInsuranceProvider(insuranceProvider);
            insurancePayment.setPolicyNumber(String.valueOf(i));
            createEntities.add(insurancePayment);
        }

        dataManager.saveAll(createEntities);
    }

    @Nested
    class EntityManagerUsage {

        @Test
        @DisplayName("Without sorting it works - as expected")
        void test_jmixEntityManager_query() {
            // when:
            List<Payment> results = entityManager.createQuery("SELECT p FROM petclinic_Payment p", Payment.class)
                    .getResultList();

            // then:
            assertThat(results).isNotEmpty();
        }


        @Test
        @DisplayName("With sorting it does not work - NOT as expected")
        void test_jmixEntityManager_withSorting_query() {
            // when:
            List<Payment> results = entityManager.createQuery("SELECT p FROM petclinic_Payment p order by p.paymentDate", Payment.class)
                    .getResultList();

            // then:
            assertThat(results).isNotEmpty();
        }
    }

    @Nested
    class DelegateEntityManagerUsage {

        @Test
        @Transactional
        @DisplayName("Without sorting it works - as expected")
        void test_delegateEntityManager_query() {

            // given:
            EntityManager delegate = (EntityManager) entityManager.getDelegate();

            // when:
            List<Payment> results = delegate.createQuery("SELECT p FROM petclinic_Payment p", Payment.class)
                    .getResultList();

            // then:
            assertThat(results).isNotEmpty();
        }

        @Transactional
        @Test
        @DisplayName("With sorting it does not work - NOT as expected")
        void test_delegateEntityManager_withSorting_query() {

            // given:
            EntityManager delegate = (EntityManager) entityManager.getDelegate();

            // when:
            List<Payment> results = delegate.createQuery("SELECT p FROM petclinic_Payment p order by p.paymentDate", Payment.class)
                    .getResultList();

            // then:
            assertThat(results).isNotEmpty();
        }
    }

    @Nested
    class DataManagerUsage {

        @Test
        @DisplayName("Without sorting it works - as expected")
        void test_dataManagerWithoutSort_query() {
            // when:
            List<Payment> results = dataManager.load(Payment.class)
                    .all()
                    .list();

            // then:
            assertThat(results).isNotEmpty();
        }

        @Test
        @DisplayName("With sorting it does not work - NOT as expected")
        void test_dataManager_withSorting_query() {
            // when:
            List<Payment> results = dataManager.load(Payment.class)
                    .all()
                    .sort(Sort.by("paymentDate"))
                    .list();

            // then:
            assertThat(results).isNotEmpty();
        }
    }

    @AfterEach
    void tearDown() {
        createEntities.forEach(dataManager::remove);
    }
}

The test fails although it should pass:

Expecting actual not to be empty
java.lang.AssertionError: 
Expecting actual not to be empty
	at io.jmix.petclinic.user.PaymentTest$DataManagerUsage.test_dataManager_withSorting_query(PaymentTest.java:168)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:728)
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at ...

Expected Behavior

• Sorting should correctly order results across all subclasses.

Sample Project

jmix-sample-with-test-case.zip