Description: 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 3.5.1.2 till today,
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, b.
*** Bug 133336 has been marked as a duplicate of this bug. ***
*** Bug 132488 has been marked as a duplicate of this bug. ***
*** Bug 132120 has been marked as a duplicate of this bug. ***
*** Bug 129105 has been marked as a duplicate of this bug. ***
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 3.3.0.4, 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 3.5.1.2 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. 3.5.2.2 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: [code] sub test ntemp = thiscomponent.databaseranges(0).filterdescriptor.filterfields(0).field end sub 'test [/code] 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. 3.5.2.2, 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.),
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, reg. b.
Regina confirmed in bug 133336 comment 13 -> NEW
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 MassPing-UntouchedBug
dear guys ... there is a "steps to reproduce" section in the bug report, pls. check yourself, I'm not motivated to refresh a buggy program every now and then just to see that no care produces no progress.
The attribute in file markup for autofilter is "table:display-filter-buttons" (19.624 part3 ODF1.4). Its description refers to the labels of rows or columns. That means, if a database range has row labels, autofilter control fields should be attached to these label cells and thus filter out columns. But that is not implemented in LibreOffice. I think the expectation in this report is wrong. A database range can have only one orientation. All cells, that belong to the same record, can either be arranged in a row or a column. As Autofilter always filters records, it will be never possible to use it to filter out a field of the database. As Autofilter is only implemented in LibreOffice for the case, that the database has the cells of a record arranged in a row, it will always conflict with a database, that has the cells of a record arranged in a column. So if the user combines these, LibreOffice has to decide which orientation to use for the database and the current decision is to use that one expected by the autofilter. So the bug is, that the user is not warned about the conflict and the current error message about missing labels has no option to abort applying autofilters. And it remains the request, that the implementation of autofilter needs to be extended to work with a database range that has an orientation with row labels. If you want to filter records of a database range, that has the cells of a record arranged in columns and thus has row labels, do not work on the database range itself, but use the new FILTER function to extract the wanted part of the database range into a separate range of cells.