NH-3787 - Decimal truncation in Linq ternary expression

[nhibernate-bot]

Erik jansson created an issue — 6th May 2015, 10:10:01:

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

---

Erik jansson added a comment — 2nd October 2017, 8:34:07:

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*

---

Frédéric Delaporte added a comment — 2nd October 2017, 16:36:44:

Binaries removed from test case zip.

---

Frédéric Delaporte added a comment — 2nd October 2017, 17:25:11:

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.12345

And that is a double bug:

• An optimization for SQL Server query plan cache set decimal parameters precision and scale to their "max" values in SqlClientDriver if they do not specify those themselves. And max scale is only 5. 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 (NH-4087 - Decimal truncation occurs after 5 digits #1196).
• 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.

[fredericDelaporte]

With #1196, the default scale is now 10, causing the case here to appear "solved". But tweaking the test case for requiring a scale above 10 shows it is still there. Even with a MappedAs (see #707 PR), the generated SQL is:

exec sp_executesql N'
select testentity0_.Id as id1_0_, testentity0_.UsePreviousRate as usepreviousrate2_0_,
        testentity0_.PreviousRate as previousrate3_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(28, 10))=@p0',
N'@p0 decimal(18,13)',@p0=12345.1234567890123