math: consistency: not fuzzing in 'fuzzy rounding' for INT, CEIL and ROUNDUP with gnumeric_**long** for negative operands, followup to #56 - 'go_fake_floor()'
Same problem as in #56 (closed) for gnumeric_long, mentioned it there but couldn't reopen.
While we use fuzzy it should be consistent across functions, gnumeric_long
fails with INT, CEIL and ROUNDUP for negative operands.
See attached sheet
fuzzing_fails_with_negative_long.gnumeric
demonstrating the issue.
Heal? Use
go-math.c_full_fuzzing_long_wordy_patch, wordy, for debugging, or
go-math.c_full_fuzzing_long_basic_patch.
long story - Click to expand
there are some points in 'fuzzy rounding' calculations weak with gnumeric_long:
case I: fuzzy INTing doesn't work as intended for negative values with gnumeric_long,
example: 'INT( -1.0000000000000000001 )',
actual result: '-2',
expected with fake_rounding: '-1',
case II: fuzzy CEILing doesn't work as intended for negative values with gnumeric_long,
example: 'CEIL( -0.99999999999999999995 )',
actual result: '0',
expected with fake_rounding: '-1',
case III: fuzzy ROUNDUPing doesn't work as intended for negative values with gnumeric_long,
example: 'ROUNDUP( -1.0000000000000000001 )',
actual result: '-2',
expected with fake_rounding: '-1',
Posted bundeled here as one issue as it's one field to work on, and the functions
are nested in code.
Add. we need to be aware of the confusions of FLOOR() vs. INT() between
spreadsheets and C-code described in:
https://www.av8n.com/physics/spreadsheet-tips.htm#sec-perverse-def
( credits and thanks to John Denker for this enlightening explanation ).
For negative values C-code FLOOR is spreadsheet INTing, gnumerics
cell-formula evaluation of FLOOR() is masked to call fake_floor only
with positive arguments.
There are quite some functions calling fake_floor for negative values,
I hope they all want INTing.
case IV: not an issue of 'being wrong' but question of 'intended'???
consequence of fake rounding:
'= round( 1125899906842624.3 )' results in 1125899906842625, where
most users would expect 1125899906842624.
How doe's that happen? Adding a pad value of 0.5 -> 1125899906842624.8,
fake_floor adds another ULP, then 1125899906842625, is INT, no effect
of floor, no re-scaling or whatever, a result off from expected.
Similar for rounding 11258999.068426242 to 8 decimals, or plenty
other ROUND() calculations.
case IVa: The added effects of a 'big' pad value, round in operation of
addition, and fake_floor lead to a - wanted? - effect of rounding up
not only the first nextafter of 0.5 towards 0 ( 0.49999999999999995 ) to 1,
but also the second, the 'nextnextafter': 0.4999999999999999.
case I: 'INT( -1.0000000000000000001 )'
mathematically '-2' is correct, but the used 'fake_floor' is
intended to pull one ULP against the rounding direction, and
then result in -1 for the 'nextafter to an INT' value. That
doesn't work as the calling chain gnumeric INT -> gnm_fake_floor
-> dispatched in numbers.h to go_fake_floorl -> which for
negative values switches to 'floorl (go_sub_epsilonl (x))',
where C-code floorl and goffice sub_epsilonl pull in the same direction,
and thus no fuzzing takes place.
case II: 'CEIL( -0.99999999999999999995 )'
mathematically '0' is correct, but the used 'fake_ceil' is
intended to pull one ULP against the rounding direction, and
then result in -1 for the 'nextafter to an INT' value. That
doesn't work as the calling chain gnumeric CEIL -> gnm_fake_ceil
dispatched in numbers.h to go_fake_ceill -> which for negative
values switches to 'ceill (go_add_epsilonl (x))', where C-code
ceill and goffice add_epsilonl pull in the same direction, and
thus no faking takes place.
case III: 'ROUNDUP( -1.0000000000000000001 )'
mathematically '-2' is correct, but the used 'fake_roundup' is
intended to pull one ULP against the rounding direction, and
then result in -1 for the 'nextafter to an INT' value. That
doesn't work as the calling chain gnumeric ROUNDUP ->
gnm_fake_roundup dispatching negative values to gnm_fake_floor,
for long dispatched in numbers.h to go_fake_floorl, which uses
': floorl (go_sub_epsilonl (x));' for negative values, where
C-code floorl pulls into the intended rounding direction away
from zero / towards neg.-INF, and goffice go_sub_epsilonl pulls
into the same direction, not achieving a fuzzing effect for
ROUNDUP here pulling towards neg.-INF.
case IV: list of functions using fake_floor:
gnumeric/src/mathfunc.c/ppois,
gnumeric/src/mathfunc.c/pbinom,
gnumeric/src/mathfunc.c/pnbinom,
gnumeric/src/mathfunc.c/phyper,
gnumeric/src/mathfunc.c/pgeom,
gnumeric/src/validation.c/gnm_validation_eval,
gnumeric/src/sheet-control-gui.c/scg_object_anchor_to_coords,
gnumeric/src/tools/tabulate.c/do_tabulate,
gnumeric/plugins/fn-stat/functions.c/gnumeric_trimmean,
gnumeric/plugins/fn-stat/functions.c/gnumeric_neg_binomdist,
gnumeric/plugins/fn-stat/functions.c/gnumeric_chidist,
gnumeric/plugins/fn-stat/functions.c/gnumeric_chiinv,
gnumeric/plugins/fn-stat/functions.c/gnumeric_fdist,
gnumeric/plugins/fn-stat/functions.c/gnumeric_finv,
gnumeric/plugins/fn-stat/functions.c/gnumeric_binomdist,
gnumeric/plugins/fn-stat/functions.c/gnumeric_binom_dist_range,
gnumeric/plugins/fn-stat/functions.c/gnumeric_critbinom,
gnumeric/plugins/fn-stat/functions.c/gnumeric_permut,
gnumeric/plugins/fn-stat/functions.c/gnumeric_hypgeomdist,
gnumeric/plugins/fn-stat/functions.c/gnumeric_confidence,
gnumeric/plugins/fn-stat/functions.c/gnumeric_confidence_t,
gnumeric/plugins/fn-stat/functions.c/gnumeric_poisson,
gnumeric/plugins/fn-stat/functions.c/gnm_kth,
gnumeric/plugins/fn-stat/functions.c/gnumeric_quartile,
gnumeric/plugins/fn-stat/functions.c/gnumeric_quartile_exc,
gnumeric/plugins/fn-stat/functions.c/gnumeric_geomdist,
gnumeric/plugins/fn-stat/functions.c/gnumeric_permutationa,
gnumeric/plugins/fn-math/functions.c/gnumeric_gcd / range_gcd,
gnumeric/plugins/fn-math/functions.c/gnumeric_lcm / range_lcm,
gnumeric/plugins/fn-math/functions.c/gnumeric_floor - doesn't push negative,
gnumeric/plugins/fn-math/functions.c/gnumeric_int - here different direction is wanted,
gnumeric/plugins/fn-math/functions.c/gnumeric_fake_roundup - see case III.,
gnumeric/plugins/fn-date/functions.c/gnumeric_time,
gnumeric/plugins/fn-numtheory/numtheory.c/func_bitor / gnm_range_bitor,
gnumeric/plugins/fn-numtheory/numtheory.c/func_bitxor / gnm_range_bitxor,
gnumeric/plugins/fn-numtheory/numtheory.c/func_bitand / gnm_range_bitand,
goffice/goffice/utils/go-image.c/go_image_draw_fb,
goffice/goffice/utils/go-image.c/go_image_get_pixbuf_fb,
goffice/goffice/utils/go-style.c/go_style_set_text_angle,
goffice/goffice/graph/gog-renderer.c/gog_renderer_push_clip_rectangle,
multiple in
goffice/goffice/graph/gog-axis.c/,
goffice/goffice/math/go-math.c/go_fake_round - doesn't push negative,
goffice/goffice/math/go-math.c/go_fake_floorl - see below,