[Info-ingres] precision

[Paul White]

Today I was surprised by a simple bit of Ingres maths.

This list of numbers should total zero, but is out of balance by .01
There are just 10 records in the batch. The amounts are not particularly
large.
The source_amount number in question is float.  It really should be decimal
or money for better reliability.

ps. test case provides a bonus E_US09C4 which seems to be related with the
multi-tuple insert.


INGRES TERMINAL MONITOR Copyright 2014 Actian Corporation
Ingres Microsoft Windows Version II 10.2.0 (a64.win/100) login
Thu Nov 15 23:51:00 2018
Enter \g to execute commands, "help help\g" for general help,
"help tm\g" for terminal monitor help, \q to quit

continue
* * /* SQL Startup File */
set autocommit on
Executing . . .

continue
* * 
drop table if exists testsum;
Executing . . .

continue
* * * * * * * 
create table testsum (
	batch_no integer not null default 0,
	seq	integer not null default 0,
	source_amount float not null default 0
	)
;
Executing . . .

continue
* * * * * * * * * * * * * 
insert into testsum values 
	( 123,  1,    672.00),
	( 123,  2,   6000.96),
	( 123,  3,   3600.00),
	( 123,  4,   3251.25),
	( 123,  5,   3229.80),
	( 123,  6,   4827.15),
	( 123,  7,   4016.25),
	( 123,  8,   1056.00),
	( 123,  9,   1943.10),
	( 123, 10, -28596.50)
Executing . . .

(10 rows)
continue
* * * Executing . . .

<<< There is a select * in here not printing for some reason >>>

+-------------+-------------+-----------+
|batch_no     |seq          |source_amou|
+-------------+-------------+-----------+
|          123|            1|    672.000|
|          123|            2|   6000.960|
|          123|            3|   3600.000|
|          123|            4|   3251.250|
|          123|            5|   3229.800|
|          123|            6|   4827.150|
|          123|            7|   4016.250|
|          123|            8|   1056.000|
|          123|            9|   1943.100|
|          123|           10| -28596.500|
+-------------+-------------+-----------+
(10 rows)
E_US09C4 Syntax error on line 1.  Last symbol read was: 'EOF'.
    (Thu Nov 15 23:51:00 2018)

continue
* * * * * * 
select batch_no, count(*), sum(source_amount) from testsum
group by batch_no
having abs(sum(source_amount)) >= 0.01
;
Executing . . .


+-------------+-------------+-----------+
|batch_no     |col2         |col3       |
+-------------+-------------+-----------+
+-------------+-------------+-----------+
(0 rows)
continue
* * * * * 
select batch_no, count(*), sum(source_amount) from testsum
group by batch_no
;
Executing . . .


+-------------+-------------+-----------+
|batch_no     |col2         |col3       |
+-------------+-------------+-----------+
|          123|           10|      0.010|
+-------------+-------------+-----------+
(1 row)
continue
* * * * * * 
select batch_no, count(*), sum(source_amount) from testsum
group by batch_no
having abs(sum(source_amount)) >= 0.005
;
Executing . . .


+-------------+-------------+-----------+
|batch_no     |col2         |col3       |
+-------------+-------------+-----------+
|          123|           10|      0.010|
+-------------+-------------+-----------+
(1 row)
continue
* * * * *
* 
Ingres Version II 10.2.0 (a64.win/100) logout
Thu Nov 15 23:51:00 2018


Paul
-------------- next part --------------
A non-text attachment was scrubbed...
Name: testsum.sql
Type: application/octet-stream
Size: 834 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20181115/a702ec2e/attachment.obj>