Oninit cdi / Archive
Talk to an expert →

Floating point rounding issue

Posted in 2004

Topics: Installation, Setup & Upgrades, Connectivity: ESQL/C, 4GL & Embedded SQL, Server Administration, Security, Permissions & Auditing, Data Types & Schema Design, Triggers, Constraints & Referential Integrity, Networking & sqlhosts Configuration, Platform-Specific Issues, Third-Party Tools & Monitoring

Brian Medley — — source: Usenet: comp.databases.informix

Hi, We are having intermittent problems with floating point numbers losing their fractional part. That is, sometimes, but not always, 23.45 is stored as 23.00 in the database. Casting all passed variables to string before execution of a statement solves the issue. There's just too much code to start binding variables explicitly at the moment. The same code worked fine under Solaris 5.8 with Informix 7 and Perl 5.6.1. We are currently using RedHat Linux, Informix DS 9.4, and Perl 5.8.0. Below is some example code that reliably reproduces the problem. After the code some details of our setup are given along with a few lines from a debug session. Something that seems odd to us are the lines from the execute function call. It seems that the value 23.45 was bound as an integer. I'd be glad to furnish any additional details that might be needed. #! /usr/bin/perl -w use strict; use DBI; my $val = $ARGV[0] || 45.67; my $load_id = $ARGV[1] || 509615; my $dbh = DBI->connect( 'dbi:Informix:web', `cat $ENV{HOME}/user`, `cat $ENV{HOME}/passwd`, { PrintError => 0, RaiseError => 0, AutoCommit => 0 } ) or die "$DBI::errstr $DBI::err"; my $wsql = qq{ UPDATE bid SET status='W' WHERE load_id=$load_id AND status != 'W' }; my $isql = qq{ INSERT INTO bid (load_id, user_id, bid, counter_bid, status) VALUES (?, ?, ?, ?, 'A') }; print "Saving as $val\\n"; my $sth = $dbh->prepare($wsql); $sth->execute; if ($val > 0) { $sth = $dbh->prepare($isql); $sth->execute($load_id, 1, $val, $val); } $dbh->commit; $sth = $dbh->prepare(qq{ SELECT counter_bid FROM bid WHERE load_id=$load_id AND status='A' });$sth->execute; my ($newval) = $sth->fetchrow; print "Actually saved as $newval\\n"; $dbh->disconnect; # Running on Red Hat Enterprise AS 3.0. # Perl 5.8.0 # DBI 1.38 # DBD::Informix 2003.04 # $ esql -V # IBM Informix CSDK Version 2.81, IBM Informix-ESQL Version 9.53.UC2X6 # $ dbaccess -V # DB-Access Version 9.40.UC2X4 # $ grep webdb $INFORMIXDIR/etc/sqlhosts # webdb onipcshm localhost webdb # # PERL_DBI_DEBUG=2 ./error.pl 23.45 >| debug_run 2>&1 # # Program output: # # Saving as 23.45 # Actually saved as 23.00000000 # # Abbreviated debug output: # # DBI 1.38-ithread dispatch trace level set to 2 # -> DBI->connect(dbi:Informix:web, apache # -> DBI->install_driver(Informix) for linux perl=5.008 pid=23213 ruid=502 euid=502 # install_driver: DBD::Informix version 2003.04 loaded from # /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/Informix.pm # -> connect for DBD::Informix::dr (DBI::dr=HASH(0x814b8a4)~0x81af934 'web' "apache # CONNECT TO 'web' with user info # -> prepare for DBD::Informix::db (DBI::db=HASH(0x81af904)~0x81b03c0 ' # INSERT INTO bid # (load_id, user_id, bid, counter_bid, status) # VALUES # (?, ?, ?, ?, 'A') # ') thr#804bcf0 # number of described fields 4 # dbd_ix_st_prepare'imp_sth->n_ocols: 4 # <- DESTROY= undef at error.pl line 36 # -> execute for DBD::Informix::st # (DBI::st=HASH(0x81b04bc)~0x804c9f0 509615 1 23.45 23.45) thr#804bcf0 # ---- dbd_ix_bindsv() fld-indx = 1 # ---- dbd_ix_bindsv() inp-type = 13 # dbd_ix_bindsv -- integer # ---- dbd_ix_bindsv() fld-indx = 2 # ---- dbd_ix_bindsv() inp-type = 13 # dbd_ix_bindsv -- integer # ---- dbd_ix_bindsv() fld-indx = 3 # ---- dbd_ix_bindsv() inp-type = 13 # dbd_ix_bindsv -- integer <----------- Why is this integer? # ---- dbd_ix_bindsv() fld-indx = 4 # ---- dbd_ix_bindsv() inp-type = 13 # dbd_ix_bindsv -- integer <----------- Why is this integer? # # $ dbschema -d web -t bid | grep bid # { TABLE "informix".bid row size = 110 number of columns = 10 index size = 33 } # create table "informix".bid # bid float not null , # bid_ref varchar(60), # counter_bid float, # revoke all on "informix".bid from "public"; # alter table "informix".bid add constraint (foreign key (load_id) # alter table "informix".bid add constraint (foreign key (user_id) # alter table "informix".bid add constraint (foreign key (status) # references "gb".ref_bid_status ); # create trigger "gb".log_bid_upd update of bid,status on "informix" # .bid referencing old as pre_upd new as post_upd # insert into "gb".bid_log (id,user_id,load_id,bid,pre_upd_status, # post_upd.bid ,pre_upd.status ,post_upd.status )); -- Brian Medley

