Bug 107385 - Autofilter sorting breaks rows data alignment without warning
Summary: Autofilter sorting breaks rows data alignment without warning
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsDevAdvice
Depends on:
Blocks: AutoFilter
  Show dependency treegraph
 
Reported: 2017-04-24 13:02 UTC by Luca
Modified: 2022-06-27 03:28 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Luca 2017-04-24 13:02:03 UTC
Description:
Autofilter sorting doesn't affect non autofiltered columns, there is no warning, the result is a complete mess up of a spreadsheet.


Big, big mess.

Solution: for the Autofiler function, just copy MS Excel behaviour in every aspect. It's perfect.

Steps to Reproduce:
1) Fill in three columns with sortable data
2) Setup three column labels (let's say A, B, C)
3) Select only A and B column labels (leave the C column out of the selection)
4) Select Data/Autofilter, to setup autofilter labels on A and B, but not C.
5) Click on the A lable
6) Select Sort/Descending (or Ascending, depending on how you sorted data in the first place)


Actual Results:  
Only the A and B column get sorted, while the C column sticks to the original sorting. Raws data alignment is broken. Big mess. 

Then: 
7) Select a cell in the C column
8) Select Data/Sort...

Autofilter labels on A and B columns get lost.

Expected Results:
Just copy MS Excel behaviour. It's perfect. 
- Raws alignment should never be broken without warning
- Autofilter labels should never disappear after a sorting operation 
- Sorting should always affect either the whole spreadsheet, or the selected columns/group of cells.


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:52.0) Gecko/20100101 Firefox/52.0
Comment 1 Cor Nouws 2017-04-24 13:37:40 UTC
Users always have the freedom to create a mess ;)
But you propose a warning such as
 "You've set a filter for rows XYZ, that you are now going to use.
  Mind that the row AB next to it, is not included and will be left out of
  the action.
  Do you want to continue or do you want to extend the filter to include
  column AB?
   [continue] [adapt filter]   [] do not show this warning again"

?
Comment 2 Cor Nouws 2017-04-24 13:38:48 UTC
/me a bit worried about all other areas and scenarios that we 'should' cover with like wise warnings..
Comment 3 Luca 2017-04-26 10:41:41 UTC
(In reply to Cor Nouws from comment #1)
> Users always have the freedom to create a mess ;)

Nope. Please, go to step 4, then try selecting whichever area you want (column, group of cells, or select all) then apply step 5 and 6, look what happens. You have no control whatsoever on the area to be sorted: sorting always applies to all columns with the autofilter label, and never to the other columns, no matter which area you selected before sorting. So, if you wanted to create *your* mess, you couldn't. It's the program that *always* create the mess of its own choice. 

> But you propose a warning such as
>  "You've set a filter for rows XYZ, that you are now going to use.

Nope. I've set a sorting order, not a filter. The behaviour of the function here should be coherent with the Data | Sort ascending and Data | Sort descending functions behaviour.

>   Mind that the row AB next to it, is not included and will be left out of
>   the action.

Nope. I've set a sorting order for column A, either all columns should be affected, or just column A. Warnings and selection extensions should be exactly the same as for Data | Sort ascending and Data | Sort descending.

>   Do you want to continue or do you want to extend the filter to include
>   column AB?
>    [continue] [adapt filter]   [] do not show this warning again"
> 
> ?

Nope. I just don't want to have *two* different sorting function, with two different and incoherent behaviours, one of them always breaking records by design, and without warning. 

I don't like MS Excel, and I don't use it, bit this is very very bad design in LibreOffice.
Comment 4 Roman Kuznetsov 2018-12-13 14:33:37 UTC
it isn't a bug but expected behaviour.

Eike, may be you have some opinion about this?
Comment 5 Oliver Grimm 2019-06-20 11:46:09 UTC
confirmed here in 6.1.5.2

I second the reporter's remark regarding the inconsistency ("Warnings and selection extensions should be exactly the same as for Data | Sort ascending and Data | Sort descending.")
Comment 6 Jean-Baptiste Faure 2019-06-22 21:24:00 UTC
I do not see this behavior as a bug. It is up to the user to select all the columns that are linked. In case you can have several independent blocks of data sharing the same rows, it's clear that Calc can't know what columns are linked to the column in which the sort is performed.

Best regards. JBF
Comment 7 Oliver Grimm 2019-06-23 20:29:52 UTC
The bug reporter and me have given our reasons why we consider this a usability bug.
In contrast, under which practical use case do you expect a user to deliberately destroy a linkage between the columns of one row in a table?
If there is a significant use case I have not seen before you may be able to convince me :-)
Comment 8 Eike Rathke 2019-06-24 09:24:23 UTC
Same warning / offering similar selection extension as in Sort may make sense. However, note that AutoFilter can be applied starting at the current selection, so extending the range above might not be wanted at all.
Comment 9 kokbira 2020-01-17 21:37:17 UTC
Well, it happened to me...

I was searching for issues on data stored from an inventory software, OCS. So I extracted a CSV from all columns of about 1200 inventoried computers, applied autofilters, sort from HOST NAME column and did a lot of operations.

