#1628 numeric value out of range error on basic arithmetic

[Fabrice Daugan]

I get a data exception: numeric value out of range error (SQL Error: -3403, SQLState: 22003) with a very simple SQL queries:
This fails:
em.createQuery("SELECT :concurrency * 1.0 FROM MyTable").setParameter("concurrency", 100d).getResultList();
select ?*1.0 as col_0_0_ from my_table

This fails:
em.createQuery("SELECT :concurrency + 0.0 FROM MyTable").setParameter("concurrency", 100d).getResultList();
select ?+0.0 as col_0_0_ from my_table

This works
em.createQuery("SELECT :concurrency * 1.0 FROM MyTable").setParameter("concurrency", 10d).getResultList();

This works
em.createQuery("SELECT 100.0 * 1.0 FROM MyTable").getResultList();

When it fails, the stack is:

Caused by: org.hsqldb.HsqlException: data exception: numeric value out of range
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.types.NumberType.convertToTypeLimits(Unknown Source)
    at org.hsqldb.types.NumberType.multiply(Unknown Source)
    at org.hsqldb.ExpressionArithmetic.getValue(Unknown Source)
    at org.hsqldb.QuerySpecification.buildResult(Unknown Source)
    at org.hsqldb.QuerySpecification.getSingleResult(Unknown Source)
    at org.hsqldb.QuerySpecification.getResult(Unknown Source)
    at org.hsqldb.StatementQuery.getResult(Unknown Source)
    at org.hsqldb.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 89 more

Version used: 2.6.0

[Fred Toussi]

It is better to cast the parameter or the marker to the exact type that you want.

-- 10 digit numeric
select cast(:concurrency as numeric(10)) as col_0_0_ from MyTable

-- double precision floating point
select cast(:concurrency as double) as col_0_0_ from MyTable

If you want a DOUBLE result, you can also use this:

SELECT :concurrency + 0e0 FROM MyTable

[Fabrice Daugan]

Indeed, as workaround I added this extra operation. However the source of the issue is in the method NumberType.convertToTypeLimits.
The weird issue is the different behaviors for the query: select ?*1.0 from my_table
Fail for value 100.0 but success for value 10.0

My guess is that the scale and precision checks in convertToTypeLimits should be completed before throwing this error.

[Fred Toussi]

Scale and precision are determined at the type level. The convertToTypeLimits uses the predetermined type.

The type of the parameter in ? * 1.0 is determined to be the same as the other arithmetic operand: NUMERIC(2,1) {corrected}. In ? * 1e0 the type is DOUBLE.

Parameter type determination is based on the SQL Standard. In the case of NUMERIC usage above, it is not what one might expect.

[Fred Toussi]

I have set a minimum for the NUMERIC precision. It should now work for parameters of up to 24 digits. Fixed and committed to SVN /base/trunk