Apache OpenOffice (AOO) Bugzilla – Issue 112652
ORB: report builder not handle correctly NULL values
Last modified: 2017-05-20 10:30:55 UTC
I tested it on DEV300_m83 with ORB 1.2.1_m82 and cws-srb1. Works same with both version. In decimal, and integer type field, when their content is NULL, in finished report shows: 1#N,AN, on decimal fields and 1,#NAN on integer fields. After saving to file system, and reopening, the report shows zeros instead of 1#NAN, and 1#N,AN. It not happens with text, or boolean fields, which I tested. This not happens with OOo 3.2.1 and ORB 1.2.1, where the example file was created, with report wizard. It seems to me as regression.
Created attachment 70191 [details] bugdoc with test data
Assign developer, set target.
I tested on ubuntu 10.04 32bit version, same results. Change OS.
I did the test also with calc and there it still works.
when generating the report in the attached bugdoc, the Writer ODF import filter is invoked on a temporary document file:///var/tmp/svi78.tmp/Table1.odt. this document is apparently not written by Writer (at least a breakpoint in obvious place didn't fire). this document contains table cells with value attributes like so: <table:table-cell table:style-name="ce2" office:value="1.#NAN" office:value-type="float"> the ODF 1.2 schema says: <define name="common-value-and-type-attlist"> <choice> <group> <attribute name="office:value-type"> <value>float</value> </attribute> <attribute name="office:value"> <ref name="double"/> </attribute> </group> the value "1.#NAN" is not in the lexical space of "double" according to XMLSchema-2: http://www.w3.org/TR/xmlschema-2/#double <quote> The special values positive and negative infinity and not-a-number have lexical representations INF, -INF and NaN, respectively. </quote> so whatever is writing these value attributes should be writing "NaN" if it wants to say not-a-number. because the file is not valid, i don't see an urgent need to improve the Writer ODF import here, although a diagnostic to inform the user about the invalid input could be helpful. assigning back to oj.
@mst: Even NaN doesn't solve the problem. The value shown in the table is 0 which is wrong when it is a Nan. When writing 1.#NAN the value is correctly read but not written when doing so.
The problem here is not the import of the document, the problem is when saving it the NaN is gone. It doesn't matter if 1.#NAN is written or Nan.
indeed, the SvXMLUnitConverter::convertDouble() writes these weird values: 1.#NAN -1.#NAN 1.#INF -1.#INF instead of the XMLSchema-2 conformant values: NaN INF -INF the weird values are produced by functions from <rtl/math.hxx>. to fix this i'd like to change the conversion functions in SvXMLUnitConverter to check for special values NaN/INF. - always write the XMLSchema-2 conformant values - when reading, check for XMLSchema-2 conformant values first, fall back to rtl::Math code for backward compatibility. @er, mib: is that ok for you?
My 2 cents: don't work around in upper layers, change the underlying code instead. Change rtl::math::stringToDouble() to recognize both, the 1.#* and NaN/INF notation. Find out where rtl::math::doubleToString() is used to write NANs and INFs, Chart could be a candidate for this. If this is not used in code that writes persistent document files, change it to write the the XMLSchema-2 NaN/INF values instead. If it is used for persistent documents, judge the impact of the change if an older version would encounter the new NaN/INF values. I'd still opt for a change though to get this right. Btw, the 1.#* notation came from the MSC representation from times where XMLSchema didn't even exist.
i've now implemented the change to ::rtl::math functions suggested by er. iha reported that this causes a problem in chart: when a chart contains deleted values, these are represented as NaN; saving such a chart with the fix and then reading with an un-fixed OOo version converts these NaNs to zeros. but i don't see a way to prevent this kind of problem.
creating the report with OOO 3.2.1 yields cells like this: <table:table-cell table:style-name="ce2" office:value-type="float"> this is invalid because the office:value-type attribute does not have a corresponding office:value attribute. creating the report with DEV300m86 yields cells like this: <table:table-cell table:style-name="ce2" office:value="1.#NAN" office:value-type="float"> now there is a value attribute, but its value is invalid. for some reason the writer renders the value as "1#N,AN" in some cases, which looks wrong. with the fix to ::rtl::math, the cells still look the same, but the import converts the string to "NaN" or "N,aN" in some cases. after a further fix to the reportbuilder itself, it generates proper cells: <table:table-cell table:style-name="ce2" office:value="NaN" office:value-type="float"> but there is still the problem that the value is sometimes displayed as "N,aN". this actually also occurs if the document type is changed to spreadsheet; i.e. Calc also displays "N,aN" for these values.
@mst: > for some reason the writer renders the value as "1#N,AN" in some cases This may happen if the number formatter is involved and a format other than "General" is used, e.g. 0.00 (or German 0,00 as it might be the case here). The number formatter is not prepared to handle NANs correctly. For this you may create a new issue and assign it to me.
ok, now i've found out where the NaNs become zeroes: in SwXCell::getValue(): if( ::rtl::math::isNan( fRet ) ) fRet = 0.0; after removing that the NaNs aren't stored as 0 anymore. furthermore i've fixed the SwTblBoxValue pool item, which implemented its operator== by comparing two doubles with ==, which doesn't work on NaNs. i guess for the purpose of pooling two items with NaN should be equal. the formatting issue does not seem to affect loading/storing the document, so i think it can be fixed separately (not for 3.3). filed issue 114125.
fixed in cws dba33h non-conforming NaN/INF representation in OOo: http://hg.services.openoffice.org/hg/cws/dba33h/rev/a120c06f7b59 http://hg.services.openoffice.org/hg/cws/dba33h/rev/5dc8dc01f4d7 non-conforming NaN/INF representation in SRB: http://hg.services.openoffice.org/hg/cws/dba33h/rev/232bb0265ae7 SwTblBoxValue pool item ASSERT with NaN: http://hg.services.openoffice.org/hg/cws/dba33h/rev/d987c5b56a33 SwXCell converting NaN to 0: http://hg.services.openoffice.org/hg/cws/dba33h/rev/ab516f27dd84
Please verify. - Open bug doc - Execute report - SaveAs in writer doc and store it - Reload => The NaN should still be valid and not converted to zero
verified in CWS dba33h find more information about this CWS, like when it is available in the master builds, in EIS, the Environment Information System: http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fdba33h
May be it solved in some way, but if I run the report in OOo 3.2.1 the places where no numbers in table, not print anything. If I open it in OOO330m10, then in both version in decimal values I get printed N,aN. In Integer fields, I get NaN. How to remove it to get empty results set in reports, instead of NaN? I tried conditional print expression, no positive results. It not removes with NOT(ISBLANK([field])).
The conditional print expression has to be checked I guess. When I fill this IF(ISNUMBER([Szam_tiny]);[Szam_tiny]) in the data field I at least get rid of the NAN values.
The strange thing is that, the IF(ISNUMBER( works with integer field types only in linux version (I tested on ubuntu), not with decimal type. On win7 IF(ISNUMBER( not help even in integer fields, it shows NaN in finished reports.
IF(ISNUMBER([Szam_tiny]);[Szam_tiny];" ") in the data field I get rid of the NAN values, and get 0 and 0,00 (int, dec) not empty places as in text and boolean fields.