After some great 8h headache searching for the cause of some errors on a sheet, I found that LibreOffice Calc make wrong on sorting columns, because IP and GATEWAY columns did not show right values.

I thought to change LibreOffice suite for another one because that issue happened with all LO Calc versions I tested.

So, searching on www, I found that bug report that fixed my problem - I forget to add autofilters to two columns.

Thanks a lot :)

About sorting columns, without the use of autofilters, I knew from LO Calc or Microsoft Excel that it will sort all columns selected or, if none is selected, all columns near that are not separated by an empty column.

So I thought LO Calc followed that behavior on autofilters, and it was my error. I rarely use autofilters on MS Excel to compare, so I do not know if LO Calc is "cloning" its behavior...

Then I agree to warn user about that behavior or to change that behavior to something more intuitive (like a label "sort only filtered columns", or a popup warning that the operation will sort only filtered colums, or an popup with two options - to sort all columns or only those filtered etc.).
Comment 10 Luca 2020-04-19 17:47:49 UTC
It did it again! 

Even if I obviously knew about this bug, I did a normal select all+sort operation without relizing that there was one column without autofilter header. Result: total mess, and I relized it only after closing the file, no undo available. I lost at least a whole day of work, and I consider myself lucky because I was able to rebuild the column in one day of extra work. I could have lost everything.

Please, please, please, all you LibO programmers, please, if you absolutely want to keep this weird and incoherent behaviour, again, please, come down from your ivory tower and add a tiny damn little warning when a *normal* operation is about to break the integrity of a row and destroy the spreadsheet.

Thank you!
Comment 11 b. 2020-05-18 09:05:23 UTC
@Mike, sorry, first part ot, relevant for the bug below, 

------------ 
@kokbira and @Luca: 

no backup no mercy, 

i suggest at least 'always create backup copy' and 'save auto recovery information every xx minutes', 

and! a manual backup before! every important step at least every hour, 

if you don't want to go to the trouble of clicking it together yourself again and again, the small macro below - e.g. placed on a shortcut via 'customize keyboard' - can make your work much easier. it saves a timestamped copy in the subdirectory 'backup' of the folder from which the document was loaded, without affecting the current work, e.g. filename of current work is kept and undos are still possible. 

sub save_a_timestamped_copy

' extract filename ----------------------------------------------------
  sParts      = Split(thiscomponent.getURL(), "/")
  filename    = sparts(UBound(sparts))

' insert "backup" to store in different folder ------------------------
  storeurl    = join(split(thiscomponent.getlocation, filename), "backup/" & filename)

' append timestamp to name for the copy -------------------------------
  timestamp   = format(now,"\_yyyy-mm-dd\_hh-mm\.")
  storeurl    = join(split(storeurl, "."), timestamp)

' save a copy with the new name ---------------------------------------
  ThisComponent.StoreToURL(storeurl, Array())

end sub 'save_a_timestamped_copy
------------ 

for the bug: 

there are! use cases where (other) users don't want to sort or filter data neighboured to that they want to act on, 

autofilter is quite aggressive in overriding users range-selection in plenty cases, far too aggresive imho, e.g. automatically adding rows below selected or defined ranges messing up users totals and footnotes, 

you have fallen into one narrow gap where autofilter takes the user's instructions into account - which in your case did not correspond to the user's intention :-(, 

changing te behaviour to throw a warning makes sense, 

a behaviour that gives the user more control is also desirable, 

and 'streamlining' the handling between data-sort, autofilter, autofilter-sort and other filters would also produce happier users, (it's limited as filtering acts on whole rows, i know), 

taking more control / influence away from the user imho is 'not good', 

perhaps there is one error in autofilter, might be ... it's intended to have automatic extension into neighboured columns, and that is mistakenly applied to rows?? #132488 suspects that something fundamental is odd in the handling of 'ByRow' and 'ByColumn'.
Comment 12 Luca 2020-06-08 10:35:21 UTC
(In reply to b. from comment #11)
> @Mike, sorry, first part ot, relevant for the bug below, 
> 
> ------------ 
> @kokbira and @Luca: 
> 
> no backup no mercy, 

Obviously you don't understand the nature of this bug and its destructive potential, since you lecture us about how to make backups. This is the kind of bug that can destroy any recursive backup, because possibly you realize that Calc destroyed the rows integrity only after having backed up the whole mess.

> for the bug: 
> 
> there are! use cases where (other) users don't want to sort or filter data
> neighboured to that they want to act on, 

Sure. That's why you can *usually* select a set of columns before applying the sorting in Calc. That's exactly why this is a serious bug: Calc *in this case* does not honour the user's selection choice and arbitrarily decides to sort what it likes. If this behaviour doesn't change, there *must* be a warning here.

> you have fallen into one narrow gap where autofilter takes the user's
> instructions into account - which in your case did not correspond to the
> user's intention :-(, 

Nope. It's quite the opposite.
Comment 13 b. 2020-06-08 12:35:11 UTC
(In reply to Luca from comment #12 and OP)

> Obviously you don't understand the nature of this bug and its destructive 
> potential, ... 

it may be that i'm on the wrong trail, let me explain my understanding: 

> This is the kind of bug that can destroy any recursive backup, because 
> possibly you realize that Calc destroyed the rows integrity only after having 
> backed up the whole mess.

??? imho calc wont harm a backup from before your daily work in the evening, won't harm a backup from 13:30 at 14:00, and won't harm a backup from before sorting with the sorting, consider external backups or time stamped backups with the script from c#11, the one 'always create a backup copy' you can activate in calc is not sufficient for important work, and has the drawback to kill your undo buffer ... 

> Sure. That's why you can *usually* select a set of columns before applying the 
> sorting in Calc. That's exactly why this is a serious bug: Calc *in this case* 
> does not honour the user's selection choice and arbitrarily decides to sort 
> what it likes. If this behaviour doesn't change, there *must* be a warning 
> here.

from your OP: 

> Autofilter sorting doesn't affect non autofiltered columns, 

> 4) Select Data/Autofilter, to setup autofilter labels on A and B, but not C.
> 5) Click on the A lable
> 6) Select Sort/Descending (or Ascending, depending on how you sorted data in 
> the first place)

> Actual Results:  
> Only the A and B column get sorted, while the C column sticks to the original > sorting. Raws data alignment is broken. Big mess. 

what do you expect calc to do after you explicitly! told it to work on columns A and B ??? do something other? it does it often enough, in this case it doesn't ... you decided the target, don't complain, 

> 7) Select a cell in the C column
> 8) Select Data/Sort...

