Bug 61634

Summary: Calc: Strange data subtotals auto range selection issue
Product: LibreOffice Reporter: crxssi
Component: SpreadsheetAssignee: Not Assigned <libreoffice-bugs>
Status: NEW --- QA Contact:
Severity: minor    
Priority: medium CC: gerard.fargeot, jmadero.dev, tmacalp
Version: 3.4.3 release   
Hardware: Other   
OS: All   
Whiteboard:
i915 platform: i915 features:
Attachments: Subtotal/sort bug spreadsheet with macro
Screenshot

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 4.0.1.2 (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 4.0.0.3 under RHEL 6.2.
Broken on LibreOffice 4.0.0.3 under Mandriva 2010.0
Broken on LibreOffice 3.5.7.2 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]
Screenshot

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
GerardF,

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 3.6.5.2 on Arch Linux (current)
and
LibreOffice 4.0.0.3 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

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.