Issue 112652 - ORB: report builder not handle correctly NULL values
Summary: ORB: report builder not handle correctly NULL values
Alias: None
Product: Base
Classification: Application
Component: ReportBuilder (show other issues)
Version: DEV300m83
Hardware: Unknown All
: P2 Trivial (vote)
Target Milestone: OOo 3.3
Assignee: marc.neumann
QA Contact: issues@dba
Keywords: odf_validation, regression
Depends on:
Blocks: 111112
  Show dependency tree
Reported: 2010-06-24 12:45 UTC by r4zoli
Modified: 2017-05-20 10:30 UTC (History)
8 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

bugdoc with test data (29.39 KB, application/vnd.sun.xml.base)
2010-06-24 12:46 UTC, r4zoli
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
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 1 r4zoli 2010-06-24 12:46:22 UTC
Created attachment 70191 [details]
bugdoc with test data
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 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"

the ODF 1.2 schema says:
    <define name="common-value-and-type-attlist">
                <attribute name="office:value-type">
                <attribute name="office:value">
                    <ref name="double"/>

the value "1.#NAN" is not in the lexical space of "double" according to XMLSchema-2:

The special values positive and negative infinity and not-a-number have lexical
representations INF, -INF and NaN, respectively.

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 2010-08-24 10:08:58 UTC
indeed, the SvXMLUnitConverter::convertDouble() writes these weird values:
instead of the XMLSchema-2 conformant values:

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 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 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"

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"

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
> 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 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 2010-08-27 11:46:10 UTC
fixed in cws dba33h

non-conforming NaN/INF representation in OOo:

non-conforming NaN/INF representation in SRB:

SwTblBoxValue pool item ASSERT with NaN:

SwXCell converting NaN to 0:
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:
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
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


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
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.