Strange problem with SCO 5.0.7 AWK and GAWK

[Steve M. Fabac, Jr.]

How do you explain AWK rounding numbers in this case:

Input file: /tmp/jackit

Customer 10 totals: 325.75 10777.91 10777.96
Customer 10 totals: 325.75 10777.92 10777.96
Customer 10 totals: 325.75 10777.93 10777.96
Customer 10 totals: 325.75 10777.94 10777.96
Customer 10 totals: 325.75 10777.95 10777.96
Customer 10 totals: 325.75 10777.96 10777.96
Customer 10 totals: 325.75 10777.97 10777.96
Customer 10 totals: 325.75 10777.98 10777.96
Customer 10 totals: 325.75 10777.99 10777.96
Customer 10 totals: 325.75 10777.90 10777.96


Awk script: dochck.awk

{
custnum = 10
# find cust_state total
if ( $1 == "Customer" && $2 == custnum && $3 == "totals:" ) {
print $0
state_total[ cur_state ] += ( $5 + 0 )
cust_total[ custnum ] += $5
check_total += ( $5 + 0 )
printf"check_total = %24.14f $5 = %24.14f\n", check_total, $5

}
}

Output of: cat /tmp/jackit | awk -f dochck.awk | less


Customer 10 totals: 325.75 10777.91 10777.96
check_total = 10777.90999999999990 $5 = 10777.90999999999990
Customer 10 totals: 325.75 10777.92 10777.96
check_total = 21555.83000000000170 $5 = 10777.92000000000010
Customer 10 totals: 325.75 10777.93 10777.96
check_total = 32333.76000000000200 $5 = 10777.93000000000030
Customer 10 totals: 325.75 10777.94 10777.96
check_total = 43111.70000000000440 $5 = 10777.94000000000050
Customer 10 totals: 325.75 10777.95 10777.96
check_total = 53889.65000000000870 $5 = 10777.95000000000070
Customer 10 totals: 325.75 10777.96 10777.96
check_total = 64667.61000000000790 $5 = 10777.95999999999910
Customer 10 totals: 325.75 10777.97 10777.96
check_total = 75445.58000000000170 $5 = 10777.96999999999930
Customer 10 totals: 325.75 10777.98 10777.96
check_total = 86223.55999999999770 $5 = 10777.97999999999960
Customer 10 totals: 325.75 10777.99 10777.96
check_total = 97001.55000000000290 $5 = 10777.98999999999980
Customer 10 totals: 325.75 10777.90 10777.96
check_total = 107779.44999999999700 $5 = 10777.89999999999960
(END)

gawk does the same:

$ cat /tmp/jackit | gawk -f dochck.awk | less
Customer 10 totals: 325.75 10777.91 10777.96
check_total = 10777.90999999999990 $5 = 10777.90999999999990
Customer 10 totals: 325.75 10777.92 10777.96
check_total = 21555.83000000000170 $5 = 10777.92000000000010
Customer 10 totals: 325.75 10777.93 10777.96
check_total = 32333.76000000000200 $5 = 10777.93000000000030
Customer 10 totals: 325.75 10777.94 10777.96
check_total = 43111.70000000000440 $5 = 10777.94000000000050
Customer 10 totals: 325.75 10777.95 10777.96
check_total = 53889.65000000000870 $5 = 10777.95000000000070
Customer 10 totals: 325.75 10777.96 10777.96
check_total = 64667.61000000000790 $5 = 10777.95999999999910
Customer 10 totals: 325.75 10777.97 10777.96
check_total = 75445.58000000000170 $5 = 10777.96999999999930
Customer 10 totals: 325.75 10777.98 10777.96
check_total = 86223.55999999999770 $5 = 10777.97999999999960
Customer 10 totals: 325.75 10777.99 10777.96
check_total = 97001.55000000000290 $5 = 10777.98999999999980
Customer 10 totals: 325.75 10777.90 10777.96
check_total = 107779.44999999999700 $5 = 10777.89999999999960
(END)

# what /usr/bin/awk
/usr/bin/awk:
awk SCO OpenServer 5.0.7 2003-02-18

# what /usr/gnu/bin/gawk
/usr/gnu/bin/gawk:

GNU Awk 3.1
#

The above program fragment and input file are extractions from a project where
I am writing an AWK script to break down a RealWorld Order Entry report
"INVOICE HISTORY BY CUSTOMER" and present a total by state and see the following:

Printed: November 04 2012 Sales By State Report for 2010 Page 1

