Bug 88002 - EDITING: Sorting ignores end column with formatting only
Summary: EDITING: Sorting ignores end column with formatting only
Status: RESOLVED DUPLICATE of bug 126678
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Katie Chan
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Sorting
  Show dependency treegraph
Reported: 2015-01-03 23:14 UTC by Katie Chan
Modified: 2022-02-11 10:10 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:

Sample doc showing bug (43.98 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-01-04 15:33 UTC, Katie Chan
LibreOffice Calc Sort Behaviour comparison (193.81 KB, image/jpeg)
2022-02-09 21:11 UTC, Katie Chan
results with LO 7.4 (191.79 KB, image/png)
2022-02-10 00:14 UTC, Jean-Baptiste Faure

Note You need to log in before you can comment on or make changes to this bug.
Description Katie Chan 2015-01-03 23:14:34 UTC
When sorting a selection with an end column that has formatting but no content (using sort key on the other columns), the sort acts as if the end column was not selected.

Encountered in 4.3.5, not an issue in 4.2.8 or previous versions I have used.
Comment 1 raal 2015-01-04 11:04:10 UTC
I can not confirm with my file on LO 4.3.5, win7 and linux. 

Please send us a sample document, as this makes it easier for us to verify the bug. 
I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document.
(Please note that the attachment will be public, remove any sensitive information before attaching it.)
How can I eliminate confidential data from a sample document?
Thank you
Comment 2 Katie Chan 2015-01-04 15:33:32 UTC
Created attachment 111726 [details]
Sample doc showing bug

Attached sample document to reproduce bug.

Compare selecting A1 to D20 and sort by "Name" which works as intended, versus selecting A10 to D20 only and sorting by "Column B". The background formatting (i.e. background color) of Column D should sort along with columns A-C but it doesn't in 4.3.5.

I have reproduced it in a clean Win8.1 on a VM with only CamStudio and LibreOffice installed. LO 4.2.8 was first installed and tested, and then upgraded to 4.3.5 by installing on top.

You can see a 26 seconds video showing the test on 4.3.5 at http://www.ktchan.info/files/libreoffice/bug88002.avi if you wish.
Comment 3 Buovjaga 2015-01-04 15:51:52 UTC
Reproduced per instructions in comment 2. LibO 4.4 RC1 on Win 8.1 32-bit.
Windows 8 only?

I'll try on Win 7 & Ubuntu later.
Comment 4 Buovjaga 2015-01-04 17:23:27 UTC
On this it sorts correctly: Ubuntu 14.10 64-bit Version:
Build ID: 430m0(Build:2)

These exhibit the wrong sorting behavior:

Win 7 64-bit and Version:
Build ID: 57626f2132f73e4e42b31e364b25c5867336e718
TinderBox: Win-x86@42, Branch:master, Time: 2014-12-26_09:26:33

Ubuntu 14.10 64-bit Version:
Build ID: f92183833fa569006602ac7e93c906d2094e0d4d
TinderBox: Linux-rpm_deb-x86_64@46-TDF-dbg, Branch:master, Time: 2014-12-14_00:21:45

Must be a regression introduced after, added whiteboard for bibisect request.
Comment 5 Michael Weghorn 2015-02-06 22:34:04 UTC
bibisect result:
 9926afee22c71ba349b8b643ca984a2635156bc0 is the first bad commit
commit 9926afee22c71ba349b8b643ca984a2635156bc0
Author: Miklos Vajna <vmiklos@collabora.co.uk>
Date:   Sat Dec 6 07:51:11 2014 +0100

    2014-12-06: source-hash-d0894ff58fbdd823273bc91939801971b7a03182

:100644 100644 b77b245ad79f2a67c5607a7c856e2210fb11dc4d e822b31ac888fe05a1382ab8adfe406b1baab047 Mbuild-info.txt
:040000 040000 d42be0798b2b820135be1bda9fef0b1cb5fe81ca c54d17cfb88aae06452faf8148d02a8f491e39a3 Mopt

y$ git bisect log
# bad: [ad5ed3c0a2b049c70515c303e8150f5a75dd818f] 2015-02-06: source-hash-004304eb2fd1703d22dec0abf0170bb2ce493d0c
# good: [e4b0a61cedc6ac0e65a4a110fd83edd8295f4856] 2014-11-20: source-hash-d273a60bfdbf9bb7623bed38667ec0647753157c
git bisect start 'master' 'oldest'
# bad: [09b2fa5b1f166e0d6f077325f2ea5fec8c061b47] 2014-12-29: source-hash-4c9cf98819037fdb70cbe68f678f6498d5646736
git bisect bad 09b2fa5b1f166e0d6f077325f2ea5fec8c061b47
# bad: [cc11b8ff286074f3edcc650b231c4ed3edaebc2f] 2014-12-09: source-hash-887cb59ac4bfca94f310baee3e9da58ccf9cb3e3
git bisect bad cc11b8ff286074f3edcc650b231c4ed3edaebc2f
# good: [82d2ef2b66746aac43c054f440e7a4b79b4cc215] 2014-11-29: source-hash-03c049a093c9ed0451099325d73429b010e71594
git bisect good 82d2ef2b66746aac43c054f440e7a4b79b4cc215
# good: [1185944aa3de25f09176f2c87e8afdf9266efe24] 2014-12-04: source-hash-183b89db121b8c6e02c14ebf2c2666ee807a9c82
git bisect good 1185944aa3de25f09176f2c87e8afdf9266efe24
# bad: [9926afee22c71ba349b8b643ca984a2635156bc0] 2014-12-06: source-hash-d0894ff58fbdd823273bc91939801971b7a03182
git bisect bad 9926afee22c71ba349b8b643ca984a2635156bc0
# good: [9c5629e92e7fdb9506e193e95f048b2dbd07517c] 2014-12-05: source-hash-6b096f273ac9d7bbe93d2cb083958b3a04866d73
git bisect good 9c5629e92e7fdb9506e193e95f048b2dbd07517c
# first bad commit: [9926afee22c71ba349b8b643ca984a2635156bc0] 2014-12-06: source-hash-d0894ff58fbdd823273bc91939801971b7a03182

I also changing "Hardware" to "All"/"All" because it occurs on Debian GNU/Linux as well - and on x86, too.
Comment 6 Matthew Francis 2015-09-03 02:49:21 UTC
This seems to have begun at the below commit.
Adding Cc: to erack@redhat.com; Could you possibly take a look at this one? Thanks

    commit 1e4235f8b2dc693b0fb1edade9db25a631bdbf94
    Author:     Eike Rathke <erack@redhat.com>
    AuthorDate: Fri Dec 5 18:36:03 2014 +0100
    Commit:     Eike Rathke <erack@redhat.com>
    CommitDate: Fri Dec 5 19:06:29 2014 +0100
        actually use identical code for both byRow and byCol, fdo#81501 follow-up
        Change-Id: I982e03a12dd80be0787f22dce4495065775e7de0
Comment 7 Robinson Tryon (qubit) 2015-12-13 11:12:19 UTC Comment hidden (obsolete)
Comment 8 Xisco Faulí 2016-09-26 15:53:54 UTC
Adding Cc: to Eike Rathke
Comment 9 Xisco Faulí 2017-09-29 08:50:29 UTC Comment hidden (obsolete)
Comment 10 Xavier Van Wijmeersch 2017-09-29 09:55:38 UTC
I can reproduce it as explained in comment 2

Build ID: 8d6dd32d58494cc21c32bc3c4798fdd4593bde08
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group


Build ID: 19910c461230f70bb9e98ad44db3525f0d755724
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 11 andrehe 2018-07-29 00:55:50 UTC
I suggest that sort has been optimized to not reorder empty cells, and a cell with only formatting is considered to be an empty cell which is the problem.
The example shows that formatting is not forgotten during reordering, just that this step is skipped with a cell lacking content.

I can reproduce it as explained in comment 2 for the following versions:

Build ID: 1:6.0.3-0ubuntu1
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); Calc: group

Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-US (en_US.UTF-8); Calc: CL

