Why does 28.08 show up as 28.080000000000002 in DataGridView

[Jon Skeet [C# MVP]]

No, because it cannot store some values that coincide with the FLOAT type
without loss of data or introduction of error.

You=3Fve said this a number of times without a shred of proof.

Please provide a value which is conicendent with the float type which
can=3Ft be stored without introduction of error or loss of data.

Hint: 28.08 is *not* coincident with the float type, and there are ways
of storing and retrieving data other than using decimal
representations.

 

[Jon Skeet [C# MVP]]

LOFL. Now you want to tell me that when I type in "f = 28.08" in C# or "SET
@f = 28.08" that it is not an "assignment statement"? Are you serious?
Please do tell what you call it.

The statement contains an assignment and an implicit conversion. The
conversion converts 28.08 into 28.07999(etc), and that=3Fs the value
which ends up being assigned.

I have to say I'm pleasantly surprised that you feel my definition is
reasonable! Unfortunately by my definition not all data types are "exact".
It doesn't include FLOAT and REAL, as I'll show you here:

First we'll consider my definition of "exact" types, which you agree is
"pretty reasonable":

"Exact data types are those data types which can store and retrieve single
values coincident with their type without loss of data, and without the
introduction of error."

To further expand, let's consider a definition for "coincident". To
coincide with a data type, a singleton value should meet these requirements:

1) The value must be composed of digits/characters that are defined as
valid, in the proper format defined for the data type,
2) For numeric types, the scale (digits after the decimal point) of the
value needs to be less than or equal to the scale of the data type,
3) For numeric types, the difference between precision (total number of
digits) and scale of the value needs to be less than or equal to the
difference between the precision and scale of the data type,
4) For numeric types, the value must be greater than or equal to the lower
bound defined for the data type, and less than or equal to the upper bound
defined for the data type,
5) For non-numeric types, the length of the value must be less than or
equal to the length of the data type

I think we can agree that by this definition, the singleton value 28.08
coincides with the following data types:

* DECIMAL (4, 2) since it consists of valid characters and the precision and
scale are less than or equal to that defined for the data type,

* FLOAT since it consists of valid characters and the precision and scale
are less than that defined for the data type,

Nope, because they=3Fre not in the proper format defined for the type.
The definition I=3Fve provided for valid values of float (as you
requested) is in terms of a binary string. If you=3Fre going to insist on
decimal representations then *obviously* decimal will =3Fwin=3F.

Additionally, the result of the expression 1/3, by this definition, does not
coincide with any data types since it's scale is infinite and the scale for
all of these data types is finite.

It doesn=3Ft coincide with any of the types we=3Fve talked about. 0.1 (base
3) *would* be coicident with a base 3 type.

 