↪ replying to Brian Medley
Ferronato — — source: Usenet: comp.databases.informix

Hi Brian, Look after , if you have a envinronment variable DBMONEY=, or DBMONEY=. or neither .... If you have , then I guess you are using a variable 'integer' to store the value when you're executing the INSERT statement. Try to run a INSERT statement in DBACCESS tool and compare if the result is the same ..... Good luck. Best regards. RFo bpmedley-removethis@4321.tv (Brian Medley) wrote in message news:<3639095f.0401061342.1e839b40@posting.google.com>... > Hi, > > We are having intermittent problems with floating point numbers losing > their fractional part. That is, sometimes, but not always, 23.45 is > stored as 23.00 in the database. Casting all passed variables to > string before execution of a statement solves the issue. There's > just too much code to start binding variables explicitly at the > moment. The same code worked fine under Solaris 5.8 with > Informix 7 and Perl 5.6.1. We are currently using RedHat Linux, > Informix DS 9.4, and Perl 5.8.0. Below is some example code > that reliably reproduces the problem. > > After the code some details of our setup are given along with a few > lines from a debug session. Something that seems odd to us are the > lines from the execute function call. It seems that the value 23.45 > was > bound as an integer. > > I'd be glad to furnish any additional details that might be needed. > > #! /usr/bin/perl -w > > use strict; > > use DBI; > > my $val = $ARGV[0] || 45.67; > my $load_id = $ARGV[1] || 509615; > > my $dbh = DBI->connect( > 'dbi:Informix:web', > `cat $ENV{HOME}/user`, `cat $ENV{HOME}/passwd`, > { PrintError => 0, RaiseError => 0, AutoCommit => 0 } > ) or die "$DBI::errstr $DBI::err"; > > my $wsql = qq{ > UPDATE bid > SET status='W' > WHERE load_id=$load_id > AND status != 'W' > }; > > my $isql = qq{ > INSERT INTO bid > (load_id, user_id, bid, counter_bid, status) > VALUES > (?, ?, ?, ?, 'A') > };> > print "Saving as $val\\n"; > > my $sth = $dbh->prepare($wsql); > $sth->execute; > if ($val > 0) { > $sth = $dbh->prepare($isql); > $sth->execute($load_id, 1, $val, $val); > } > $dbh->commit; > > $sth = $dbh->prepare(qq{ > SELECT counter_bid > FROM bid > WHERE load_id=$load_id > AND status='A' > });> $sth->execute; > my ($newval) = $sth->fetchrow; > print "Actually saved as $newval\\n"; > > $dbh->disconnect; > > # Running on Red Hat Enterprise AS 3.0. > # Perl 5.8.0 > # DBI 1.38 > # DBD::Informix 2003.04 > # $ esql -V > # IBM Informix CSDK Version 2.81, IBM Informix-ESQL Version 9.53.UC2X6 > # $ dbaccess -V > # DB-Access Version 9.40.UC2X4 > # $ grep webdb $INFORMIXDIR/etc/sqlhosts > # webdb onipcshm localhost webdb > # > # PERL_DBI_DEBUG=2 ./error.pl 23.45 >| debug_run 2>&1 > # > # Program output: > # > # Saving as 23.45 > # Actually saved as 23.00000000 > # > # Abbreviated debug output: > # > # DBI 1.38-ithread dispatch trace level set to 2 > # -> DBI->connect(dbi:Informix:web, apache > # -> DBI->install_driver(Informix) for linux perl=5.008 pid=23213 > ruid=502 euid=502 > # install_driver: DBD::Informix version 2003.04 loaded from > # /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/Informix.pm > # -> connect for DBD::Informix::dr > (DBI::dr=HASH(0x814b8a4)~0x81af934 'web' "apache > # CONNECT TO 'web' with user info > # -> prepare for DBD::Informix::db > (DBI::db=HASH(0x81af904)~0x81b03c0 ' > # INSERT INTO bid > # (load_id, user_id, bid, counter_bid, status) > # VALUES > # (?, ?, ?, ?, 'A') > # ') thr#804bcf0 > # number of described fields 4 > # dbd_ix_st_prepare'imp_sth->n_ocols: 4 > # <- DESTROY= undef at error.pl line 36 > # -> execute for DBD::Informix::st > # (DBI::st=HASH(0x81b04bc)~0x804c9f0 509615 1 23.45 23.45) > thr#804bcf0 > # ---- dbd_ix_bindsv() fld-indx = 1 > # ---- dbd_ix_bindsv() inp-type = 13 > # dbd_ix_bindsv -- integer > # ---- dbd_ix_bindsv() fld-indx = 2 > # ---- dbd_ix_bindsv() inp-type = 13 > # dbd_ix_bindsv -- integer > # ---- dbd_ix_bindsv() fld-indx = 3 > # ---- dbd_ix_bindsv() inp-type = 13 > # dbd_ix_bindsv -- integer <----------- Why is this integer? > # ---- dbd_ix_bindsv() fld-indx = 4 > # ---- dbd_ix_bindsv() inp-type = 13 > # dbd_ix_bindsv -- integer <----------- Why is this integer? > # > # $ dbschema -d web -t bid | grep bid > # { TABLE "informix".bid row size = 110 number of columns = 10 index > size = 33 } > # create table "informix".bid > # bid float not null , > # bid_ref varchar(60), > # counter_bid float, > # revoke all on "informix".bid from "public"; > # alter table "informix".bid add constraint (foreign key (load_id) > # alter table "informix".bid add constraint (foreign key (user_id) > # alter table "informix".bid add constraint (foreign key (status) > # references "gb".ref_bid_status ); > # create trigger "gb".log_bid_upd update of bid,status on "informix" > # .bid referencing old as pre_upd new as post_upd > # insert into "gb".bid_log > (id,user_id,load_id,bid,pre_upd_status, > # post_upd.bid ,pre_upd.status ,post_upd.status ));

