"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message

news:MP******** **************@ msnews.microsof t.com...

Mike C# <xy*@xyz.comwro te:
>"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message

The SQL Decimal and Numeric data types are designed to store every single

last number in the range of valid values in an *EXACT* representation.

1/3 is in the range, but can't be stored exactly. Which part of that

statement do you disagree with?

Just as an example: Consider the SQL NUMERIC(2, 1) data type. 1/3 is

*absolutely not* a member of the set of valid values for this type. The

complete set of valid values for this type is:

{ -9.9, -9.8, -9.7, -9.6, -9.5, -9.4, -9.3, -9.2, -9.1, -9.0, -8.9, -8.8, -8.7,

-8.6, -8.5, -8.4, -8.3, -8.2, -8.1, -8.0, -7.9, -7.8, -7.7, -7.6, -7.5, -7.4,

-7.3, -7.2, -7.1, -7.0, -6.9, -6.8, -6.7, -6.6, -6.5, -6.4, -6.3, -6.2, -6.1,

-6.0, -5.9, -5.8, -5.7, -5.6, -5.5, -5.4, -5.3, -5.2, -5.1, -5.0, -4.9, -4.8,

-4.7, -4.6, -4.5, -4.4, -4.3, -4.2, -4.1, -4.0, -3.9, -3.8, -3.7, -3.6, -3.5,

-3.4, -3.3, -3.2, -3.1, -3.0, -2.9, -2.8, -2.7, -2.6, -2.5, -2.4, -2.3, -2.2,

-2.1, -2.0, -1.9, -1.8, -1.7, -1.6, -1.5, -1.4, -1.3, -1.2, -1.1, -1.0, -0.9,

-0.8, -0.7, -0.6, -0.5, -0.4, -0.3, -0.2, -0.1, 0.0, 0.1, 0.2, 0.3, 0.4,

0.5, 0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9,

2.0, 2.1, 2.2, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8, 2.9, 3.0, 3.1, 3.2, 3.3, 3.4,

3.5, 3.6, 3.7, 3.8, 3.9, 4.0, 4.1, 4.2, 4.3, 4.4, 4.5, 4.6, 4.7, 4.8, 4.9,

5.0, 5.1, 5.2, 5.3, 5.4, 5.5, 5.6, 5.7, 5.8, 5.9, 6.0, 6.1, 6.2, 6.3, 6.4,

6.5, 6.6, 6.7, 6.8, 6.9, 7.0, 7.1, 7.2, 7.3, 7.4, 7.5, 7.6, 7.7, 7.8, 7.9,

8.0, 8.1, 8.2, 8.3, 8.4, 8.5, 8.6, 8.7, 8.8, 8.9, 9.0, 9.1, 9.2, 9.3, 9.4,

9.5, 9.6, 9.7, 9.8, 9.9 }

Likewise with NUMERIC (10, 2), DECIMAL (24, 18), or any other valid

precision and scale settings for the NUMERIC and DECIMAL types.

The fact that you cannot accurately store a value that is not a member of

the set of valid values for a type is of no significance. Since by

definition 1/3 is not a member of the set of valid values for the DECIMAL or

NUMERIC types, why do you find it surprising that 1/3 cannot be stored

exactly using these types?

>Are we talking about information loss from the simple act of converting

from

one base to another, which is what the Decimal and Numeric types address;

or

are you talking about information loss from performing operations on data

which goes back to your 1/3 example earlier?

They're the same thing. Converting 0.1 base 3 (i.e. 1/3) into a decimal

*is* a conversion from one base to another, and the Decimal and Numeric

types *do not* store that number exactly.

No, they are two different arguments. As explained, 0.1 is a non-repeating

decimal, whereas 1/3 is an infinitely repeating decimal which cannot be

represented exactly in decimal form, period. The fact that an infinitely

repeating decimal cannot be represented *exactly* using a DECIMAL or NUMERIC

type should come as no surprise to you or anyone else for that matter. The

fact that a non-repeating decimal with a scale of 1 or 2 (just one or two

digits after the decimal point) cannot be stored *exactly* using FLOAT and

REAL data types comes as a surprise to many people.

>You need to present your arguments about this to ANSI, since these are

the

definitions they put forth.

I'm happy to disagree with any number of people, if what they're saying

doesn't make sense.

That's perfectly fine; however, as long as the standard calls INT, DECIMAL,

NUMERIC, etc., "exact" numeric data types and it calls FLOAT and REAL

"approximat e" numeric data types, you may as well prepare yourself to

disagree with every SQL database programmer and designer in the world.

>They can store *every single valid value in their defined set of values*

in

an exact representation. With Numeric and Decimal types precision and

scale

that you specify define the valid set of values.

In that case the same can be said for Float. Every Float value is an

exact value - you can write down the *exact* binary string it

