Thread: Re: [SQL] Decimal precsion?
> > Yeah I finnally figured that out, wish the docs would cover things like > that. > > I went through two books (which said its just liek COBOL! and then > ignored me) and finnally the mysql pages had a good right up. > > I still thing the 0.0 thing is an error. > Yes, and why doesn't this generate an error:test=> insert into example values(0.12345);INSERT 19488 1test=> insert into examplevalues(0.1234567);INSERT 19489 1test=> select * from example; other------0.12350.1235(2 rows) Jan, can you comment on this. I found the code in numeric.c, but can't figure out what the proper test should be. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > > > > Yeah I finnally figured that out, wish the docs would cover things like > > that. > > > > I went through two books (which said its just liek COBOL! and then > > ignored me) and finnally the mysql pages had a good right up. > > > > I still thing the 0.0 thing is an error. > > > > Yes, and why doesn't this generate an error: > > test=> insert into example values(0.12345); > INSERT 19488 1 > test=> insert into example values(0.1234567); > INSERT 19489 1 > test=> select * from example; > other > ------ > 0.1235 > 0.1235 > (2 rows) > > Jan, can you comment on this. I found the code in numeric.c, but can't > figure out what the proper test should be. Haven't seen the original message, but it looks to me that the column is declared as decimal with 4 digits after the decimal point. Therefore, all values get rounded at the time of INSERT/UPDATE. The above looks numerically right to me. Isn't that behaviour correct? Does the standard define something else? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
> > > > > > > > Yeah I finnally figured that out, wish the docs would cover things like > > > that. > > > > > > I went through two books (which said its just liek COBOL! and then > > > ignored me) and finnally the mysql pages had a good right up. > > > > > > I still thing the 0.0 thing is an error. > > > > > > > Yes, and why doesn't this generate an error: > > > > test=> insert into example values(0.12345); > > INSERT 19488 1 > > test=> insert into example values(0.1234567); > > INSERT 19489 1 > > test=> select * from example; > > other > > ------ > > 0.1235 > > 0.1235 > > (2 rows) > > > > Jan, can you comment on this. I found the code in numeric.c, but can't > > figure out what the proper test should be. > > Haven't seen the original message, but it looks to me that > the column is declared as decimal with 4 digits after the > decimal point. Therefore, all values get rounded at the time > of INSERT/UPDATE. The above looks numerically right to me. > > Isn't that behaviour correct? Does the standard define > something else? OK, I wasn't sure on whether rounding was correct. However, the original message had DECIMAL(4,4) and he could insert 0.1, but not 0.0. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > the column is declared as decimal with 4 digits after the > > decimal point. Therefore, all values get rounded at the time > > of INSERT/UPDATE. The above looks numerically right to me. > > > > Isn't that behaviour correct? Does the standard define > > something else? > > OK, I wasn't sure on whether rounding was correct. > > However, the original message had DECIMAL(4,4) and he could insert 0.1, > but not 0.0. The rounding is of course correct. Rounding 0.1234567 first to 0.123457 and then cutting of to 0.1234 is finally cutting, not rounding, and totally braindead. You only have to look at the digit after the last significant one. In this case we have 4 significant digits, so we must look at digit 5, nothing else. Thus 0.1234500 is 0.1235 while 0.1234499 is 0.1234 - end of story. The other one, not beeing able to insert 0.0, is surely a bug. Would you please put it onto the TODO? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
> > However, the original message had DECIMAL(4,4) and he could insert 0.1, > > but not 0.0. > > The rounding is of course correct. Rounding 0.1234567 first > to 0.123457 and then cutting of to 0.1234 is finally cutting, > not rounding, and totally braindead. You only have to look at > the digit after the last significant one. In this case we > have 4 significant digits, so we must look at digit 5, > nothing else. Thus 0.1234500 is 0.1235 while 0.1234499 is > 0.1234 - end of story. > > The other one, not beeing able to insert 0.0, is surely a > bug. Would you please put it onto the TODO? Done: * insert of 0.0 into DECIMAL(4,4) field fails -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026