> Autofilter labels on A and B columns get lost.

that's because you don't work with 'defined database ranges', in such cases calc constructs one - only one! - 'anonymous' database range per sheet for the users comfort, but changes that every time it's used for another area, thus somewhat unstable. solution: <data - define range - select the area you want to work in>, and calc will work in a way that's easier to understand and where the user has a handle to steer it, 

i'm not against a warning as proposed in c#1, but that's not a critical bug but an enhancement request, 

from c#3: 
> Nope. Please, go to step 4, then try selecting whichever area you want 
> (column, group of cells, or select all) then apply step 5 and 6, look what 
> happens. You have no control whatsoever on the area to be sorted: sorting 
> always applies to all columns with the autofilter label, and never to the 
> other columns, no matter which area you selected before sorting. So, if you 
> wanted to create *your* mess, you couldn't. It's the program that *always* 
> create the mess of its own choice. 

no ... imho it's a three step process, in step 1 you define col A+B as autofilter target, in step 2 you select another range, in step 3 you work with the autofilter defined in step 1, the range selected in step2 doesn't have an autofilter ... quite easy to get other behaviour, select new target area, apply <data - autofilter>, sort with autofilter, it worked for me, 

or ... dont use autofilter for sorting, there is <data - sort> which will work independent from your range definition ... 

if i'm wrong just tell me, what exactly is a fail! in calc's behaviour, for the moment i'd say 'sharp knifes carry risks', and with defined ranges you can reduce your risk, 

> Nope. It's quite the opposite.

you selected col. A and B as target, and got calc to work there, that's the small gap where calc couldn't stop you from making your faults ... 

sorry if i'm wrong, but then your description may have shortcomings too ... 

reg. b.
Comment 14 Luca 2020-06-09 01:26:14 UTC
(In reply to b. from comment #13)
> (In reply to Luca from comment #12 and OP)

> ??? imho calc wont harm a backup from before your daily work in the evening,

Then you just lost your daily work, the moment you saved the mess and closed the file. Please, drop it, this is not the place to learn how to do backups. If you want to keep on arguing about this you'd better write to me in private.

> > 4) Select Data/Autofilter, to setup autofilter labels on A and B, but not C.
> > 5) Click on the A lable
> > 6) Select Sort/Descending (or Ascending, depending on how you sorted data in 
> > the first place)
> 
> > Actual Results:  
> > Only the A and B column get sorted, while the C column sticks to the original > sorting. Raws data 
> > alignment is broken. Big mess. 
> 
> what do you expect calc to do after you explicitly! told it to work on
> columns A and B ??? 

No I didn't. The autofilter column menu item that I selected doesn't say "Sort all and only autofiltered column ascending/descending". It reads "Sort ascending/descending". Full stop. The expected behaviour is the standard applied to any other command with the same "Sort ascending/descending" indication: apply sort to what I selected, and issue a warning when "The cells next to the current selection also contain data".

From the help file:
| The AutoFilter function inserts a combo box on one or more data columns that 
| lets you select the records (rows) to be displayed.

It says "select RECORDS". What Calc programmers fail to understand is that the integrity of the record is sacred. The program should NEVER brake the integrity of a record, unless the user EXPLICITLY told it to do that. They ignore this rule and they cause users' data loss. This is not the only case, also see bug 107779.
Comment 15 QA Administrators 2022-06-27 03:28:40 UTC
Dear Luca,

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