[Jon Skeet [C# MVP]]

And the difference is: If you store a value in an INTEGER or DECIMAL that
is coincident with that type you do not risk introducing error or data loss.
With REAL or FLOAT you risk introducing error or losing data when storing
values that are coincident with the type.

See other replies. By defining =3Fcoincident with the type=3F to require
evaluation from a string of *decimal* digits, you=3Fre shifting the
goalposts from where I at least interpreted them to be.

I had originally understood your =3Fvalues coincident with the type=3F to
be =3Fvalues which can be exactly represented by that type=3F, and that=3Fs
the definition I was considering when I agreed with your definition of
an exact data type. If you look up =3Fcoincident=3F with a Google search of
=3Fdefine: coincident=3F then the definitions given there are a lot closer
to =3Fvalues which can be exactly represented by that type=3F than =3Fworks
with a given length of decimal digits=3F (a simplification of what you
said, but the main thrust).

 

[Aaron Bertrand [SQL Server MVP]]

Really, other than awareness of how the conversion takes place (and

being able to spot by eye what value will actually be stored), what's
the difference here?

The main difference I see is that when you convert 28.08 to an INT, you are
*losing* data merely by rounding or truncating the input. IMHO, this is a
very easy concept to grasp, since we've been doing it since middle school.

When you convert 28.08 to a FLOAT, a data type most database users don't
fully comprehend, the conversion actually *adds* data. This is a much
tougher concept to grasp, and that is why you see 1800 conversations on
Google about FLOAT conversions, and very few questions about why 28.08 got
changed to 28.

When I get home from work, and I am asked what I did today, do you think I
go on about how I created a partition function against one of our OLTP
tables, which column I chose for the range and why, and the exact deltas in
i/o and throughput that were gained/lost against various workloads? Or do
you think I just say, "I made our database faster."? Since using your
definition of a FLOAT type (which I still don't agree with, but that's
another story), and adding information about base 3 and other base values
that the user couldn't care less about and probably doesn't understand, we
tend to do the same thing here: simplify, without a drawn-out lesson in
mathematical theory. So, I'm going to keep calling FLOAT and REAL
approximate data types, okay?

A

 

[Mike C#]

The statement contains an assignment and an implicit conversion. The
conversion converts 28.08 into 28.07999(etc), and that's the value
which ends up being assigned.

As mentioned before, *EVERY* numeric assignment statement contains an
implicit conversion regardless of the type. So what in the world do you
feel is so special about pointing it out for FLOAT as opposed to pointing it
out for DECIMAL or INTEGER? When you assign the INTEGER value 28 to an
INTEGER variable, as in the following example: "i = 28" or "SET @i = 28",
there is an implicit conversion as well.

I'd like to know what's special about the conversion of the literal 28.08 to
a FLOAT as compared to the conversion of the literal 28 to an INTEGER?
What's the difference?

Nope, because they're not in the proper format defined for the type.
The definition I've provided for valid values of float (as you
requested) is in terms of a binary string. If you're going to insist on
decimal representations then *obviously* decimal will "win".

The SQL Server range for FLOAT and REAL (as defined by BOL at
http://msdn2.microsoft.com/en-us/library/ms173773.aspx):

FLOAT: -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
REAL: -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38

The values for the range are given in decimal numbers.

MSDN (here http://msdn2.microsoft.com/en-us/library/b1e65aza(VS.80).aspx)
defines the C# float type as having this range:
float: +/-1.5 x 10 ^ -45 to +/- 3.4 x 10 ^ 38

Values are given by the standard in Base 10.

Further, as I'm sure you're aware, the C# Language Specification (right
here:
http://download.microsoft.com/downl...385062/csharp language specification v1.0.doc).
The SQL specification also has a specific definition for real literals which
is based on decimal digits. The C# 1.0 Language Specification pp. 47 - 48
states the following:

"Real literals are used to write values of types float, double, and
decimal."

The C# 1.0 Language specification provides the following format for Real
literals:

"real-literal:
decimal-digits . decimal-digits exponent-partopt real-type-suffixopt
.. decimal-digits exponent-partopt real-type-suffixopt
decimal-digits exponent-part real-type-suffixopt
decimal-digits real-type-suffix
exponent-part:
e signopt decimal-digits
E signopt decimal-digits
sign: one of
+ -
real-type-suffix: one of
F f D d M m"

This is sufficient proof that the intended format for FLOAT and REAL
literals is decimal; therefore I am insisting on "decimal representations"
since that is what the standards and specifications "insist on". Therefore,
to use your words, "*obviously* decimal will always win" basically because
the standards bodies define floating point literals in terms of decimal
digits.

If you want people to stop using decimal representations for FLOAT and REAL
literals, then lobby Microsoft, ANSI, IEEE and the other relevant standards
bodies. Until then, I think I've provided as precise and accurate a
real-world definition as you're likely to locate.

If you do happen to find a better real-world definition of an "exact" data
type, please feel free to share it with us.

Until then, feel free to try and keep your arguments within the bounds of
the standards and specifications for the tools with which we are concerning
ourselves here.

It doesn't coincide with any of the types we've talked about. 0.1 (base
3) *would* be coicident with a base 3 type.

As long as you agree that it doesn't coincide with any of the data types
we've talked about. SQL does not contain a Base 3 data type (neither does
C#, C++, VB, yadda yadda), so this is really a non-issue. But when you
finally convince ANSI to add a Base 3 data type to SQL or talk Microsoft
into adding one to their languages, my definition will still cover it. And
1/3 will coincide with that data type *once it is implemented*. But as I
said, back in the real world, 1/3 does not coincide with any existing
numeric data types.

 

[Erland Sommarskog]

This is a much tougher concept to grasp, and that is why you see 1800
conversations on Google about FLOAT conversions, and very few questions
about why 28.08 got changed to 28.

Nah, those questions are just as common, although for a different reason.
And, OK, may not just with the numbers 28.08 and 28. A typical result people
expect is 0.333333.... when they get 0.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Mike C#]

You've said this a number of times without a shred of proof.

28.08 is the proof.

Please provide a value which is conicendent with the float type which
can't be stored without introduction of error or loss of data.

Hint: 28.08 is *not* coincident with the float type, and there are ways
of storing and retrieving data other than using decimal
representations.

28.08, by my definition which you accepted, does coincide with the FLOAT
data type. By way of proof:

* It contains all valid digits for a FLOAT type literal;
* It is in the proper format for a FLOAT type literal as defined by the
standard;
* It's precision and scale are within the bounds for a FLOAT type literal;
* It is between the lower and upper bounds for a FLOAT type literal

At this point I have proven that 28.08 does coincide with the definition for
a FLOAT literal, including the definition of a floating point literal taken
directly from the C# language specifications (with which I'm sure you're
familiar). You have yet to prove that 28.08 does not coincide with the
definition for a FLOAT type literal; and please provide your references as I
have done.

 

[Mike C#]

See other replies. By defining "coincident with the type" to require
evaluation from a string of *decimal* digits, you're shifting the
goalposts from where I at least interpreted them to be.

To use your analogy, the "goalposts" were put in place by the standards
bodies. See my reply which includes the C# definition of a float type
literal. Your interpretation of where the "goalposts" are located is
incorrect according to the standards for the tools with which we are
concerning ourselves.

I had originally understood your "values coincident with the type" to
be "values which can be exactly represented by that type", and that's
the definition I was considering when I agreed with your definition of

If I had meant "values which can be exactly represented by that type", I
would have used those words. If you want the "main thrust" of what I said,
then we can replace the word "coincident" with my definition, so that the
full definition is the following:

"Exact data types are those data types which can store and retrieve single
values which conform to the following rules without loss of data, and
without the
introduction of error:

1) The value must be composed of digits/characters that are defined as
valid, in the proper format defined for the data type,
2) For numeric types, the scale (digits after the decimal point) of the
value needs to be less than or equal to the scale of the data type,
3) For numeric types, the difference between precision (total number of
digits) and scale of the value needs to be less than or equal to the
difference between the precision and scale of the data type,
4) For numeric types, the value must be greater than or equal to the lower
bound defined for the data type, and less than or equal to the upper bound
defined for the data type,
5) For non-numeric types, the length of the value must be less than or
equal to the length of the data type"