↪ replying to Brian Medley
Jonathan Leffler — — source: Usenet: comp.databases.informix

Brian Medley wrote: > We are having intermittent problems with floating point numbers losing > their fractional part. That is, sometimes, but not always, 23.45 is > stored as 23.00 in the database. Casting all passed variables to > string before execution of a statement solves the issue. There's > just too much code to start binding variables explicitly at the > moment. The same code worked fine under Solaris 5.8 with > Informix 7 and Perl 5.6.1. We are currently using RedHat Linux, > Informix DS 9.4, and Perl 5.8.0. Below is some example code > that reliably reproduces the problem. > > After the code some details of our setup are given along with a few > lines from a debug session. Something that seems odd to us are the > lines from the execute function call. It seems that the value 23.45 > was bound as an integer. > > I'd be glad to furnish any additional details that might be needed. Dear Brian, I had a roughly similar report maybe 9 months ago. I remember that there was a fairly simple fix in the code in dbdimp.ec, but I don't remember the details. Further, I'm not in the office again until next week, and I don't have the relevant source code at home (a mistake, no doubt, but nonetheless, that's the way it is at the moment). Is it rounding or truncation? What do you get on 23.65 instead of 23.45? That's a minor issue, though. The problem was related to the tests on whether the SV (Perl scalar value structure) contained an integer or not - SvIOK, SvNOK, etc. Somehow or other, in 5.8.x, the SvIOK bit was being set even though the SvNOK was also set. I may even simply have reversed the order of the tests -- sorry, having a complete brain fart and limited to no access to the data where I did it. Be careful if you do reverse the order of the tests; it might have unexpected consequences. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Glossary