Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||ORB: report builder not handle correctly NULL values|
|Status:||CLOSED FIXED||QA Contact:||issues@dba <issues>|
|Priority:||P2||CC:||IngridvdM, issues, michael.brauer, mst.ooo, ocke.janssen, ooo, orw, stephan.bergmann.secondary|
|Target Milestone:||OOo 3.3|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
|Issue Depends on:|
Description r4zoli 2010-06-24 12:45:12 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.
Comment 2 r4zoli 2010-06-24 12:47:42 UTC
Assign developer, set target.
Comment 3 r4zoli 2010-06-29 09:07:28 UTC
I tested on ubuntu 10.04 32bit version, same results. Change OS.
Comment 4 ocke.janssen 2010-07-07 12:21:35 UTC
I did the test also with calc and there it still works.
Comment 5 mst.ooo 2010-07-29 18:27:45 UTC
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.
Comment 6 ocke.janssen 2010-08-23 13:50:51 UTC
@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.
Comment 7 ocke.janssen 2010-08-24 07:08:46 UTC
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.
Comment 8 mst.ooo 2010-08-24 10:08:58 UTC
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?
Comment 9 ooo 2010-08-24 10:51:46 UTC
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.
Comment 10 mst.ooo 2010-08-25 11:47:30 UTC
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.
Comment 11 mst.ooo 2010-08-25 15:15:48 UTC
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.
Comment 12 ooo 2010-08-26 13:57:03 UTC
@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.
Comment 13 mst.ooo 2010-08-26 14:24:20 UTC
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.
Comment 14 mst.ooo 2010-08-27 11:46:10 UTC
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
Comment 15 ocke.janssen 2010-09-01 08:12:54 UTC
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
Comment 16 marc.neumann 2010-09-03 09:07:56 UTC
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
Comment 17 r4zoli 2010-10-15 13:25:42 UTC
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])).
Comment 18 ocke.janssen 2010-10-18 08:51:06 UTC
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.
Comment 19 r4zoli 2010-10-18 09:37:55 UTC
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.
Comment 20 r4zoli 2011-01-14 08:38:01 UTC
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.