an exact data type. If you look up =3Fcoincident=3F with a Google search
of
=3Fdefine: coincident=3F then the definitions given there are a lot closer
to =3Fvalues which can be exactly represented by that type=3F than
=3Fworks
with a given length of decimal digits=3F (a simplification of what you
said, but the main thrust).

In fact, an oversimplification of what I said and not the main thrust at
all. If you want a simplification of what I said with the main thrust, see
above.

As I said, if you can provide a better definition, feel free. Otherwise, I
think this definition is the best you are going to achieve considering where
the standards bodies placed the "goalposts".

 

[Jon Skeet [C# MVP]]

<snip>

I'm replying to the original post as this is going to be a summary
post. I've started repeating myself, and I'm sure I'm not the only one.
I don't see any sign that anyone's convincing anyone of anything, and
as I effectively started this mess, I think it makes sense for me to be
the one to end it (if others want it ended). At least, I expect when I
stop posting the thread will probably die fairly soon - if it doesn't,
of course, that's fine. My previous attempt to leave the thread clearly
failed miserably so this time I've set my newsreader to ignore the
thread. In a week I'll "unignore" it to read any extra responses, but
by then I'm sure my sense of netiquette will overwhelm my desire to
respond In the unlikely case that anyone *really* wants to hear any
more from me, please mail me.

So, I thought I'd just summarise my position for the sake of anyone
who's interested in the future. Here we go:

1) I believe the correct answer to the OP's question of why 28.08 shows
up as 28.08000(etc)2 is that the conversion from a decimal number to a
binary number (e.g. a float) is one which can lose information. The
result of the conversion is stored exactly, but that result isn't the
same as the original number.

2) I agree with everyone else in terms of what the OP should do: use a
decimal based type.

3) I suspect we all at least mostly agree on the actual technical
aspects of what's happening, but we come from very different points of
view. My point of view tries to be "base-agnostic", viewing a number as
a number regardless of base, whereas the point of view which treats
float as "approximate" places a greater importance on decimal numbers
than other numbers.

4) I believe that the float type can accurately store and retrieve
every value it can accurately represent. This set of values is *not*
every number within the range of the minimum to the maximum values of
the type, just as integer types can't represent every number between
their minimum and maximum values - they can only represent every
*integer* between those values.

