Decimal value with more than 5 decimal places gets truncated when doing a conditional select with linq
Description
Environment
Attachments
is related to
Activity
Still failing with a variation:
[Test]
public void TestLinqQueryOnExpression()
{
using (var s = OpenSession())
using (var t = s.BeginTransaction())
{
var queryResult = s
.Query<TestEntity>()
.Where(e => (e.UsePreviousRate ? e.PreviousRate : e.Rate) == _testRate)
.ToList();
Assert.That(queryResult.Count, Is.EqualTo(1));
Assert.That(queryResult[0].PreviousRate, Is.EqualTo(_testRate));
t.Commit();
}
}Yields:
exec sp_executesql
N'select
testentity0_.Id as id1_0_,
testentity0_.UsePreviousRate as usepre2_0_,
testentity0_.PreviousRate as previo3_0_,
testentity0_.Rate as rate4_0_
from TestEntity testentity0_
where cast(
case when testentity0_.UsePreviousRate=1
then testentity0_.PreviousRate
else testentity0_.Rate end
as DECIMAL(19,5)) = @p0',
N'@p0 decimal(28,5)', @p0=12345.12345And that is a double bug:
An optimization for SQL Server query plan cache set decimal parameters precision and scale to their "max" values in
SqlClientDriverif they do not specify those themselves. And max scale is only5. Any way, max scale is non-sens, since it is indeed equal to precision, in which case their are no more digits before the dot. It is more a default scale. And such a default scale is quite a nasty thing. It causes the value to be truncated (and not rounded) to 5 digits after the dot, when the query parsing was not able to infer its precision/scale from compared entity property (due to the expression). This issue has been isolated in NH-4087.An undue cast to the NHibernate default decimal (
decimal(19, 5)) is done on the expression, causing a round. So this undue cast may compensate the first bug when truncation and rounding are equivalent... But fixing the first bug will leave us with the second anyway.
Tests currently pushed here, I may PR that later.
Binaries removed from test case zip.
This bug has been fixed in NH 4.1.1.4000.
Generated SQL
select testentity0_.UsePreviousRate as col_0_0_, testentity0_.Rate as col_1_0_, testentity0_.PreviousRate as col_2_0_ from TestEntity testentity0_Details
Details
Assignee
Reporter
Labels
Components
Fix versions
Affects versions
Priority
Major
My table has two decimal values with precision 15 and scale 9.
public partial class TestEntityMap : ClassMap<TestEntity> { public TestEntityMap() { Id(x => x.Id).GeneratedBy.Identity(); Map(x => x.UsePreviousRate).Not.Nullable(); Map(x => x.Rate).Precision(15).Scale(9).Not.Nullable(); Map(x => x.PreviousRate).Precision(15).Scale(9).Not.Nullable(); } }In my query i want to select which value to select based on a boolean (UsePreviousRate).
var queryResult = (from test in _session.Query<TestEntity>() select new RateDto { Rate = test.UsePreviousRate ? test.PreviousRate : test.Rate }).ToList();If PreviousRate is 12345.123456789 i expect the property Rate in RateDto to be the same but the value is rounded to 5 decimals (12345.12346)
Generated SQL looks like
select cast(case when testentity0_.UsePreviousRate=1 then testentity0_.PreviousRate else testentity0_.Rate end as DECIMAL(19,5)) as col_0_0_ from [TestEntity] testentity0_The same query with queryover and conditional projection returns expected value
var query = _session.QueryOver<TestEntity>(() => testEntity) .Select( Projections.Alias(Projections.Conditional(Restrictions.Eq(Projections.Property(() => testEntity.UsePreviousRate), true), Projections.Property(() => testEntity.PreviousRate), Projections.Property(() => testEntity.Rate)), "Rate") .WithAlias(() => rateDto.Rate)); var queryResult = query.TransformUsing(Transformers.AliasToBean<RateDto>()).List<RateDto>(); Assert.AreEqual(12345.123456789M, queryResult[0].Rate);Generated sql
exec sp_executesql N'SELECT (case when this_.UsePreviousRate = @p0 then this_.PreviousRate else this_.Rate end) as y0_ FROM [TestEntity] this_',N'@p0 bit',@p0=1