[Libreoffice-bugs] [Bug 132488] New: filesave: fileopen: macro: xml: calc filtered ranges wrongly saved in .ods format? xml export / import buggy? affects filters in database ranges with offset to cell A1

Tue Apr 28 12:04:45 UTC 2020

https://bugs.documentfoundation.org/show_bug.cgi?id=132488

            Bug ID: 132488
           Summary: filesave: fileopen: macro: xml: calc filtered ranges
                    wrongly saved in .ods format? xml export / import
                    buggy? affects filters in database ranges with offset
                    to cell A1
           Product: LibreOffice
           Version: 4.1.6.2 release
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: newbie-02 at gmx.de

Description:
hello, 

absolute short for those who don't like to read: 

imho calc stores wrong values for 'field-number' in the filtering conditions in
ods file format once the referenced database area doesn't start in cell A1. 

relative short:  

compare the values for the 'table:field-number' vs. 'filterColumn colId' in: 

<table:filter-condition table:field-number="1" table:value="test33"
table:operator="="/>
and: 
<table:filter-condition table:field-number="-2" table:value="test33"
table:operator="="/>

stored as the filtering condition in the ods file attached with next comment
(open ods with packer, open content.xml inside with e.g. firefox, scroll to the
bottom), 

with those: 

<filterColumn colId="2"><customFilters and="true"><customFilter
operator="equal" val="test33"/>
and: 
<filterColumn colId="2"><customFilters and="true"><customFilter
operator="equal" val="test33"/>

stored in the same sheet saved in xlsm format one comment further (you find 'em
in the xlsm-file, open with packer, then xl/tables/table1.xml and table2.xml). 

'1' and '-2' can't be correct, '2' and '2' are correct (third column of
database area counted 0-1-2). 

please please please excuse my extensive writing, it's a tricky problem which
only occurs under certain circumstances and is 'hidden' in plenty cases, i'll
try to nail it down as good as possible but that needs some words. if the bug
is as it appears to me it might be the root cause of many 'filter' and 'sort'
and compatibility bugs, thus it's worth looking into. 

short I) and short II) are closely related - interdependent - that i think it's
right to handle them in one bug, 

short I: i've seen values for the '.field' (column) property of 'filterfield's
stored in the 'content.xml' part of .ods files which do not match the standard:
'zero-based value for the filtered column relative to the database area'. 

As i've seen even negative! values there ... something must be wrong. 

.xlsm files store different - correct - values. 

short II: values for theese fields accessible by basic / macros (filterfields,
filterfields2, filterfields3 in (get)FilterDescriptor) also differ from what
one would expect, thus it's often impossible to manipulate re-loaded filters by
macros (fresh applied filtering works ok).

the errors occur whenever a sheet is saved with an active filtered area, and
that area has an offset to cell A1 (sometimes it's correct on the first save
and wrong from the second one?). 

(database areas with their top-left cell on a 'diagonal line' A1-B2-C3-D4-E5-F6
... do not show this error, imho because col-offset and row-offset are equal
then and it doesn't matter if you swap them?) 

miscalculated saved filters can be reloaded as the miscalculations are
compensated by corresponding 'counter-errors' on load?

if you dis- and re en-able autofiltering on a loaded sheet it deals with
correct values in the area accessible from macros / basic, after save - close -
load they become faulty again. 

see sample attached to next comment. you can open the ods file with a packer -
e.g. 7-zip - and open the content.xml therein with e.g. firefox, the filter
fields are defined at the end of that file. they do not match the column
relative to the databaseranges - zero-based!. 

(beware .. sometimes they do! match, e.g. a filter for the rightmost column
isn't crippled with an offset > 1 from colA to the databaserange. that gave me
toggeling results and headaches.)

pls. recheck ... 

with patches to XMLExportDatabaseRanges.cxx: 

527c527
<         SCCOLROW nFieldStart = aParam.bByRow ? aRange.aStart.Col() :
aRange.aStart.Row();
---
>         SCCOLROW nFieldStart = aParam.bByRow ? aRange.aStart.Row() : aRange.aStart.Col();

and xmlfilti.cxx: 

416c416
<     SCCOLROW nStartPos = mrQueryParam.bByRow ? mrQueryParam.nCol1 :
mrQueryParam.nRow1;
---
>     SCCOLROW nStartPos = mrQueryParam.bByRow ? mrQueryParam.nRow1 : mrQueryParam.nCol1;

i could affect the filtered and re-loaded values, but haven't yet stable
results. 

(the patches are only exchanging Row / Col in the result of the 'if' statement,
thus setting the col-offset as nStartPos for standard (column oriented,
datasets by row) sheets.) 

but that were only poking tests ... it needs a recheck and - if bug - a
fundamental workthrough. 

if 'bug' then it is an old one, the issue strikes already in ver. 4.1.6.2., the
code there looks the same.  

AOO (4.1.7. portable) looks free of this flaw on first short sight, code looks
as if not yet somebody attempted to implement horizontal autofilters. that's
been the case for LO? 

what i couldn't yet manage is to assign correct values for the structures
accessible from the macro editor (dbranges(), getfilterdescriptor,
getfilterfields). that was my initial problem, those values deviated after a
save - close - load cycle whenever the database range startet off from the
'diagonal' line A1-B2-C3-D4-E5-F6-G7. can anybody tell where the calculations
for those fields (those shown in the integrated macro editor) are in the code?
datauno? 

tia for any help, 

b. 

Steps to Reproduce:
1. open attachement from next comment, 
2. check filtering situation, caution, fields are counted 0-based, 
3. open ods file with zipper then content.xml inside and check values stored
for 'field-number' at the end of file not matching column relative to database, 
4. check 'show .field value', 
5. switch autofiltering off and on again, 
6. apply a filter to the same column as before, 
7. check 'show .field value' now other (correct) value, 
8. save and close file, 
9. reload file, 
10. check 'show .field value' now wrong again, 
11. become confused ... 

Actual Results:
- wrong values for field-number stored in file, different from values for other
file formats (xlsm e.g. is correct), 
- wrong values for 'filterfield' in structures for basic macros after save -
close - load of a file with active filter, 

Expected Results:
- correct values saved to file, 
- correct values accessible by macros, 

Reproducible: Always

User Profile Reset: Yes

Additional Info:
tested with plenty versions from 4.1.6.2 to 7.0.0.0.a0+, earlier may be buggy
too, win and lin, all buggy,

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20200428/66563828/attachment.htm>