Build ID: b3972dcf1284967612d5ee04fea9d15bcf0cc106
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-US (en_US.UTF-8); Calc: CL

Build ID: 5a7d18e432571fb1e9b2cd54d6168353d25c6f19
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-07-26_22:07:10
Locale: en-US (en_US.UTF-8); Calc: CL

Build ID: c838ef25c16710f8838b1faec480ebba495259d0
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-US (en_US.UTF-8); Calc: CL

Build ID: 490fc03b25318460cfc54456516ea2519c11d1aa
Locale: en-US (en_US.UTF-8)

=> main window does not open, cannot test

=> main window does not open, cannot test
Comment 12 Eike Rathke 2018-08-15 11:53:37 UTC
If there is no data (only empty cells) in the rightmost column(s) then those columns are entirely omitted from the sort operation (for cases where users select whole rows or other ranges that include more columns than actually filled to prevent unnecessary reordering of only empty cells).

Looks like this shrinking can not be done if Sort Option "Include formats" is activated and formatting is present in those otherwise empty ranges.
Comment 13 QA Administrators 2019-08-19 06:55:23 UTC Comment hidden (obsolete)
Comment 14 Katie Chan 2019-08-20 22:54:52 UTC
Yes, the bug/regression is still around.

Version: (x64)
Build ID: 057fc023c990d676a43019934386b85b21a9ee99
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
Locale: en-GB (en_GB); UI-Language: en-US
Calc: CL
Comment 15 Robert Lacroix 2020-08-25 21:27:32 UTC
*** Bug 107779 has been marked as a duplicate of this bug. ***
Comment 16 b. 2020-08-26 05:40:07 UTC
there are some 'targets' for calc, performance, usability, 'understandability', 'standard', respect the manual?, and often mentioned compatibility - to ex$el, 

(compatibility to prior versions is also important, but would block any changes except additions)

at least ex$el compatibility is missed with the current behaviour, 

