DECIMAL questions

Is it correct that the data-type DECIMAL must be “signed”?

If so, does the negative sign (-) take up one of the spaces?

For example, if you had DECIMAL(5,2) what would be the range of numbers?

TomTees

Let’s try another example…

The number “0.00012345” has five Significant Digits, and so they should all be retained in a variable with data-type DECIMAL(5,2) if the “5” truly represents “Significant Digits”…

Let’s insert the value…


INSERT INTO uhoh3
(format_orig, format_decimal)
VALUES
((0.00012345), (0.00012345))
;

Let’s look at what MySQL retains and displays…


mysql> SELECT * FROM uhoh3;
+----+-------------+----------------+
| id | format_orig | format_decimal |
+----+-------------+----------------+
|  1 |           1 |           1.00 | 
|  2 |           1 |           1.00 | 
|  3 |       1.004 |           1.00 | 
|  4 |       1.005 |           1.01 | 
|  5 |       1.006 |           1.01 | 
|  6 |          -1 |          -1.00 | 
|  7 |      -1.005 |          -1.01 | 
|  8 |        9.37 |           9.37 | 
|  9 |       99.37 |          99.37 | 
| 10 |      999.37 |         999.37 | 
| 11 |     9999.37 |         999.99 | 
| 12 |     99999.4 |         999.99 | 
| 13 |      999999 |         999.99 | 
| 14 |       9e+06 |         999.99 | 
| 15 |   0.0001223 |           0.00 | 
[B]| 16 |  0.00012345 |           0.00 | 
[/B]+----+-------------+----------------+
16 rows in set (0.01 sec)

mysql> 

What do you know?! :scratch:

Looks like MySQL does NOT capture 5 Significant Digits afterall…

(If it did, “0.00012345” would be stored and displayed as “123.45 X 10^-6”)

I think that is pretty convincing.

Proof once again that MySQL does a poor job with technical writing.

(Now you know why I spend most of my time at Sitepoint - where people are coherent - and not over there…)

Sincerely,

TomTees

P.S. r937, I hope you are satisifed that I have done my “due diligence” for the day?! :wink:

Fegall,

Sorry to say, but the way the MySQL Manual uses the term defies any accepted definition of how Significant Digits work.

For instance, the number “0.000122300” has six Significant Digits. (I took this number from Wikipedia, but their example is how I was taught in college as well.)

After running this…


INSERT INTO uhoh3
(format_orig, format_decimal) 
VALUES
((0.000122300), (0.000122300))
;

the following results were displayed…


mysql> SELECT * FROM uhoh3;
+----+-------------+----------------+
| id | format_orig | format_decimal |
+----+-------------+----------------+
|  1 |           1 |           1.00 | 
|  2 |           1 |           1.00 | 
|  3 |       1.004 |           1.00 | 
|  4 |       1.005 |           1.01 | 
|  5 |       1.006 |           1.01 | 
|  6 |          -1 |          -1.00 | 
|  7 |      -1.005 |          -1.01 | 
|  8 |        9.37 |           9.37 | 
|  9 |       99.37 |          99.37 | 
| 10 |      999.37 |         999.37 | 
| 11 |     9999.37 |         999.99 | 
| 12 |     99999.4 |         999.99 | 
| 13 |      999999 |         999.99 | 
| 14 |       9e+06 |         999.99 | 
[B]| 15 |   0.0001223 |           0.00 | 
[/B]+----+-------------+----------------+
15 rows in set (0.00 sec)

mysql> 

Notice how in Row 15, MySQL’s DECIMAL data-type chops off all Significant Digits?

Bottom-line is that the tech writers of the MySQL Manual should reconsider their usage of terms like “Significant Digits” because what they mean, and how the DECIMAL data-type works, and how the International Scientific Community uses the concept of “Significant Digits” are completely different!!

I now understand what they were trying to communicate, but they did it in a very misleading way, and that is dangerous when it comes to our profession!!

(If you want a black coffee don’t ask the waitress for a strawberry milkshake, ya know?!)

TomTees

No - in this case what it means by significant digits is different from what you expected. As there is a decimal point in a specified position within the significant digits the specific significant digits it grabs in this case are those in the appropriate positions relative to the decimal point rather than those at the front of the number. The significant digits are not necessarily the most significant ones, just those in the specific position within the number.

The manual is unclear because it doesn’t explain which digits are the significant ones but it isn’t wrong.

Hey r937, I would like to think I have lots of initiative and try to do my part, however, a few thoughts…

1.) MySQL’s documentation was written by flunkies

2.) Places like Sitepoint exist to get to the answer quicker by asking others

3.) If I wasn’t unemployed and on the verge of ending up on the street, I’d gladly take several months and read up on as much as I could before coming here. However my time is limited in trying to find work and what little time is left trying to get my website built. As such, I am more dependent on getting help from gurus like yourself.

But if it is any consolation, I wouldn’t have spent most of Sunday reading up on Foreign Keys, Constraints, and Index if I didn’t have a genuine desire to learn more and become a fluent developer with PHP and MySQL.

Sincerely,

TomTees

Aha, but da manual does claim that, r937…

From here

