DataNucleus BigDecimal precision and scale (with HSQL at least)

[GESCONSULTOR - Óscar Bou <o....@gesconsultor.com>]

Just to notice a DN-HSQL scale/precision potential problem for others working with DN and also for Estatio (at least for tests using HSQL).


As a example, I have an Entity field declared as:

	// {{ Impact (property)
	private BigDecimal impact;

	@MemberOrder(sequence = "1.2")
	public BigDecimal getImpact() {
		return this.impact;
	}
 
	public void setImpact(final BigDecimal impact) {
		this.impact = impact;
	}

	// }}

DataNucleus creates, at least with HSQL, the table with the field type as NUMERIC(19). So no decimal digits are admitted [1].

That implies that when a record is inserted, a log entry similar to this one appears:

INSER INTO ENTITY(..., IMPACT, ....) VALUES (...., 0.5, ....)

But when that same record is retrieved, the log will show that a value of "0" is returned, instead of 0.5.

The solution is to explicitly add the scale to the field like this:


	// {{ Impact (property)
	@Column(scale=2)
	private BigDecimal impact;

	@MemberOrder(sequence = "1.2")
	public BigDecimal getImpact() {
		return this.impact;
	}
 
	public void setImpact(final BigDecimal impact) {
		this.impact = impact;
	}

	// }}



On Estatio BDD tests the tables have different number of decimal places, due to precision of BigDecimal operations.

Sure most of you already know, but there is a good tutorial on BigDecimals on [2].

Just by annotating with @Column(scale=xxx) and setting also the scale to the result of a BigDecimal operation with setScale(scale, roundingMode) [3] has been enough for our tests.

So hope this helps (and many thanks for sharing the project),

Oscar.

[1] http://hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html#sgc_numeric_types

[2] http://www.opentaps.org/docs/index.php/How_to_Use_Java_BigDecimal:_A_Tutorial

[3] http://www.tutorialspoint.com/java/math/bigdecimal_setscale_rm_roundingmode.htm

Re: DataNucleus BigDecimal precision and scale (with HSQL at least)

[Dan Haywood <da...@haywood-associates.co.uk>]

thanks for this, Oscar... I'll document it somewhere on the website so it's
a bit more discoverable.

Dan



On 24 July 2013 22:44, GESCONSULTOR - Óscar Bou <o....@gesconsultor.com>wrote:

>
> Just to notice a DN-HSQL scale/precision potential problem for others
> working with DN and also for Estatio (at least for tests using HSQL).
>
>
> As a example, I have an Entity field declared as:
>
>         // {{ Impact (property)
>         private BigDecimal impact;
>
>         @MemberOrder(sequence = "1.2")
>         public BigDecimal getImpact() {
>                 return this.impact;
>         }
>
>         public void setImpact(final BigDecimal impact) {
>                 this.impact = impact;
>         }
>
>         // }}
>
> DataNucleus creates, at least with HSQL, the table with the field type as
> NUMERIC(19). So no decimal digits are admitted [1].
>
> That implies that when a record is inserted, a log entry similar to this
> one appears:
>
> INSER INTO ENTITY(..., IMPACT, ....) VALUES (...., 0.5, ....)
>
> But when that same record is retrieved, the log will show that a value of
> "0" is returned, instead of 0.5.
>
> The solution is to explicitly add the scale to the field like this:
>
>
>         // {{ Impact (property)
>         @Column(scale=2)
>         private BigDecimal impact;
>
>         @MemberOrder(sequence = "1.2")
>         public BigDecimal getImpact() {
>                 return this.impact;
>         }
>
>         public void setImpact(final BigDecimal impact) {
>                 this.impact = impact;
>         }
>
>         // }}
>
>
>
> On Estatio BDD tests the tables have different number of decimal places,
> due to precision of BigDecimal operations.
>
> Sure most of you already know, but there is a good tutorial on BigDecimals
> on [2].
>
> Just by annotating with @Column(scale=xxx) and setting also the scale to
> the result of a BigDecimal operation with setScale(scale, roundingMode) [3]
> has been enough for our tests.
>
> So hope this helps (and many thanks for sharing the project),
>
> Oscar.
>
> [1]
> http://hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html#sgc_numeric_types
>
> [2]
> http://www.opentaps.org/docs/index.php/How_to_Use_Java_BigDecimal:_A_Tutorial
>
> [3]
> http://www.tutorialspoint.com/java/math/bigdecimal_setscale_rm_roundingmode.htm
>
>