Bug 132488 - calc: fileopen: ods: xml: macro: filesave: filtered ranges messed up in .ods format after save-close-load cycle, data import buggy, affects filters, e.g. autofilter, in database ranges with offset to cell A1
Summary: calc: fileopen: ods: xml: macro: filesave: filtered ranges messed up in .ods ...
Status: RESOLVED DUPLICATE of bug 133529
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-04-28 12:04 UTC by b.
Modified: 2020-06-05 06:16 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
.ods file with wrong values for filtering columns, (11.75 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-04-28 12:13 UTC, b.
Details
.xlsm file with correct values for filtering columns, (9.01 KB, application/zip)
2020-04-28 12:14 UTC, b.
Details
list of calc file formats and their capabilities reg. autofilter to 'survive' save-close-reload cycles (15.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-21 17:17 UTC, b.
Details

Note You need to log in before you can comment on or make changes to this bug.
Description b. 2020-04-28 12:04:45 UTC
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,
Comment 1 b. 2020-04-28 12:13:56 UTC
Created attachment 160022 [details]
.ods file with wrong values for filtering columns,
Comment 2 b. 2020-04-28 12:14:40 UTC
Created attachment 160023 [details]
.xlsm file with correct values for filtering columns,
Comment 3 b. 2020-05-05 11:55:28 UTC
after some more investigation: 

the initial save (new file - define dataarea - apply autofilter - filter - save) works correct, the values for 'table:field-number' in the .ods - content.xml file are relative to the data range as expected, 

on load of the saved file the filtering conditons accessible from the header row (dotted triangle buttons) are applied correctly, but the structures 'filterdescriptor', 'getfilterfields' accessible from macros deal with wrong numbers, mostly off from what it should be by +col-offset (not subtracted), -row-offset (wrongly subtracted), but not everytime, imho somebody stopped values too far off by 'saturating_add', just be aware to get irritating results, 

on a re-save of the file messed up on load also the values saved to the file get messed up, 

similar happens to 'sort', 

once you had introduced an 'anonymus_dataarea' - at any time? - by applying autofilter without first defining a dataarea / data range things work somewhat different, making analysis even more tricky, 

reg. 

b.
Comment 4 b. 2020-05-06 11:25:23 UTC
after some more investigation: 

(all said here please to be re-checked, i'm on the limit of my knowledge and analytical talents)

on the first save of a file the defined data-ranges are saved !!! without !!! the following tag: 

table:orientation="column"

on subsequent saves after re-loading the file the data-ranges get this tag in their xml definition, 

despite one may think this tag is ok or 'not required' as it's the default? it does! affect the evaluation of filtering conditions on load, 

on load of a file with a defined filter condition two (or more) properties must be implemented in the representation: 

1. - the column being filtered, and from that marking the right buttons 'blue-triangle-dotted' to access the filtering from the UI, imho internally working with absolute values for the column number, 

2. - the value used for .field in the sc...filterdescriptor...filterfields struct, imho values relative to the defined database area, 

problem: having the tag - table:orientation="column" - set in the file toggles which of 1.) or 2.) is correctly applied, the other will be 'glitched', 

thus: 

- while initial saving and subsequent saving produces different format - with effect! on import of filtering conditions - it's impossible to construct a stable and consistent solution, 

-- either first and subsequent saves should produce the same output, or 

-- the evaluation should be in a manner that the parameter 'column' is optional, 

once that is solved and if the deviating evaluation of filtered column and .field stay alive one can dig for the reason ... 

code-tag: sc/source/filter/xml/xmlfilti.cxx and everything 'around', 

i'd be very! thankful if someone with knowledge how it should be could step in and give some help ...
Comment 5 b. 2020-05-09 05:32:09 UTC
same happens to parameters for 'standard filter', 
- initial save: correct .field values, no 'orientation' tag, filter works, 
- re-load and then save: orientation "column", wrong .field values, 

(i don't know yet if .field values for standard filter are accessible for users, thus can only check in the files)

see snippets from files, (had to edit tabs by "   ", between **[ and ]**: comments
first save: 
<table:database-ranges>
   <table:database-range table:name="dataarea01" table:target-range-address="Sheet1.B3:Sheet1.D6" table:display-filter-buttons="true" table:on-update-keep-styles="true" table:on-update-keep-size="false">**[comment: no 'orientation-tag' here]** 
      <table:filter>
         <table:filter-and>
            <table:filter-condition table:field-number="1"**[comment: correct value to filter second column in range]** table:data-type="number" table:value="2" table:operator="="/>
         </table:filter-and>
      </table:filter>
   </table:database-range>
</table:database-ranges>

save after re-load of above file: 
<table:database-ranges>
   <table:database-range table:name="dataarea01" table:target-range-address="Sheet1.B3:Sheet1.D6" table:display-filter-buttons="true" table:on-update-keep-styles="true" table:on-update-keep-size="false" table:orientation="column">**[comment: now with! 'orientation-tag']**
      <table:filter>
         <table:filter-and>
            <table:filter-condition table:field-number="0"**[comment: miscalculated value for second column]** table:data-type="number" table:value="2" table:operator="="/>
         </table:filter-and>
      </table:filter>
   </table:database-range>
</table:database-ranges>

reg. 

b.
Comment 6 b. 2020-05-14 10:32:01 UTC
from 

http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#__RefHeading__1418550_253892949

i read: 

------------ 
19.686.3<table:database-range>

The table:orientation attribute specifies whether data fields are organized in row or columns.

The defined values for the table:orientation attribute are:

    ●column: organization is by columns 

    ●row: organization is by rows 

For a <table:database-range> 9.4.15 element the default value for this attribute is row.

The table:orientation attribute is usable with the following element: <table:database-range> 9.4.15.

The values of the table:orientation attribute are column or row.
------------

apart from the fact that it is not self-explanatory (what is the meaning of 'organization is by ...'? the different fields (attributes) of one record are next to each other in a row - 'by row'? or corresponding fields of the different records are in columns one below each other - 'by column', or vice versa (oriental / asian notation?) records are from top to bottom or bottom to top, per record 'by column', or their comparison and key fields are next to each other accordingly - 'by row', 

i'll just guess that 'by row' means records row by row, accordingly filter criteria by column!, 

in no case should for one file with kept data and structure 'by column' - being defined as an exception to the default value - should be accounted sometimes and sometimes not, because that changes the evaluation in a nonsensical way - by chance - 

thus it's neccessary to find which module in which circumstances writes this to the files, 

but the 'bug before' is that on read in of a correctly written? file from initial save the .field value is assigned or calculated wrongly, 

if any of the pros would like to point me to a module or code sequence ... open file, read in values, create data structure, create data range structure, create filter structure, assign filter values, acess by ui (buttons), make available for basic, access by macro, from where to take values for save, which module calculates .field values and 'table orientation' for save ... i would be very grateful and continue with efforts to find the root cause ...
Comment 7 b. 2020-05-21 17:17:55 UTC
Created attachment 161091 [details]
list of calc file formats and their capabilities reg. autofilter to 'survive' save-close-reload cycles

after some more investigation: 

quite sure that the initial fail is the reload of a previously correct saved *.ods file, on which: 

1. wrongly a table-orientation "column" is applied, 
2. wrong numbers are calculated for the 'filterfields[2|3].field' values, 
3. wrong values and a wrong 'xml orientation tags' "column" are saved in subsequent saves / copies of the file, 

thus corrected subject / title for not to loose interested people who do one check and say 'ok', the first save is! ok, mess starts after reload, 

checked some other file formats, see attachement, 

as after three weeks no interest and no help i have to try other roads (a solution in 8 years is of no help) ... thus 'user forum niveau': 

there are four formats working correct for this task, see attached file, i'll try to use (oo)xlsx, if no other issues occur that's a viable workaround, 

i'm unsure what to do with this bug, from 'viable workaround' i tend to set importance to minor, 

weighing the embarrassment that calc has been storing crap for many years in its very own format and only works cleanly with the format of the competition ... and yet wants to 'look good' compared to the competition ... critical is not high enough ... 

as this bug affects a fundamental functionality of the filtering system and has the potential to screw up plenty other issues and can cause other problems as well as block the fixing of other bugs I think critical is right ... 

an experienced QA member should decide ...
Comment 8 b. 2020-06-05 06:16:47 UTC
it boils down to something being unclear about the use of the flag/tag 'table:orientation="column"', described there: 

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

as of now that's the main problem to be solved, thus setting this duplicate,

*** This bug has been marked as a duplicate of bug 133529 ***