When declaring a DECIMAL or NUMERIC column, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

DECIMAL(5,2) stores 5 total digits, of which 2 are to the right of the decimal point, which leaves only 3 to the left

So, I was a good sport and did my part by reading MySQL’s documentation, however, the documentation is not only unclear, this time is is flat out WRONG!!!

That is easy enough to understand.

Maybe da manual - which you kept sending me to today - should say that…

(Or, you could have saved me most of my day and just told me that first thing this morning…) :wink:

TomTees

indeed :eyes:

you seem to be pretty hung up on the word “significant”

the value 0.00012345 has ~no~ significant digits to the left of the decimal point

it actually has as many zeros as you wish to write to the left of the decimal point

but the zeroes, in that case, aren’t significant

do you like this interpretation of the word “significant” any better?

just remember, DECIMAL(5,2) means five digits overall, two digits to the right of the decimal point

if you try to store 0.00012345 in there, you’ll end up with 000.00

That’s why I think the documentation is misleading. Try it and you’ll find that the limits are -999.99 and 999.99.

Like I said, if you have (5, 2) anything larger than 999.99 (as 9,999.37 is) will be clipped to 999.99.

But I thought the negative was included?

Should it be from -99.99 to 999.99 ??? :confused:

As regards the FLOAT column this is because of rounding and precision.
Because the DECIMAL type stores the number by character rather than just a floating point value, you are guaranteed not to get any rounding errors, just clipping when you exceed the range you’ve declared.

Read the descriptions of each numeric type, in particular the differences between DECIMAL, FLOAT and DOUBLE:
http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html

But take a look at row #11.

How do you get from FLOAT 9999.37 to DECIMAL 999.99 ??

And what about these two…

| 12 | 99999.4 | 999.99 |
| 13 | 999999 | 999.99 |

TomTees

I actually have been reading the MySQL Manual, however since it looks like it was written and formatted by people who struggled in remedial English classes it isn’t so easy to follow.

(I can’t think of a more poorly executed website when it comes to effective communication of information than what the guys (and gals) over at MySQL are doing…) :rolleyes:

this is actually a pretty good question, as it allows me to suggest that you set up a quick test to insert what you might think are values outside that range

CREATE TABLE uhoh ( foo DECIMAL(5,2) );
INSERT INTO uhoh VALUES
 (1        )
,(1.0      )
,(1.004    )
,(1.005    )
,(1.006    )
,(-1       )
,(-1.005   )
,(9.37     )
,(99.37    )
,(999.37   )
,(9999.37  )
,(99999.37 )
,(999999.37)
;

if you have any questions about this test, just ask

:slight_smile:

I spent all morning trying to figure out how to get the SQL to work from the mysql command line, but changed things to make this more educational.

Here is the table I created…


CREATE TABLE uhoh3 (
	id INT NOT NULL AUTO_INCREMENT,
	format_orig FLOAT,
	format_decimal DECIMAL(5,2),
	PRIMARY KEY (id)
);

Here is your data in a modified way…


INSERT INTO uhoh3
(format_orig, format_decimal) 
VALUES
((1        ), (1        )), 
((1.0      ), (1.0      )), 
((1.004    ), (1.004    )),
((1.005    ), (1.005    )),
((1.006    ), (1.006    )), 
((-1       ), (-1       )), 
((-1.005   ), (-1.005   )), 
((9.37     ), (9.37     )), 
((99.37    ), (99.37    )), 
((999.37   ), (999.37   )), 
((9999.37  ), (9999.37  )), 
((99999.37 ), (99999.37 )), 
((999999.37), (999999.37))
;

Here are the results that mysql spit out…


mysql> SELECT * FROM uhoh3;
+----+-------------+----------------+
| id | format_orig | format_decimal |
+----+-------------+----------------+
|  1 |           1 |           1.00 | 
|  2 |           1 |           1.00 | 
|  3 |       1.004 |           1.00 | 
|  4 |       1.005 |           1.01 | 
|  5 |       1.006 |           1.01 | 
|  6 |          -1 |          -1.00 | 
|  7 |      -1.005 |          -1.01 | 
|  8 |        9.37 |           9.37 | 
|  9 |       99.37 |          99.37 | 
| 10 |      999.37 |         999.37 | 
[COLOR="Red"][B]| 11 |     9999.37 |         999.99 |[/B] 
| 12 |     99999.4 |         999.99 | 
| 13 |      999999 |         999.99 | 
[/COLOR]+----+-------------+----------------+
13 rows in set (0.00 sec)