represents, and even convert it into an exact decimal representation.

Sounds great! Prove it: Store 28.08 as a FLOAT value and write doen the

*exact* binary string it represents. Then convert it into an exact decimal

representation. Here's something to get you started:

DECLARE @r FLOAT

SELECT @r = 28.08

SELECT @r

--Result in QA: 28.079999999999 998

>You might want to re-read that, because you seem to have picked out just

enough of a single sentence to support your argument, but completely

ignored

the part that doesn't. Here's the full sentence for you (the emphasis is

mine):

"WHEN MAXIMUM PRECISION IS USED, valid values are from - 10^38 +1 through

10^38 - 1."

That also defines a default scale of "0". If, however, you define a

different precision and/or scale, the valid set of values changes.

I don't think that makes any difference - when maximum precision is

used, the range of valid values is given in terms of a minimum and a

maximum, and not every value in that range is exactly representable. If

you're going to treat it as "every value that can be exactly

represented can be exactly represented" then as I say, the same is true

for Float.

Of course you don't think it makes any difference, or you would have quoted

the full quote instead of just the last part that appears to substantiate

your argument. The point is that when the precision and scale are turned

down the set of valid values changes for a given NUMERIC or DECIMAL data

type declaration. See the example above for NUMERIC (2, 1) for an example.

The range of valid values for NUMERIC (2, 1) is nowhere near -10^38 + 1 nor

+10^38 - 1. The minimum and maximum values in this case are -9.9 and +9.9,

and the set of valid values includes only decimal numbers that consist of

two digits where one of those digits is after the decimal point. Your

example of 1/3 does not fit in that set since it has more than 1 digit after

the decimal point (in fact, it has infinitely more digits after the decimal

point).

>Of course I'm biased to decimal numbers, as are most of the people here

who

were born with 10 fingers and 10 toes. Now let's talk about your bias

against irrational numbers, since you seem to be tip-toeing all around

them

here.

No, I'm recognising the mathematical reality that the existence (and

value) of a number doesn't depend on what base you represent it in.

The mathematical reality is that very few people through the course of human

history have needed to use Base 3. The reality is that NUMERIC and DECIMAL

types do not represent every single number "in existence", nor every single

number between the minimum and maximum values specified by the precision and

scale set by the user. The real reality is that the user defines the set of

*valid values* as well as implicit upper and lower bounds when they declare

a DECIMAL or NUMERIC type. The reality is that every single *valid value*

in this set can be stored *exactly*; that is, you get out of it exactly what

you put into it.

>There is no "fraction" data type in SQL, which you might consider a

shortcoming of the language. There is also no "fraction" data type built

into C#, C++, or most other languages. The reason for that might be a

huge

bias or conspiracy against vulgar fractions, or it might be the lack of a

useful vinculum in the original ASCII character set, or who it might just

be

that most people just don't find it all that useful.

I wasn't talking about usefulness, I was talking about existence.

Suggesting that 1/3 isn't a number is silly, IMO.

1/3 is a number, and I for one did not say otherwise. Now let me tell you

what is really silly (IMO):

- Suggesting that because you can't store an exact representation of the

infinitely repeating fraction 1/3 in a NUMERIC or DECIMAL (or any data type

for that matter) makes it inexact is ludicrous. As mentioned above, 1/3 is

not defined as a *valid value* for any NUMERIC or DECIMAL precision and

scale.

- Saying that trying to store the infinitely repeating decimal number 1/3

and getting less than an infinitely repeating decimal number is *the same

thing* as storing the non-repeating decimal number 1/10 and getting

something other than 0.1 back as a result is absolutely ridiculous.

Maybe a couple of examples will help, but let's get away from numbers and

bases for a minute and talk about character data (so we don't have to worry

about my decimal bias, or your love for "Base 3"):

0. Consider the VARCHAR data type on SQL 2000. It maxes out at 8,000

characters. You absolutely cannot store an infinite number of 'A'

characters in a VARCHAR(8000); therefore it is not exact by your reasoning.

By your reasoning, the only "exact" representation would be one in which you

could store every possible combination of characters ever, in an infinitely

long character string; even though a character string with 9,000 characters

is not a member of the set of valid values for a VARCHAR(8000). Likewise

you cannot store an infinitely repeating decimal (like 1/3) which, by your

reasoning, makes the NUMERIC and DECIMAL data types "inexact" as well;

despite the fact that 1/3 is not a member of the set of *valid values*

defined for the type.

1. Consider an imaginary data type similar to VARCHAR. We'll call it

APPROXIMATE VARCHAR. The APPROXIMATE VARCHAR will store basically the

strings we pass it; however, it may lose some accuracy in the least

significant characters (right-hand side of the string) due to the way it is

stored. If you were to store 8000 'A' characters in an APPROXIMATE

VARCHAR(8000), but then select the value back you might get 7,999 'A'

