HQL generate wrong cast

Question

Having a cast like this:

SELECT
    CAST(a * 100000000 as big_integer)
FROM
    Amount
WHERE
    id = :id

Generate this SQL:

prepareStatement(
   "select cast(amount0_.\"a\"*100000000 as numeric(19, 2)) from \"AMOUNT\" where amount0_.\"id\"=?");

Question: Why two decimal places though?

There are some other problems

cast as Long in HQL - int8 in SQL (but long is bigger than int8)
cast as Double in HQL - float8 in SQL (but double is more precise than float8)

Using:

Hibernate-core-jakarta 5.6.15.Final
Postgres jdbc 42.7.4

Good question, but as a note that support for CAST() is up to the JDBC driver. Why do you need to do this explicit cast? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 8, 2024 at 9:54
@TimBiegeleisen I do not think the jdbc-driver is responsible for two digits precision. The SQL is generated by hibernate. The hibernate dialect might more likely have an impact to the SQL generation. — Grim
– Grim, Commented Dec 8, 2024 at 19:41

Lajos Arpad · Accepted Answer · 2024-12-08 10:24:20Z

A similar question was asked here. The solution boiled down to something like

    @Override
    public void contributeTypes(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry);
        typeContributions.getTypeConfiguration().getDdlTypeRegistry().addDescriptor(
            new DdlTypeImpl(SqlTypes.NUMERIC, "numeric", this)
        );

and casting to BigDecimal. The reasoning, as Beikov presented was

You can register a custom DdlType into DdlTypeRegistry in a TypeContributor for the NUMERIC/DECIMAL type code. The custom DdlType can then override getCastTypeName to return just numeric if length and precision are null.

This would be a nice improvement for Hibernate ORM though, so if you could create a Jira improvement ticket in our issue tracker and possibly also provide a PR with the improvement, chances are good that we will accept this

Collectives™ on Stack Overflow

HQL generate wrong cast

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related