I guess I don’t understand what is going on. (Especially in the RED and RED BOLDED areas.

TomTees

i suppose so, because i don’t think you can make it unsigned

allow me once again to introduce you to da manual:[indent]MySQL stores DECIMAL and NUMERIC values as strings, rather than as binary floating-point numbers, in order to preserve the decimal precision of those values. One character is used for each digit of the value, the decimal point (if the scale is greater than 0), and the `-’ sign (for negative numbers). If the scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part. [/indent]
i certainly don’t mind continuing to look stuff up for you, if you want to keep asking :wink:

this is actually a pretty good question, as it allows me to suggest that you set up a quick test to insert what you might think are values outside that range

CREATE TABLE uhoh ( foo DECIMAL(5,2) );
INSERT INTO uhoh VALUES
 (1        )
,(1.0      )
,(1.004    )
,(1.005    )
,(1.006    )
,(-1       )
,(-1.005   )
,(9.37     )
,(99.37    )
,(999.37   )
,(9999.37  )
,(99999.37 )
,(999999.37)
;

if you have any questions about this test, just ask

:slight_smile:

well, there’s the problem – DECIMAL(5,2) doesn’t claim to store “five significant digits”

DECIMAL(5,2) stores 5 total digits, of which 2 are to the right of the decimal point, which leaves only 3 to the left

I always think of it as the total number of digits and then how many of those are used for the decimal part. So, (5, 2) would accommodate -999.99 to 999.99. Where you are trying to store values outside that range, they are being clipped, so whether it’s 1,000.00 or 9,999.99 or 99,999.99 you will get a maximum value of 999.99 stored for a (5, 2) definition (and similarly for large negative values).

As regards the FLOAT column this is because of rounding and precision.
Because the DECIMAL type stores the number by character rather than just a floating point value, you are guaranteed not to get any rounding errors, just clipping when you exceed the range you’ve declared.

Read the descriptions of each numeric type, in particular the differences between DECIMAL, FLOAT and DOUBLE:
http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html

and it’s nice to see you testing, thanks for taking up my suggestion

:slight_smile:

Okay, I just did another test, and from my understanding of Significant Digits, the way MySQL’s DECIMAL data-type represents them is totally convoluted?!

I just added in the value “9000000” which only has ONE Significant Digit, and this is what MySQL spit out…

| 14 | 9000000 | 999.99 |

In Science, “9000000” would be written as 9 X 10^6 with ONE Significant Digit showing.

I’m not sure why MySQL is changing it to “999.99” if it claims it can handle 5 Significant Digits.

That is just plain wrong from a Mathematical/Scientific Notation standpoint…

TomTees

It isn’t storing significant difits - it swtores the closet number it can to what you entered. Anything bigger than 999.99 sotred in a field where 999.99 is the biggest possible value will be stored as 999.99 regardless of whether the number is 1000 or 9999999999999999999999999999. If you want it to store so many significant digits without worrying about exact precision then use float.

Because the numbers are stored as characters and there are 256 possible values for each with only 10 actually being used the sign can be accomodated by using an additional 10 of the 246 unused values. Similarly it doesn’t need to store the decimal point because the definition of the field tells it where the decimal point goes.

But I believe the way that Significant Digits work is by rounding from left to right.

So in this row…

| 11 | 9999.37 | 999.99 |

The original number “9999.37” is 6 significant digits and the field only holds 5 significant digits.

I figured the rounding would go left to right and so only the first 5 significant digits (i.e. “99993”) would be kept, leaving a result of “999.94”?!

More about Significant Digits

(** I never really liked or understood Significant Digits all the way back from my Physics class days in college!! **) :rolleyes:

TomTees

No hang ups - but it is an established term, so when it is used, I expect it is used the correct way.

the value 0.00012345 has ~no~ significant digits to the left of the decimal point

Agreed.

it actually has as many zeros as you wish to write to the left of the decimal point but the zeroes, in that case, aren’t significant

Agreed.

do you like this interpretation of the word “significant” any better?

…and the zeroes to the right of the decimal place in “0.00012345” aren’t significant either.

However, the digits “12345” are significant in the number “0.00012345”. That is five significant digits. So any variable “claiming” to store fivesignificant digits should be able to re-represent the number “0.00012345” and not lose them. But clearly that isn’t what te DECIMAL data-type in MySQL does.

So it would appear that “Significant Digits” isn’t a very good (or standard) way to communicate what DECIMAL does.

It’s pretty black and white.

just remember, DECIMAL(5,2) means five digits overall, two digits to the right of the decimal point

I now understand that now, but that isn’t what “da manual” says!!! It says it stores “significant digits”, and that is patently false.

if you try to store 0.00012345 in there, you’ll end up with 000.00

Which I showed in my test above.

Problem is that all zeros in the example “0.00012345” and NOT significant. And the digits that are significant (i.e. “12345”) are lost, thus nullifying what the documentation purports?!

Hey, it’s nothing personal against any of you, but this is clearly a a poor choice of terms - likely because whoever wrote the documentation doesn’t really understand what the term “Significant Digits” means.

I can say that I am 6 feet tall - where a “foot” is 10.5 inches, but that would be ridiculous since everyone assumes that when you refer to a foot (U.S.) you mean 12 inches.

When language (and terms) lose their commonly accepted meanings you have chaos…

Anyways!

TomTees

Admittedly, he could have done the test himself, but I think it’s a reasonable question as even the documentation is far from specific.

The implication that one character is used for each digit, minus sign and decimal point could give the impression that you can store a larger positive number (where the sign is implied) than negative (where the sign is included) i.e. “999.99” and “-99.99” use the same number of characters. Obviously this isn’t the case, but I still think the documentation could be clearer (and some examples woudn’t hurt).