characters and one 'B' character. This might be no big deal if you really

only ever need to use the first 10 characters anyway, which appears to be

IEEE's rational behind their representation of floating point numbers. The

analogy is, of course, storing 28.08 in a FLOAT and retrieving 28.07999...

or 28.080000002 or some such number that *approximates* the exact value you

stored originally.

By your reasoning, both of these situations are the same: i.e., trying to

store an infinite number of characters in a data type that has a

well-defined limitation on the number of characters that can be stored *is

the same thing* as storing an approximation of the character string you pass

in. This is pretty silly, IMO.

>Regardless of the reason, 1/10 is not a repeating decimal in decimal

(yes,

I'm showing my "bias" for decimal again). It has a finite representation

in

decimal. 1/3 is a repeating decimal in decimal. Since it's decimal

representati on repeats infinitely, it's impossible to store it exactly

anywhere. However, with regards to Decimal and Numeric data types in

SQL,

"0.33333" is not a member of the set of valid values for a DECIMAL (10,

2)

or a NUMERIC (10, 3) [for instance].

Indeed.

Glad we agree on something.

>The definitions of SQL DECIMAL and NUMERIC specifically allow you to

define

the precision and scale. By doing so you define the number of total

digits

and the number of digits after the decimal point. Any numbers that are

in

the set of valid values for the precision and scale that you define will

be

stored *exactly*. Any numbers that are not in the set of valid values

for

that precision and scale will not be stored exactly. SQL REAL and FLOAT

types do not come with this guarantee.

There's no reason why they shouldn't - why would it not be able to

guarantees tha the number is essentially reproducible, which is all I

can understand that we're talking about? The binary string "0.010101"

represents an exact number and no others.

SQL REAL and FLOAT types are based on the IEEE standard which guarantees

that it will store an approximation of a value you feed to it. Since we're

dealing with binary computers (as opposed to analog computers) we can expect

that once our exact value is converted to an approximation the internal

representation *of that approximation* will be exact (1's and 0's).

However, the exact value you feed it is still converted to an approximation,

as shown by the OP. NUMERIC and DECIMAL types do not suffer from this

approximation. They store only values from the set of *valid values*

defined for their precision and scale, and they store them exactly. In your

example of 1/3, storing it as a NUMERIC (2, 1) results in "0.3" being

stored, and that rounding occurs during casting, not during the storage

operation. Basically it is attempting to cast 1/3, which is not a member of

the valid set of numbers for NUMERIC(2, 1), to a valid member of that set.

It has nothing to do with the exactness with which "0.3" is actually stored;

whereas that is the entire issue with FLOAT and REAL.

>Quite simply because with NUMERIC and DECIMAL types I can define the

precision and scale, and every valid value in that range will be stored

*EXACTLY*. REAL and FLOAT come with pre-defined ranges, but have no

guarantees concerning how the exactness with which your numeric data will

be

stored. As we saw, even a number as simple as 28.08 is stored as an

approximatio n of 28.08 (or 2808/100 if you prefer your fractions).

Whereas

with a NUMERIC (10, 2) it would be stored as -- well, "28.08" *EXACTLY*.

Do you regard 28.08 as in the "range" for FLOAT? It seems unfair to

demand that it is while saying that 1/3 isn't in the "RANGE" for

NUMERIC, but that's what you've basically got to do in order to claim

that FLOAT is "approximat e" but DECIMAL is "exact".

Life is not fair; however, your argument here is lacking.

FLOAT is a floating point type and 28.08 is a non-repeating decimal. The

fact that FLOAT cannot accurately store 28.08 exactly but DECIMAL and

NUMERIC can proves the point very well.

DECIMAL and NUMERIC are fixed-point types with a guarantee of storage

"exactness" . As mentioned, you define the precision (total number of

digits) and scale (number of digits after the decimal point) for DECIMAL and

NUMERIC, and in the process you implicitly define the upper and lower

bounds, as well as the set of *valid values* for the DECIMAL and NUMERIC

types. The guarantee is that any value that is a member of the set of

*valid values* for those types will be stored exactly. If you want to store

1/3 as DECIMAL or NUMERIC you have to cast it (implicitly or explicitly) to

a member of the set of *valid values* defined for the precision and scale

you have chosen. 1/3 is not a *valid value* from the set of *valid values*

for any NUMERIC or DECIMAL type (since they all have a finite scale).

Consider a DECIMAL (5, 4). You can store only 4 digits after the decimal

point; therefore you cannot store an infinite number of 3's after the

decimal point. Therefore 1/3 is not in the set of valid values for that

type (see previous for example).

Personally I don't find your argument about not being able to store an exact

representation of an infinitely repeating decimal on machines with a finite

amount of memory to be all that compelling a reason to call DECIMAL and

NUMERIC data types "inexact".