Bug 133529 - Calc: ui: filesave: conflict between sort and filter, unclear use of tag 'table:orientation'
Summary: Calc: ui: filesave: conflict between sort and filter, unclear use of tag 'tab...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.5.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
: 129105 132120 132488 133336 (view as bug list)
Depends on:
Blocks: Calc-DataRange
  Show dependency treegraph
Reported: 2020-05-30 21:10 UTC by b.
Modified: 2023-07-21 16:16 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description b. 2020-05-30 21:10:25 UTC
all of the following happened for defined 'database-ranges' (<data - define range>) which do not start on the 'diagonal' cells A1-B2-C3-D4-E5-F6, as it's a result of 'row-offset' and 'column-offset' of the range being different, and the wrong of them is used for some calculations, 

while working on tdf#133336 it came up that filter and sort definitions depend on the definition of the 'table:orientation' ('row' by default, 'column' can be set), 

sort has an option to set 'horizontal' sorting, see 'options' tab in the dialog <data - sort> (left to right, sort by values in one row, changing the sequence of the columns), 

this orientation is marked by something in memory at runtime, and by a tag 'table:orientation="column"' on save of the sheet. 

it's conflicting with the definition of filters, which also evaluate and affect the orientation of the table-range, 

this results in some irregularities: 

- if you set 'sort' new and select the key first, then go to the tab 'options' and change the direction and confirm with ok on that tab, sometimes the sorting is not done, on a recheck you see the key is set to 'undefined', 

- if you additionally filter a range with horizontal sorting (this automatically happens vertically), a wrong key is displayed for a recheck of 'sort', and the sorting direction is set back to 'top to bottom', 

- starting with the first save, wrong and not standard-conform values for 'table:field-number' are written to the files for sort, and starting with the second save for filter, 

- reading in filters stored in files works only partially, for access via the UI two errors cancel each other out, for access via basic / macros the filterdescriptor.filterfields(x).field values are useless, !!that's my main concern!!

i don't know spontaneously how to solve this, the orientation is obviously used by two procedures - sort and filter - each according to its own gusto, and a change in one area harms the other, 

Steps to Reproduce:
1. spread values 1 to 9 in B3:D5, 
2. apply a name to that range <data - define range>
3. sort this range by some row, horizontally, 
4. recheck the sort, same key and direction proposed, 
5. add a filter <data - autofilter - push-button - select a value> 
6. recheck the sort, wrong key proposed, recheck the sort direction, is reset to 'top to bottom', 
7. this detail is the fastest to check, the .field values not functional is the bad impact to work of macro programmers, 

Actual Results:
settings of orientation for sort and filter conflicting, 

Expected Results:
settings for sort and filter independent and all options working, 

Reproducible: Always

User Profile Reset: Yes

Additional Info:
all versions from till today,
Comment 1 b. 2020-06-02 08:12:34 UTC
hello @Regina: 

sorry for the mail bombing the other day, i didn't realize that it's possible to discuss on git and the mails from gerrit automatically expanded a reply to multiple persons, 

as you are the expert for the standards - ? - you may be able to help with this problem by explaining if 'sort' or 'filter' should have the leadership over 'table:orientation', or if there is a possibility to use multiple tags 'orientation' ... or how to avoid mutual interference, 

as long as the current situation contains a conflict, and i don't know how it *should* be, i'm blocked from improving anything, 

also a hint to someone else who knows about it and might be able or willing to help, or to the corresponding documentation would be helpful, 

imho saving in .xlsm format is free of such conflicts as it simply doesn't store any sort info? 

a sort info 'horizontal' (orientation="column") stored in *.ods is still washed away in ver, 7.1 once you additional apply a filter to that range, 

many thanks in advance, 

Comment 2 b. 2020-06-05 06:10:40 UTC
*** Bug 133336 has been marked as a duplicate of this bug. ***
Comment 3 b. 2020-06-05 06:16:47 UTC
*** Bug 132488 has been marked as a duplicate of this bug. ***
Comment 4 b. 2020-06-05 06:19:41 UTC
*** Bug 132120 has been marked as a duplicate of this bug. ***
Comment 5 b. 2020-06-05 06:21:07 UTC
*** Bug 129105 has been marked as a duplicate of this bug. ***
Comment 6 b. 2020-06-06 02:45:33 UTC
after some more investigation: 

0. the conflict between sort and filter reg. being 'master of the table orientation' is older, oldest version i could get my hands on is, already buggy, 

1. i could find the same conflict in apache openoffice 4.1.7, thus the 'root cause' is inherited from OO, filter 'auto-harming' itself (wrong read in and write out of filter definitions) is LO special and doesn't appear in AOO, 

2. up to version the bug only affected sheets / ranges where you defined a filter and a sort in different orientation, horizontal sort (left to right) messes up the .field values for the filter, 

3. starting with ver. something changed, at least the 'preset' for bByRow in the database range (xmldrani.cxx), and the bug started messing up the .field values on read of files which had been saved with active filtering, without needing 'sort' to harm, 

4. a 'follow up bug' is that starting with a save after reload the filter writes the tag/flag 'table:orientation="column"' to the range definition in files, and accordingly wrong values for the fields to get them back to the right buttons despite being calculated against the wrong start value, 

5. steps to test / verify: 

