Bug 61634 - Calc: Strange data subtotals auto range selection issue
Summary: Calc: Strange data subtotals auto range selection issue
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.3 release
Hardware: Other All
: medium minor
Assignee: Not Assigned
Depends on:
Blocks: Function-Subtotal
  Show dependency treegraph
Reported: 2013-02-28 20:09 UTC by crxssi
Modified: 2021-12-03 04:28 UTC (History)
3 users (show)

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

Subtotal/sort bug spreadsheet with macro (42.98 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-03-01 17:34 UTC, tmacalp
Screenshot (134.73 KB, image/png)
2013-03-01 18:52 UTC, m.a.riosv

Note You need to log in before you can comment on or make changes to this bug.
Description crxssi 2013-02-28 20:09:25 UTC
There is some unusual relation between Data Sort and Data Subtotals that can cause minor issues under certain circumstances.  Here is an illustration:

* Create a new spreadsheet.
* Start in A1 and enter several names going down.
* Now start in B1 and enter several matching numbers going down.
* Data-> Sort and check that it automatically selects all your data.
* Also note in Options that "Range contains column labels" is unselected.
* Perform the sort for column A.
* Now insert a row before row 1 for a header row.
* Add appropriate headers, like "Name" in A1 and "Dollars" in B1.
* Data-> Subtotals
* Note that the first row is not included in the automatically selected range.  At this point is is impossible to perform the data subtotals correctly and you have to cancel the whole operation.

The workaround is to select the range manually, BEFORE activating Data-> Subtotals.  I have verified this is a regression from OpenOffice 3.2.1 which did not have this problem.
Comment 1 m.a.riosv 2013-02-28 21:08:34 UTC
Hi crxssi,

following your instructions, works right for me with:
Win7x64 Ult.
Version (Build ID: 84102822e3d61eb989ddd325abf1ac077904985).
Comment 2 crxssi 2013-02-28 23:28:34 UTC
Thanks for testing.  Not sure why you could not replicate.  I don't think it would be platform specific....

I just tested it again, following my instructions:

Broken on LibreOffice under RHEL 6.2.
Broken on LibreOffice under Mandriva 2010.0
Broken on LibreOffice under Mageia Linux 2

Not broken on OpenOffice 3.4.0 under Mageia Linux 2
Not broken on OpenOffice 3.2.1 under RHEL 6.2
Comment 3 tmacalp 2013-03-01 00:11:10 UTC
I've been able to confirm that this bug appears as far back as LibreOffice 3.4.3 using Windows XP.

Here is another way to reproduce the error:

* Start a new spreadsheet
* Starting with cell A1, enter some data into column A, and numbers into column B
* Data -> Sort
* Sort by any criteria, just hit "OK"
* Insert a new row 1
* Add headings to row 1 (something like "Name" and "Age")
* Click around to make sure nothing is selected
* Data -> Subtotal

Notice that the actual header rows are ignored and the two items listed in the box "Calculate subtotals for" list the first row of DATA instead of the headers like they should.

It appears that whenever the Subtotals function is called, it's limiting the selected region of data, probably to cut out the header row.  But the actual Subtotal function is already ALWAYS cutting out the header row. It's impossible to run a subtotal on a section of data that doesn't include a header row, but that's another story.
Comment 4 m.a.riosv 2013-03-01 03:52:25 UTC
Works fine, even clicking out of data, before sort after insert row 1, or before call subtotal.
And also works fine in:
OpenSUSE 12.2
LibreOffice 3.5 build-413
Comment 5 crxssi 2013-03-01 04:19:18 UTC
(In reply to comment #4)
> Works fine, even clicking out of data, before sort after insert row 1, or
> before call subtotal.

Somehow I think you are just doing something differently than I am.  Guess we will have to wait for more than just three people's experiences.
Comment 6 tmacalp 2013-03-01 17:34:19 UTC
Created attachment 75745 [details]
Subtotal/sort bug spreadsheet with macro

I've attached a spreadsheet with a macro to show the behavior.

All you need to do is click the button labeled "Run Macro" and then open the subtotal dialog to notice that the headings are not being used.

You should notice that the categories listed in the subtotal dialog are taken from the actual data and are not using proper headings.
Comment 7 m.a.riosv 2013-03-01 18:52:24 UTC
Created attachment 75754 [details]

This is what I see after run your macro and select Menu/Data/Subtotals.
Comment 8 GerardF 2013-03-01 20:14:30 UTC
I think i've found why this "bug" is reproducible or not within same version by different users.
Depends on profil :

Tools > Options > LO Calc > General,
"Expand references when new columns/rows are inserted"

Reproducible if checked
Not reproducible if not checked

So i think this is a normal behavior. Range remains the same if the option is not checked.
May be we can close this bug ?
Comment 9 tmacalp 2013-03-01 22:14:33 UTC

Changing that setting does indeed explain the difference in behavior that we're seeing.

I'm getting the opposite behavior that you indicated. These were my results:

Reproducible if not checked (LO Default)
Not reproducible if checked

I've tested these results using
LibreOffice on Arch Linux (current)
LibreOffice on RHEL 6.1

I have 2 issues with this bug being closed:

1. "Expand references when new columns/rows are inserted" is disabled by default. Enabling this feature can cause other unintentional side-effects. If enabling this option was preferred, it probably would be enabled by default.

2. Whether there is a work-around or not, it is inconsistent and buggy behavior. When you call for a Subtotal, THAT is when it should be figuring out the proper data range, not based off of some out-dated/incorrect range that was calculated previously (when the row was inserted?)

How can this be the closed as the intended default behavior?
Comment 10 crxssi 2013-03-01 22:32:56 UTC
"Expand references when new columns/rows are inserted" might change the behavior, but based on my understand of that option, it really shouldn't.  From the help documentation:

 "Specifies whether to expand references when inserting columns or rows adjacent to the reference range. This is only possible if the reference range, where the column or row is inserted, originally spanned at least two cells in the desired direction.  Example: If the range A1:B1 is referenced in a formula and you insert a new column after column B, the reference is expanded to A1:C1. If the range A1:B1 is referenced and a new row is inserted under row 1, the reference is not expanded, since there is only a single cell in the vertical direction. If you insert rows or columns in the middle of a reference area, the reference is always expanded."

That all has to do with inserting rows and columns and having it affect/adjust existing references/formulas.  In my reported bug example, at the point before the subtotals function is activated, there are no formulas or references at all.  And no rows or columns are being inserted by the subtotal function.

The fact that the "Expand References" option affects the behavior at all is very curious (and helpful observation), but it is not really a solution, nor does it explain why this is happening.  Plus, that option is not selected in OpenOffice when I tested there, and yet there were no such issues with subtotaling in OpenOffice, just LibreOffice...

Also, based on the description of the "Expand References" option, I would never want that option "on", anyway.  I am guessing most people would not want that "on".
Comment 11 crxssi 2013-07-26 16:26:56 UTC
Tested again in LO 4.1.  There has been no change in behavior with this issue.  Needs at least to be marked "confirmed"
Comment 12 Joel Madero 2013-07-26 19:53:20 UTC
Due to comment 3 updating version
Comment 13 QA Administrators 2015-04-01 14:42:22 UTC Comment hidden (obsolete)
Comment 14 tmacalp 2015-04-01 15:17:47 UTC
I tested this bug in LO under 32bit Fedora 17 and can confirm that this bug is still reproducible.
Comment 15 tommy27 2016-04-16 07:28:32 UTC Comment hidden (obsolete)
Comment 16 tmacalp 2016-04-18 02:32:54 UTC
I can still reproduce this bug in

Build ID: Arch Linux build-1
CPU Threads: 6; OS Version: Linux 4.5; UI Render: default; 
Locale: en-US (en_US.UTF-8)
Comment 17 QA Administrators 2017-05-22 13:26:40 UTC Comment hidden (obsolete)
Comment 18 QA Administrators 2019-12-03 14:09:44 UTC Comment hidden (obsolete)
Comment 19 QA Administrators 2021-12-03 04:28:36 UTC
Dear crxssi,

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