5) I believe that the business of which base literals are usually
expressed in is irrelevant to whether or not the values that a type
stores are exact or not. There will always be a conversion involved
from a decimal type to a binary type, and that conversion will always
be "lossy". I believe people should be aware of that, as well as being
aware that the value actually stored is an exact value - just not
necessarily the value they would expect. I also believe it's pretty
much as easy to explain that as to just say that float is an
approximate type. Of course, it's definitely worth pointing out that if
you want a conversion from a decimal literal which is lossless (within
the appropriate range/scale/precision) you should use a decimal type.

6) I agree that the examples I've given about base 3 values are
irrelevant to what will actually happen in real life, but I believe
they lead to a useful thought experiment which shows that float is just
as "exact" as decimal. Every type we've been talking about (float,
integer, decimal) has a set of values it can represent, and when
presented with a value from a type which can represent values not in
the set, the original value may not be representable. (It's not
*necessarily* an information-losing conversion - converting from uint
to int and back in C#, for instance, you can avoid losing data but you
can't always represent the original value.)


I dare say I've missed some points I've made throughout the rest of the
discussion, but never mind. I hope others have found the discussion as
interesting as I have.

 

[Mike C#]

See other replies. By defining =3Fcoincident with the type=3F to require
evaluation from a string of *decimal* digits, you=3Fre shifting the
goalposts from where I at least interpreted them to be.

By your last two posts, the real issue here is more obvious. The real issue
has nothing to do with the definition of "exact" or "approximate" data
types. The real issue is your misinterpretation of where the "goalposts"
are. Fortunately this issue is easily corrected by simply familiarizing
yourself with the standards. Once you understand that C#, SQL, et al., do
not allow Base 3 floating point type literals, etc., it is much easier to
determine where the "goalposts" are.

As you said, "if you're going to insist on decimal representations then
*obviously* decimal will win." As you read the standards, you will learn
that the standards define floating point literals in terms of the decimal
representations. So obviously decimal will win; because that's where the
standard sets that "goalpost".

I've noticed throughout this discussion that you tend to embrace that which
you feel supports your argument - even partial sentences - while completely
ignoring (as opposed to disproving) that which refutes your argument; even
to the point of cutting a sentence in half to remove the proper context! If
that is how you also read official standards, then it is no wonder that your
interpretation of where the "goalposts" are located is inconsistent with
those published standards.

 

[Mike C#]

I think Mr. Skeet has an excellent idea in wrapping up this thread. As
such, I'd like to provide an opposing view to the question which Mr. Skeet
implied early on; in a nutshell: "Why are FLOAT and REAL called
'approximate' types while INT, DECIMAL, etc., types called 'exact'?"

I believe that the following simple definition follows the intent of the
standards put forth by ANSI (SQL), Microsoft (C#, VB, etc.) and other
standards bodies in referring to data types based on the IEEE floating
standards as "approximate" and other data types as "exact":

"Exact data types are those data types which can store and retrieve single
values which conform to the following rules without loss of data, and
without the introduction of error:

1) The value must be composed of digits/characters that are defined as
valid, in the proper format defined for the data type,
2) For numeric types, the scale (digits after the decimal point) of the
value needs to be less than or equal to the scale of the data type,
3) For numeric types, the difference between precision (total number of
digits) and scale of the value needs to be less than or equal to the
difference between the precision and scale of the data type,
4) For numeric types, the value must be greater than or equal to the lower
bound defined for the data type, and less than or equal to the upper bound
defined for the data type,
5) For non-numeric types, the length of the value must be less than or
equal to the length of the data type"

This definition follows the intent of the standards bodies, including their
specification of floating point literals as Base 10. This definition also
accounts for truncation and rounding errors in irrational numbers and
infinitely repeating decimal values since their scale is infinitely beyond
what can be accurately represented by any built-in finite SQL, C#, or VB
types.

While Mr. Skeet also expressed an issue with calling FLOAT and REAL data
types "approximate", these labels are entirely accurate, as they succinctly
express the position that when you try to store a numeric literal that
follows the above rules, an "approximation" of that value may be stored
instead of the "exact" value.

Like Mr. Skeet, I too feel this thread has run its course, and also feel
that arguments about items that have no real world applications or relevance
(quite possibly why they were left outside the scope of the adopted
standards: i.e., "Base 3", etc.) has no real bearing on the issues at hand.
This has been an interesting discussion despite having to deal with side
issues that have no basis in reality, and the ignoring of accepted standards
as well as the intent of the standards bodies in defining these types and in
labeling them as "approximate" or "exact".