Mailing List Archive


Back to the month index Back to the list index
Larry Stone (lstone@gte.net)
Wed, 02 Apr 1997 11:41:11 -0600 

Message-Id: <3.0.32.19691231180000.00693bac@mail.gte.net>
Date: Wed, 02 Apr 1997 11:41:11 -0600
From: Larry Stone <lstone@gte.net>
Subject: [mSQL] Precision problems with UPDATE

All,

I've seen some other mention on the list recently concerning problems with
the precision handling for REAL columns, which I guess was added in 2.0B5.
I've been experimenting some with it, and it seems that INSERT statements
preserve the precision of the inserted value, but UPDATE statements result
in a value which rounds to the precision of the value originally inserted
in that row.

For example (I'm leaving out most of the responses for clarity):

% msqladmin create test
% msql test
msql> create table tmp ( num real ) \g
msql> insert into tmp values ( null ) \g
msql> insert into tmp values ( 1.0 ) \g
msql> insert into tmp values ( 1.555 ) \g
msql> select * from tmp \g
... returns the strings "NULL", "1.0", "1.555"
msql> update tmp set num = 3.445 where num = null \g
... updates 1 row
msql> select * from tmp \g
... returns the strings "3", "1.0", "1.555"
msql> update tmp set num = 3.445 where num = 1.0 \g
... updates 1 row
msql> update tmp set num = 3.445 where num = 1.555 \g
... updates 1 row
msql> select * from tmp \g
... returns the strings "3", "3.4", and "3.445"

And now the kicker

msql> update tmp set num = 3.4455 where num = 3.445 \g
... updates all 3 rows !!!
msql> select * from tmp \g
... returns the strings "3", "3.4", and "3.446"

So it appears that the correct number is being stored, but is being rounded
off based on the precision of the value which was originally inserted into
that row. Note that this isn't just a problem for the "msql" interface
program. If I do the select in C code, the double value which is returned
has already been rounded.

Is there some way of modifying the precision of a value after the original
insert? Better yet, can we set the precision for a field to some constant
value? My application is such that it would be nice to always get 4 digits
after the decimal, regardless of the value which was originally inserted.

Thanks in advance for any help,

        Larry W. Stone
        lstone@gte.net
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com. Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!