ex$el 2010 treats empty cells - with formatting! - selected either by selection or by 'all' as part of the range to work on and re-arranges them together with the others, keeping 'row-integrity', 'column integrity' (when sorting left to right), or 'dataset integrity', 

i had no chance to check if cells without any attributes are reordered, but there is no big difference if they are sorted or not,
Comment 17 Jürgen Kirsten 2021-01-10 16:11:08 UTC
Hello everyone, 

I also noticed this bug and was planning to publish it here. During the search I noticed this case here.

When optimising the sorting area, it is not only reduced at the end of the area with empty columns, but also at the beginning. Also, the same behaviour when sorting in columns.

Excel and Google Spreadsheets do it "right".

Meanwhile, two sort-options have been created:
- Include boundary column(s) containing only comments 
- Include boundary column(s) containing only images 

You could add another option for the boundary column(s) containing only formatting. You could also combine it with the comments option. So:

- Include boundary column(s) containing only comments or formatting

This would then also solve the problem of compatibility with previous versions.

Have fun programming.
Comment 18 Jean-Baptiste Faure 2022-02-09 14:55:27 UTC
I don't understand why this bug report is still open when it has been indicated (comment #12) that the sort option "Include formats" allows to take into account empty cells with format.

So closing as WorksForMe.
If you do not agree, please argue why you think the sort option "Include formats" do not allow to make the sorting working as expected.

Best regards. JBF
Comment 19 Katie Chan 2022-02-09 19:09:15 UTC
It was still open because the regression in behaviour going from 4.2.8 to 4.3.5 remains.

Using the attachment from comment #2, previously if you sort A10 to D20 by "Column B", Calc does not decide to only worry about A10:C20 because it doesn't think there's any content in D10:D20.

On the basis of consistency in behaviour, sort should not return differently if you select A1:D20 compared to a the fewer rows but same columns A10:D20. Formatting such as colour is data. The program is optimising where it shouldn't.

"Include formats" does not solve this issue. Selected, Sort is moving the formating (such as colour) with the rest of the content of the cell, but that's after it has reduced what it is sorting (and hence moving) from A10:D20 to A10:C20 because it think there's no content including any formatting despite the colour.
Comment 20 Jean-Baptiste Faure 2022-02-09 20:28:24 UTC
Well, Calc does not think, it does what the commands chosen by the user are programmed to do.

Please, add a screen copy of what you expect when you sort the range A10:D20 of your test file.

Status has been set to NEEDINFO, please set it back to UNCONFIRMED once requested information has been provided.

Best regards. JBF
Comment 21 Katie Chan 2022-02-09 21:11:46 UTC
Created attachment 178177 [details]
LibreOffice Calc Sort Behaviour comparison

When a subset of cells are sorted by the same criteria, a user should expect to see the same result in the user selected subset of cells compared to the result of having the superset of cells sorted. If the sort range include at least one cell with user entered characters in a column when "Include formats" is selected in the Options, everything in that row are moved by the sort including any formatting such as cell colour in an otherwise empty cell.

This was the behaviour in 4.2.8 and earlier. And in fact, this is also the behaviour where the formatting only row is NOT the left-most or right-most column selected. Where the formatting only row is left most or right most, that left-most or right-most column with formatting only is not sorted along with the rest of the selected range.
Comment 22 Katie Chan 2022-02-09 21:24:06 UTC
Ugh, by "formatting only row", I of course meant "formatting only column" both times in the second paragraph in comment #21 above. Sorry for typo.
Comment 23 Jean-Baptiste Faure 2022-02-10 00:14:46 UTC
Created attachment 178180 [details]
results with LO 7.4

I do not know how you perform the sort, but here is my results.
The attachment shows the same results as your expected results. LO 7.3 and LO 7.4 give me the same results. The attachment shows my sort options.

Best regards. JBF
Comment 24 Katie Chan 2022-02-10 19:49:00 UTC
I've just tested with a few versions on a fresh Windows 10 VM. So it appears that this was fixed going from 7.1.8 to 7.2.1.

Guess which version I was still running without realising when I tested it yesterday. Oops!

Thanks all.

I've changed the status to Resolved - Fixed.
Comment 25 Jean-Baptiste Faure 2022-02-11 09:07:01 UTC
(In reply to Katie Chan from comment #24)
> I've just tested with a few versions on a fresh Windows 10 VM. So it appears
> that this was fixed going from 7.1.8 to 7.2.1.
> Guess which version I was still running without realising when I tested it
> yesterday. Oops!
> Thanks all.
> I've changed the status to Resolved - Fixed.

Thank you for testing again with an updated version.
As we don't know the commit that fixed the problem, the correct status is Resolved - WorksForMe

Best regards. JBF
Comment 26 Xisco Faulí 2022-02-11 10:10:20 UTC
This was fixed by https://cgit.freedesktop.org/libreoffice/core/commit/?id=774a61afa9fc281290e7bfad4e28c05978b82d73
Closing as duplicated of bug 126678

*** This bug has been marked as a duplicate of bug 126678 ***