Created attachment 160022 [details]
.ods file with wrong values for filtering columns,

Created attachment 160023 [details]
.xlsm file with correct values for filtering columns,

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.

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

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.

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

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

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