Created attachment 192929 [details]
Sample sheet must be compared between 7.5 & 7.6

Repro with 7.6.5.2. and in recent alpha.

No repro with 7.5.3.2.

@Colin,

It would be better to have more precise steps to reproduce. Since you have a better idea of what you expect to see and how you work with the list(s), please try to come up with a minimal set of steps to reproduce the problem. Hunch: headers should be visible, so to hide/show groups of columns and rows (more easily).

You could have a recent Developer version installed in parallel to your current main version; just remember not to run them both simultaneously (i.e. always close all LO windows of one version before running the other version, including Start Center).

<https://dev-builds.libreoffice.org/daily/master/current.html>

(In reply to ady from comment #2)
> Repro with 7.6.5.2. and in recent alpha.
> 
> No repro with 7.5.3.2.
> 
> @Colin,
> 
> It would be better to have more precise steps to reproduce. Since you have a
> better idea of what you expect to see and how you work with the list(s),
> please try to come up with a minimal set of steps to reproduce the problem.
> Hunch: headers should be visible, so to hide/show groups of columns and rows
> (more easily).
> 
The precise steps to reproduce  in 7.6.5.2 really are as simple as;

Unhide the defined range for the header "Fruit & Veg" - which is the local name for the defined range "Veg" - this will expose all relevant products.
Sort column B "Fruit & Veg" using any of the predefined sort criteria.
Observe the chaos

Perhaps a description of a previously encountered and reported bug may help.

If more than one range is defined in a column then they must be seperated by at least one cell. If there is no seperation zone and data is inadvertently entered into the cell(s) between tables then the upper range will overflow into the lower range. This will cause the filter function to consolidate both tables and act as if they were one table. the sorting will be upon everything in both tables - even to the extent of moving the "header" cell with its own filter to the (in)appropriate place in the list.

I don't know if there has been a remedy for this since I first reported the underlying bug - my workaround worked until this morning.

As mentioned, the only method of avoiding this is to have a clear demarcation zone between the lower row of the first table and the upper row of the next table ad infinitum.

As it's far too easy to inadvertently add something into the "forbidden zone" then each end zone had been Menu>Data>Validated as "data entry forbidden" with a contrived data validation of "must be a whole number greater than 999999999999 with empty cells permitted". This results in a STOP.

The first time I used the sheet after yesterday's update was 04:30 today and the moment I sorted on the product description it contrived to almost randomly modify the Data Groups - which previously permitted the products in the various departments to be concealed - and applied the sort across every bit of data in column B. Undoing the action returned the status quo and sorting on any of the adjacent filters didn't destroy the integrity of the tables.

As you can see, the values within the departments are accumulated and summarised in the cell adjacent to the department name.

My apoligies if I assumed that the moment somebody followed my instructions _ open the table and sort it, then the impact would have been obvious. Nobody would want to see a whole list of departments merged into one contiguous column with "randomised" data groups

Background story; the two stores are adjacent to each other and publish all their prices online. ICA gives us old 'uns an extra 5% discount so when I go shopping I just put in the prices and my shopping list then tells me where is the best deal and how much it should cost me.

Yes, the departments are in "walk through" order and the products similarly organised. I'm retired - what else am I gonna do?

Is that too much information ;)