5a. spread integers 1 to 9 in B3:D5, e.g. B3:1, B4:8, B5:2, C3:5, C4:6, C5:7, D3:4, D4:9, D5:3, 

5b. define a range for that area <data - define range> name it e.g. b, 

5c. apply auto-filtering to that range <data - filter - autofilter> (menu varies slightly across versions), 

5d. set a filter on the first column (B), e.g. select '2' to be shown, 

5e. check the .field value for the filtering with the following macro: 

sub test
ntemp = thiscomponent.databaseranges(0).filterdescriptor.filterfields(0).field
end sub 'test

put that in your macro file under tools - macros - edit macros - basic macros or similar, place the cursor on ntemp and press F7 to get a 'watch' on the variable, step through the macro with F8, see '0' to show up for the watch, it's the correct value for 'first column of range, zero-based' 

5f. apply a horizontal sort to that range, <data - sort - options - left to right - ok>

5g. recheck the filterfield with the macro, you'll get '1' as a wrong value, would reference to col. C if used by macro / basic, 

6. conclude sort and filter are conflicting, 

7. only sort can set orientation=column, but filter can reset it, and both evaluate the 'orientation', if they disagree (vertical filter / horizontal sort) setting one of them will harm a previous set of the other, 

8. in whatever attempt to change anything, starting with ver., filter 'auto-harms' it's definition by a mistake in the read in and subseq. mistake on re-save of files - (in ods format!, as that's the (only?) format holding orientation definitions), 

10. the initial conflict appears before write to file in runtime, 

11. it's two different - but closely related - problems, 
11a. sort-filter conflict reg. orientation, and 
11b. filters problem how to read in and write range definitions, 

12. each of them can be solved by either: 
12a. different 'orientation-definition' for sort and filter, in memory and in files, or 
12b. 'filter' ignoring the orientation definition handeled by 'sort', in memory and in files, (in detail: perform it's own and independent calculation of .field values against he correct - column - offset / startCol.),
Comment 7 b. 2020-06-08 15:00:07 UTC
i'll try to requeue the patch i tried for tdf#133336, i didn't yet find a better solution and would like some progress ... 

i've outcommented the bIsSortedColumns check in test/sort/sheet/xdatabaserange.cxx as i see no use case for it, but some other improving with the patch, 

the patch contains a long comment describing the situation and improvement, it's not neccessary, i'm hoping it'll help against hasty "not okay" verdicts,  

i'd be happy about a careful review, 

the basic error, conflict between sort and filter, can probably only be corrected by changing the tdf standard, or by changing the application of the standard by calc, 

some use cases are better with the patch than without, no use cases are worsened, see list below, 

i checked: proposal for sort orientation, proposal for sort field, filter correct in gui, filter.field values correct for basic, tag 'table:orientation' in content.xml file, property table:field-number in content.xml, for creating, saving, reload, re-save and re-re-load of: a databaserange without sort or filter applied, a databaserange with an active filter, a databaserange fresh sorted vertically, a databaserange fresh sorted horizontally, a databaserange first filtered and then sorted vertically, a databaserange first filtered and then sorted horizontally, a databaserange first sorted vertically and then filtered, a databaserange first sorted horizontally and then filtered, 

comparison with patch - without patch: 
number - use case - runtime - first save - reload - second save - reload

without patch: 
1. - dbrange - ok - ok - !ok - !ok - !ok (proposal for sort: horizontal/tag "column"), 
2. - dbrange+filter - ok - ok - !ok - !ok - !ok (orientation/tag "column"/.field values), 
3. - dbrange+sort-vertical - ok - ok - ok - ok - ok, 
4. - dbrange+sort-horizontal - ok - ok - ok - ok - ok, 
5. - dbrange+1filter+2sort-vertical - ok - ok - ok - ok - ok, 
6. - dbrange+1filter+2sort-horizontal - !ok - !ok - !ok - !ok - !ok (orientation/tag "column"/.field values), 
7. - dbrange+1sort-vertical+2filter - ok - ok - ok - ok - ok, 
8. - dbrange+1sort-horizontal+2filter - !ok - !ok - !ok - !ok - !ok (proposal for sort messed/no tag orientation), 

with patch: 
9. - dbrange - ok - ok - ok - ok - ok, 
10. - dbrange+filter - ok - ok - ok - ok - ok, 
11. - dbrange+sort-vertical - ok - ok - ok - ok - ok, 
12. - dbrange+sort-horizontal - ok - ok - ok - ok - ok, 
13. - dbrange+1filter+2sort-vertical - ok - ok - ok - ok - ok, 
14. - dbrange+1filter+2sort-horizontal - !ok - !ok - !ok - !ok - !ok (filter: orientation/tag "column"/.field values), 
15. - dbrange+1sort-vertical+2filter - ok - ok - ok - ok - ok, 
16th - dbrange+1sort-horizontal+2filter - !ok - !ok - !ok - !ok - !ok (sort: proposal for sort messed/no tag orientation), 

problems can still occur with a - wrong? - use of table:orientation for 'eastern languages' (right to left or bottom to top?), i have no knowledge about this, 


Comment 8 Buovjaga 2020-10-16 15:26:17 UTC
Regina confirmed in bug 133336 comment 13 -> NEW
Comment 9 QA Administrators 2022-10-17 03:29:56 UTC
Dear b.,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword

Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team