NHibernate 5 precision maximum on decimal reduced vs. NHibernate 4

[TiltonJH]

Hi,

I am facing an issue with the precision on an System.Decimal property.

I map a properties of System.Decmial (using FluentNHibernate) to decimal(38,5) in NHibernate 4.x, which works well.
Now I do the same thing with NHibernate 5.0.3, but I only get a maximum of decimal(28,5).

(I use the ExportSchema to generate to data base an SQL Server 2016.)

Now trying to store a valid System.Decimal of the value 792281625142643375935439.50335m (== decimal.MaxValue / 10000m) within the data base and I get an SqlException: Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.

So in this test one has a valid System.Decimal with 29 significant digits and it would work with decimal(29,5), but I can not set that precision either.

29 to 38 significant digits have the same space requirements within the data base (17 bytes, decimal and numeric (Transact-SQL)) so I use the maximum of 38.

I think it changed in "NH-4088 - Dialect.GetCastTypeName is buggy (#709)".

[fredericDelaporte]

This has changed for matching at most .Net decimal maximal precision, which is 28-29 (depending on cases, so 28 for being safe).

(Update: note that previously, the maximum decimal precision for SQL Server dialect was 19, but before #709, this maximum was ignored for all dialects.)

Can you explain how you use with NHibernate a decimal having a greater precision than what .Net support? We may have falsely assumed than a greater precision than what .Net support could not be used, but we need to know in which case it can be useful.

You can still circumvent the trouble for your case by deriving a custom dialect from the dialect you currently use, and re-register decimal with another maximal precision. (Do this in constructor. Or if the dialect has an overridable suitable register method, by overriding this method, calling its base then re-registering decimal in it. By example for MsSql dialects, yo can override RegisterNumericTypeMappings. The registering is RegisterColumnType(DbType.Decimal, 28, "DECIMAL($p, $s)"); for MsSql, re-register it with something else than 28.)

[fredericDelaporte]

@hazzik, since decimal in .Net can still go to 7.9*10^28, maybe we should consider limiting built-in dialect registrations maximal decimal precision to 29 instead of 28 as currently. What is your opinion?

[hazzik]

Agree

[hazzik]

Also, all dialects needs to be reviewed so the decimals are mapped consistently.

[fredericDelaporte]

Also, all dialects needs to be reviewed so the decimals are mapped consistently.

Which kind of undue discrepancy would you have in mind? Current discrepancies should only be due to some databases being more limited than a maximal precision of 28. But maybe there is undue discrepancies in the default precision/scale when not specified: unfortunately fixing them may be breaking, and would then require to be done in 6.0.

[hazzik]

Which kind of undue discrepancy would you have in mind?

One example - SybaseAse15 - This wiki says that it is 38, but we have only generic mapping.

Other examples are MySQL (<5) and SQLite.

[TiltonJH]

I think is great that you think about reviewing the default decimal precision.

However, my issue with the current version is not that the default has changed from (19,5) to (28,5), but rather that I can not explicitly map it to any value above 28. And therefore my current schema vs. a newly generated one breaks.

[fredericDelaporte]

We are also changing the maximum of 28 to 29 for accepting the greatest values .Net can handle.