TestBuilder float and decimal format differs from the values returned by database
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
Hi Lari, Your patch makes sense to me. However we might increase the test suite execution time significantly [ref needed]. I would suggest to refresh (discard_changes) the object from your .t file ($built would be built using build_object, then call ->discard_changes->unblessed). The "Statistic" FIXME would be moved there as well (or depend on a bug that will add the PK!). It's definitely less clean as I am suggesting to move code out of (test covered) .pm, but it feels better in this specific situation. Cheers, Jonathan Le ven. 5 févr. 2021 à 19:37, Lari Taskula <lari.taskula@hypernova.fi> a écrit :
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
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
Thanks for the idea. It seems build_object refreshes the build()ed object by searching the row via Koha-objects. This alone is enough to solve my original problem in the patron data export Bug, because all the tables that are related to patrons by a foreign key, and have float or decimal columns, do seem to have a Koha-object, making the use of build_object possible. In fact I found only two DBIx schema files that have decimal fields with higher number of allowed decimal places than two, and no corresponding Koha::Object. These are AqbudgetsPlanning and PendingOfflineOperation. So there are very few cases where build_object would not be sufficient to solve the decimal formatting issues. Lari Taskula CEO, Hypernova Oy PL 16 80101 Joensuu, Finland On 8.2.2021 13.32, Jonathan Druart wrote:
Hi Lari,
Your patch makes sense to me. However we might increase the test suite execution time significantly [ref needed]. I would suggest to refresh (discard_changes) the object from your .t file ($built would be built using build_object, then call ->discard_changes->unblessed). The "Statistic" FIXME would be moved there as well (or depend on a bug that will add the PK!).
It's definitely less clean as I am suggesting to move code out of (test covered) .pm, but it feels better in this specific situation.
Cheers, Jonathan
Le ven. 5 févr. 2021 à 19:37, Lari Taskula <lari.taskula@hypernova.fi> a écrit :
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
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : https://www.koha-community.org/ git : https://git.koha-community.org/ bugs : https://bugs.koha-community.org/
participants (2)
-
Jonathan Druart -
Lari Taskula