Why alias is not working on criteriaQuery-CodePudding

I'm facing some troubles using criteriaquery

I'm migrating a server app based on Websphere (EAR) to Spring-boot. Here I need to order a result set using a stored procedure on a DB2 server. It works perfect on the old tecnology but fails on spring-boot as the created query differs between both frameworks.

Generated old query (EAR):

SELECT
    t0.PURCHASE_ID, t0.USER, t0.CASH_AMT, .... 
    FN100_AMOUNT(ABS(t0.CASH_AMT), t1.CUR_NM_DECIMALS) AS SORTBY_AMOUNT 
FROM
    VG100_PURCHASES t0 LEFT OUTER JOIN
    VG205_CURRENCIES t1 ON t0.CURRENCY= t1.CURRENCY_ID 
WHERE (
        t0.USER = ? AND
        t0.PURCHASE_DATE = ?
) 
ORDER BY
    SORTBY_AMOUNT DESC
FETCH FIRST 10 ROWS ONLY

I'm coding it using criteriaQuery, adding the function as an Expression, setting an alias, then referencing it in the order_by clause, as follows:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> query = builder.createTupleQuery();
Root<Purchase> root = query.from(Purchase.class);    

final Path<BigInteger> pathInt = root.get(Purchase_.cashAmount);
final Path<Integer> pathDec = root.join(Purchase_.currency, JoinType.LEFT)
        .get(CurrencyEntity_.numberOfDecimals);

final Expression<BigDecimal> expression = builder.function("FN100_AMOUNT", BigDecimal.class,
        builder.abs(pathInt), pathDec)
        .as(BigDecimal.class);

Selection<BigDecimal> expressionAlias = expression.alias("SORTBY_AMOUNT");

// ... Add where clause

// Selected columns
final List<Selection<?>> selectList = ...

selectList.add(expressionAlias);
query.multiselect(selectList);
query.orderBy(builder.desc(root.get(expressionAlias.getAlias()))); 
//Order sort = builder.desc((Expression<?>) aliasSelection); // SAME ERROR

List<Tuple> resultList = em.createQuery(query).getResultList();

But the spring/hibernate generated query is as follows:

select
    t0.purchase_id as  col_0_0_,
    t0.user as  col_1_0_,
    t0.cash_amt as  col_2_0_, ...
    FN100_AMOUNT(ABS(t0.cash_amt), t1.cur_nm_decimals) as  col_17_0_
from
    VG100_PURCHASES t0 LEFT OUTER JOIN
    VG205_CURRENCIES t1 ON t0.CURRENCY= t1.CURRENCY_ID 
where
    t0.user = ? and
    t0.purchase_date = ?
order by
    FN100_AMOUNT(ABS(t0.cash_amt), t1.cur_nm_decimals) asc
fetch first 10 rows only;

This query is not accepted by DB2 as the stored procedure call is done inside the order_by clause, not allowed here as it must be done in the select clause.

If I send the final desired query it works without any problem. Old framework (OpenJPA) correctly uses aliases.

So the question is, how can I get alias working properly using criteria query?

Ask for any missing detail if needed

Thank you!

Extra info

Depencencies used in the project:

hibernate-core-5.2.14
spring-boot 2.0.0
spring-data-jpa 2.0.5

CodePudding user response：

Prior to Hibernate 6 it wasn't possible to refer to aliases in the order by clause when using the JPA Criteria API. You can only do that by using HQL/JPQL.