GA Georgia 259.36
IL Illinois 23701.13
LA Louisiana 85.80
MI Michigan 72.98
MO Missouri 406.70
TN Tennessee 53.30
CN Canada 13688.33
==============
Grand Total: $ 38267.60

Warning: Check total does not equal Grand Total: Check total = $ 38267.60
is off by $ 0.00

When I see the "check total" is off by 0.00 I had to dig into the reason.

The "check total" is a running sum of the customer totals located in the
file by the test: if ( $1 == "Customer" && $2 == custnum && $3 == "totals:" ) {
state_total[ cur_state ] += ( $5 + 0 )
cust_total[ custnum ] += $5
check_total += ( $5 + 0 )
}

Note: check_total += $5 does the same as check_total += ( $5 + 0 )

In the END section of the AWK script I print out the by state totals and sum them
as a check to see that all customer totals are accounted for.

END {
for ( x = 1; x < 162; x+=3 ){
i = substr( state_sort, x, 2)
if ( state_total[ i ] > 0 ) {
printf"%20s %-14s %12.2f\n", i, state_name[ i ], state_total[ i ]
grandtot += state_total[ i ]
}

}
printf" ==============\n"
printf" Grand Total: $%12.2f\n\n", grandtot

if ( grandtot != check_total ) {
printf " Warning: Check total does not equal Grand Total: Check total = $%12.2f\n",check_total
printf" is off by $%12.2f\n", check_total - grandtot
}
}

Trying to find the reason, I changed the %12.2f to %24.14f and see the following:

Printed: November 04 2012 Sales By State Report for 2010 Page 1

GA Georgia 259.36000000000001
IL Illinois 23701.12999999999740
LA Louisiana 85.80000000000000
MI Michigan 72.98000000000000
MO Missouri 406.69999999999999
TN Tennessee 53.30000000000000
CN Canada 13688.32999999999990
==============
Grand Total: $ 38267.59999999999850

Warning: Check total does not equal Grand Total: Check total = $ 38267.6000000000058
is off by $ 0.00000000000728


That's when I created the code fragment dochck.awk and the /tmp/jackit input file
to see if the problem manifests without all the other code in the original AWK
program.

Adding a print $0 and printf when the Customer number Total
line is found shows:


Printed: November 04 2012 Sales By State Report for 2010 Page 1

Customer 10 totals: 325.75 10777.96 10777.96
check_total = 10777.95999999999910 $5 = 10777.95999999999910
Customer 11 totals: 878 12923.17 12923.17
check_total = 23701.12999999999740 $5 = 12923.17000000000010
Customer 12 totals: 436 13688.33 13688.33
check_total = 37389.45999999999910 $5 = 13688.32999999999990
Customer 15 totals: 2 72.98 72.98
check_total = 37462.44000000000230 $5 = 72.98000000000000
Customer 206798 totals: 1 85.80 85.80
check_total = 37548.24000000000520 $5 = 85.80000000000000
Customer 210270 totals: 9 259.36 259.36
check_total = 37807.60000000000580 $5 = 259.36000000000001
Customer 212073 totals: 7 406.70 406.70
check_total = 38214.30000000000290 $5 = 406.69999999999999
Customer 213028 totals: 2 53.30 53.30
check_total = 38267.60000000000580 $5 = 53.30000000000000
GA Georgia 259.36000000000001
IL Illinois 23701.12999999999740
LA Louisiana 85.80000000000000
MI Michigan 72.98000000000000
MO Missouri 406.69999999999999
TN Tennessee 53.30000000000000
CN Canada 13688.32999999999990
==============
Grand Total: $ 38267.59999999999850

Warning: Check total does not equal Grand Total: Check total = $ 38267.60000000000580
is off by $ 0.00000000000728
/tmp/sales_by_state_2010 (END)


WTF!

The simple solution is to change the test:

if ( grandtot != check_total ) {

To

if ( (( grandtot - check_total) > .01) || ((check_total - grandtot) > .01) ) {


--
Steve Fabac
S.M. Fabac & Associates
816/765-1670

[Bob Bailin]

"Steve M. Fabac, Jr." <smf...@att.net> wrote in message
news:50960D35...@att.net...

[snip (limit on quoted msg)]

Sounds like the age-old problem of binary floating point numbers not being
able to accurately represent decimal fractions in accounting applications.
Binary-coded decimal (BCD) applications get around this problem.

The simplest solution would be to have all the numbers in your input file
multiplied by 100 to create whole numbers, e.g.,

Customer 10 totals: 32575 1077791 1077796

and then output them always assuming 2 decimal places for the last 2 digits.

Et voila, no rounding problems!