[Koha-devel] TestBuilder float and decimal format differs from the values returned by database

[Lari Taskula]

Hi devs,

While working on patron data export (
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20028 ), I was
trying to generate a bunch of test data with TestBuilder, and then
compare them to what was returned by DBIx's search().

Unfortunately I couldn't do this, because most decimals from DBI/DBIx
are returned in a different formatting to what TestBuilder has. Running
an is_deeply() to compare the objects fails with:

#     Structures begin differing at:
#          $got->[0]{amount} = '90203.710000'        (value returned by DBIx)
#     $expected->[0]{amount} = '90203.71'            (value in HASHref generated by TestBuilder)

This is because TestBuilder's _gen_real() forces the formatting to 2
decimal places.

But there's more - My original idea to solve this issue was by using
"column_info" provided by DBIx, and formatting the value to maximum
decimal places allowed. But even if you follow the column's size
definition, you will notice that there is a different behavior between
data types "float"/"double" and "decimal". If it's a decimal, it stays
in "stringified" format with all those trailing zeroes, but if it's a
float, all the insignificant zeroes are dropped from the end.

At some layer lower than DBIx (perhaps DBD::mysql?) there appears to be
some formatting magic. A concrete example of this is when you have a
FLOAT(10,4) and DECIMAL(10,4) columns and store "10.1000" in both
columns, in the end the float becomes 10.1 and the decimal "10.1000".
It's the same result with both DBI and DBIx. However, for example
mariadb-client chooses to format both values as "10.1000".

My other solution was to reload the row generated by DBIx in
TestBuilder::_storeColumnValues() with discard_changes/get_from_storage
method, but that
1. doesn't work for tables that do not have primary key (e.g.
statistics), see
DBIx::Class::ResultSource::_pri_cols_or_die(): Operation requires a
primary key to be declared on 'Statistic' via set_primary_key at
/home/koha/Koha/t/lib/TestBuilder.pm line 362
2. adds some overhead, although only into tests

I would appreciate some ideas or improvements. I've created a Bug for
this issue, see
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27630 . There
is a test to demonstrate the issue, and the previously mentioned
attempts to solve this issue.

P.S. Somewhat related bug:
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20621

-- 
Lari Taskula
CEO, Hypernova Oy
PL 16
80101 Joensuu, Finland

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20210205/d5d054ce/attachment.htm>