Hi,

Thankyou for this report.

I realize the next statement is going to seem slightly wacky, but this behavior is, in fact, "by-design" ... here goes:

Think of your numerics as fixed-size boxes, holding 38 decimal digits (with another box, off-to-the-side, that remembers how far along the decimal point is). And 38 is also the bigger numeric we support.

Now, 1.2345679 looks like this: 00000000000000000000000000000123456789 ; 8
where the trailing 8 is our scaling box, off-to-the-side.

But floor on this value returns a numeric(38,0), so it looks like:
00000000000000000000000000000000000001 ; 0

In order to subtract one value from t'other, we conceptually need to align their decimal points. If we were to do this to the floor value, we'd end up with:
00000000000000000000000000000100000000 ; 8

But hold on a second! We just shifted-out, and lost, 8 decimal digits from our fixed-size, 38-wide, box. [In our case, we shifted out zeroes - but we don't know that in the general case].

We're stuck with boxes 38 digits wide. So, either way, we don't have enough space to peform this calculation at full accuracy (that would require a wider box, with 46 digits, right?).

So our algorithm follows common-sense, and prefers to throw away fractions than integers (since they're smaller). So we end up instead by right-shifting our original value to yield:

00000000000000000000000000000000000001 ; 0

(the 23456789 fell into the bit-bucket on the right, and are gone forever)

Finally, perform the subtraction, and we get zero. Voila!

I admit this behavior is non-intuitive. Why did floor not keep the numeric(p,s) of its input? - the original designer probably reckoned he would maximize accuracy here, since floor is guaranteed to have no fractional part - by definition.

A workaround is to cast the floor result back 'up' to numeric(38,8), like:

@x - CAST(floor(@x) as numeric(38,8))

[I realize this explanation doesn't address precisely the example you gave, but hopefully tells enough of the internals to make those results clear too].

This was an interesting one to track down :-)

Thanks,

Jim Hogg