(In reply to ady from comment #2)
> Repro with 7.6.5.2. and in recent alpha.
> 
> No repro with 7.5.3.2.
> 
>
> 
> You could have a recent Developer version installed in parallel to your
> current main version; just remember not to run them both simultaneously
> (i.e. always close all LO windows of one version before running the other
> version, including Start Center).
> 
I suspect this may not work as anticipated because the crashing also reported this morning leaves the soffice.bin running as a background process and would imply that under certain circumstances, the system may not be completely unloaded between versions.

(In reply to Colin from comment #4)

> I suspect this may not work as anticipated because the crashing also
> reported this morning leaves the soffice.bin running as a background process
> and would imply that under certain circumstances, the system may not be
> completely unloaded between versions.


Please check whether after a reboot of your OS, the soffice.bin left-overs are still there.

As for my request for simpler clear STR... You are describing the procedure assuming that any other user understands it. I'll try with an example, but please feel free (and encouraged) to improve it with even simpler or less steps.


0. The following steps should be followed, using LO 7.5.x and then repeated with LO 7.6.5+.

1. Open attachment 192929 [details].

2. Menu View > View Headers (or similar name of this menu item, depending on version). It should display the Column/Row Headers, and also the grouping icons on the upper-left corner of the spreadsheet near Column A Row 1.

2.1. Note the content of cells B6:B11. This is:

Potatoes – Baking
Mushrooms
Garlic
Clementines
Bananas
Apples Royal Gala

2.2. Note that there are additional (filtered) lists located on the same column, starting at cell B23, B32, B43... These lists can be visible in full using the grouping icons ("1", "2") described in step 2 above.


3. On cell B5 ("Fruit & Veg"), click on the filter (down arrow); click on "Sort ascending".

The expected result of the list (at least up until LO 7.5.x, included) should be:

Apples Royal Gala
Bananas
Clementines
Garlic
Mushrooms
Potatoes – Baking

... and the other lists (cell B23 and down) are in place.


But using LO 7.6.5, the actual (unexpected) result is:

Apples Royal Gala
Bananas
(hidden: Chorizo)
Clementines

Additionally, using LO 7.6.5, note that the lists that were located downwards are now also moved up and mixed.

HTH.

(In reply to ady from comment #5)
> (In reply to Colin from comment #4)
>  
> Please check whether after a reboot of your OS, the soffice.bin left-overs
> are still there.

It's difficult to induce the identical crash but from memory, if I forget to unload the leftovers following any crash then a subsequent PC restart will produce a "clean" environment for normal use.

> As for my request for simpler clear STR... You are describing the procedure
> assuming that any other user understands it. I'll try with an example, but
> please feel free (and encouraged) to improve it with even simpler or less
> steps.

I followed your description but unfortunately I already understand the procedure so it made perfect sense - but so did my original.

As it's agreed that it is indeed a bug I think it deserves a higher rating than medium. The crashing occurred randomly when I was removing a sheet to submit a sample. It was reproduced by everybody who assessed the report and a bandaid applied almost immediately.

The broken autofilter makes it impossible to work with almost all of my projects in anything later than 7.5.9 ALL of the time - not just when I try to submit a "doctored" sample to demonstrate the critical corruption.

BTW, the issue happens in sorting with the filter option, that also change corrupt the Menu/Data/Define Range for Veg, modifying it.
Seems option Menu/Tools/Options/LibreOffice Calc/General - Expand references when new columns/rows are inserter, doesn't affect here.

Sorting with Menu/Data/Sort, I think works fine and doesn't corrupt the ranges.

(In reply to m_a_riosv from comment #7)
> BTW, the issue happens in sorting with the filter option, that also change
> corrupt the Menu/Data/Define Range for Veg, modifying it.
> Seems option Menu/Tools/Options/LibreOffice Calc/General - Expand references
> when new columns/rows are inserter, doesn't affect here.
> 
> Sorting with Menu/Data/Sort, I think works fine and doesn't corrupt the
> ranges.

I'm just getting signs that it's far worse than that. I have a project going back to October 2020 with 8 data rows per day with cluster calculations over 14 columns. It wasn't immediately apparent that the sorting corruption affects the relationships - it somehow reset the parameter to have references updated by the sort so now I have 11000 rows with scrambled precedents. I hope I can recover from the last backup prior to March 4th and only have to re-source 250+ entries.

If I can properly identify the impact (downgrading may have reset some of the user prferences) I can update the bug report or write up some new reports but I reiterate my earlier observation in comment 6

######
As it's agreed that it is indeed a bug I think it deserves a higher rating than medium. The crashing occurred randomly when I was removing a sheet to submit a sample. It was reproduced by everybody who assessed the report and a bandaid applied almost immediately.

The broken autofilter makes it impossible to work with almost all of my projects in anything later than 7.5.9 ALL of the time - not just when I try to submit a "doctored" sample to demonstrate the critical corruption.
######

Clearly it isn't an isolated incident. It looks like any file using autofilter that was even activated during the corrupt phase is suspect. Anybody still using 7.6.5 and experiencing sort problems may already have irrepearably corrupted workfiles.

Further to my comment #8

I am aware that sorting rows with formulae referring to rows either above or below that row always ends up losing the reference point when the reference is to an array eg

C12 = SUM(D2:D6) C13 = SUM(D3:D7) C14 = SUM(D4:D8)

will lose its compass if sorting on row C or D even when the parameter to update references when sorting a range of cells is active. I always refer to each cell in the sum individually and if I have an "array" of similar calculations then I only need to refine one cluster and then it will automatically replicate when "drag filled".

C12 = D2+D3+D4+D5+D6

in that manner LO always knows where D4 will end up no matter where it sorted C12 or any of its precedents.

With this in mind I have ascertained that the corruption mentioned above was the result of restarting in "safe" mode to try to identify why grouping and ungrouping columns - F12 & [Ctrl]+F12 - was inoperative on sheets that had been subjected to the vagaries of 7.6.5. Subsequent restarting in normal mode still left the user profile somewhere in the pavement pizza of 11000 mishandled rows.

Why have I mentioned this? Because if somebody eventually fixes sorted defined arrays they may wish to consider doing it properly so arrays within function calls are automatically tracked. Or is that rocket science?

This seems to have begun at the below commit in bibisect repository/OS linux-64-24.2.
Adding Cc: to Caolán McNamara ; Could you possibly take a look at this one?
Thanks
 e1acec27d1c5311403c524680d60307efa76a85a is the first bad commit
commit e1acec27d1c5311403c524680d60307efa76a85a
Author: Jenkins Build User <tdf@maggie.tdf>
Date:   Sat Nov 11 20:37:40 2023 +0100

    source 4abe6c83e76f825319e8b2a0c0b8b8e92177da65

159238: sc: extend backcolor area | https://gerrit.libreoffice.org/c/core/+/159238

I believe this should be considered with bug145995