LibreOffice Sort Bug ?

users
#1

Hello at the nice people at LibreOffice! (especially you :slight_smile: )

I'm using LibreOffice version 4.4.1.2

I was very surprised to see a bug (?!) in the internal SORT function of Calc.

Please fill the following cells with a simple letter:
L17=Z
L18=B
L19=Y
L20=R
L21=Q
L22=P

Now select those 6 cells and click on the Down/Up arrow in the toolbar (Sort), select Sort Key 1 : Z, Ascending, then click OK. Expected result was B-P-Q-R-Y-Z, but the Z did not move at all ! =-O

This is undoubtedly a serious bug... and nobody has detected this before? How strange.

Hopefully I could be of help making the software still better!
Cheers
Richard

#2

I have 4.2.7.2 on Ubuntu 14.04 and you are correct, sort is not working.

I tried with several sets of letters:
p
a
b
q
r
z

The above is the sorted column.
a isnt first? hmmm

Wade

#3

When I reported it, I provided a great deal of information about it and
was told "Not a bug" and "works for me" by those who have the power to
close bugs.

My argument was/is that if you use the sort arrows (as you have done
here) then you're asking/telling LO to sort the selected rows with no
options.

Instead it was deemed correct that instead, LO assumes the first cell
found is used as a label regardless of any other settings.

This "feature" (*cough*) cost me a number of hours of work to get around
and avoid.

The argument is that since you can use the "Sort" menu option and change
this behavior *for each sort occurrence* then there's a work around for
the "feature" and we should be happy with that.

#4

**cough cough** .. feature understood.

#5

Hmm I just tried to sort going through the
sort menu and I still cant get it to sort
properly.

It does numbers just fine though.

#6

A quick look at the doco reveals...

Range contains column/row labels
– omits the first row or the first column in the selection from the sort

Untick this box and you are sorted.

Cheers

#7

If "Sort By" shows the data in the first cell then it is being treated as a column header. Go to the Options tab and correct the item regarding the sort range including the header.

Dave

#8

Hi,

while LO does allow a certain amount of customisation, at this point in the game this particular menu option can not be changed.

This then becomes a feature request, either

  * change the default
  * allow the sort options to be customised

Once a feature request is submitted it is really up to a (unpaid) programmer to step forward and take on the action. I can't see this being high on the programmer's list of priorities but it may be deemed an "easy hack" which an aspiring young person could field.

Cheers

#9

See, now *THAT* is an answer we can live with. Thank you!

I hate it but now I know what the problem (behind the scenes) is.

#10

Tim Lloyd's comment is not acceptable!
This is not a question of a (unpaid) programmer's likings or priorities -- this is a serious question of Libre Office's liability,
because both of the the sort buttons in the toolbar (asc & desc) give wrong results as well as there is no clear explanation why and how the sort command
in Data=> sort must be "customized" if yopu want a correct result.

A spreadsheet program that cannot be fully trusted is totally worthless!!

These "bugs" are a result of very bad planning and an obviously total lack of control & testing of the programming before it was accepted
as a feature in Calc and the complete LibreOffice suite.

Neither is this really not a "game" -- anyone who goes for to programming something for LibreOffice must feel the responsibility for the result -- as well as
the controlling team (if any?).

The sort feature is a very important feature why these faults have to be corrected immediately
>> the function of both the sort buttons set to "normal" sort of a selected range
>> all selections set to blanc in Data=>sort=>options
Pertti Rönnberg

#11

Pertti, all,

1) This seems to be an old known problem (see e.g. [1])

2) I just tested 3.4.1 (LibO+AOO) - its already present there, so IMHO it's
inherited from OOo

3) an easy Workaround exists (go over Data > Sort, choose proper column
label Options)

So IMO the next questions are,
- how important is this bug?
- is there general agreement about a default behavior / column label
recognition algorithm?

Then the bug could be pushed to a higher prio / dev visibility.

Nino

[1] https://bugs.documentfoundation.org/show_bug.cgi?id=77681

#12

Hi :slight_smile:
I think you meant that the background behind Tim's answer is not
acceptable. His actual answer seems very honest, truthful and useful. It
is not a voice of arbitrary authority but one of wisdom and counsel. One
problem with emails is that we don't hear the tone of voice nor any clues
from body-language. Of course a lot of us in IT don't process that sort of
stuff in face-to-face communications either but that's another story.

On this mailing list we have absolutely no control over what the devs do
nor how this project is run and we are almost never asked for our opinions
on what might be a good way. We can only give answers to try to help users
figure out how to use the suite as it is, and maybe grumble about oddities
and issues related, such as the fading dominance of the MS formats. We are
free to join other lists such as Discuss@ and the social media channels but
most of us on this mailing-list are only interested in just helping people
directly.

Several answers in this thread have been 'brutally' honest with no false
apologies or soft-pedalling the truth or giving it any spin (err, except
with a blatant cough rather than my usual sarcastic quotes ' ). From my
own experience i suspect many of the answers so far have been answered
off-list by one of the "higher ups" berating them and possibly threatening
to ban them from the lists.

To me Tim's answer seems a bit heroic, not quite on the scale of children
in any war-torn area, but still admirable. He makes a stand for truth and
honesty and that might have got him in trouble already.

However just because something is not done quite the same way as it's done
in MS Office or/and is not intuitive to some people does not necessarily
make it wrong.

The main problem here, in my opinion, is that it wasn't easy to figure out
and documentation may not have been easy enough to get to. Of course now
that we have had this answer on the list it will probably crop up again
quite soon and we have a quick answer ready.

Regards from
Tom :slight_smile:

#13

Hi :slight_smile:
Err the fading dominance of MS formats is good but the "dominance" part is
really annoying and causes a lot of unnecessary agro.
Regards from
Tom :slight_smile:

#14

Hi :slight_smile:
Personally i think that it is quite annoying.

If i select something to sort i tend to leave out any stuff i don't want
sorted. Similarly if i want to make some things bold. I select the things
i want to make bold and leave out the rest!
Regards from
Tom :slight_smile:

#15

opinion FWIW: I tend to use sort on an entire sheet (using data>sort), and I nearly always have a column headings row at the top; I get narked when, coming to repeat a sort on a sheet, I find that LO has "forgotten" that I have previously checked the "has headings" box and ends up sorting the headings into the data - so I definitely prefer the "has headings" to be default. (It mightn't be *quite* so bad if one didn't have to go to an options tab in order to see what the current setting is - it's just too easy for feeble-minded people like me to forget to do that!). So it would definitely be good to be able to rely upon the setting being unchanged since the last sort.
It would also be good to have the possibility to set the required behaviour on a per-sheet basis.
OTOH, sorting a selected range, as has been the case in this thread, is a different kettle of fish - in that case, the default should definitely be to assume no column headings and to sort everything that's given.
A thought for future development: maybe best of all would be good to be able to mark a row (or column) as being a headings row (or column) and hence be excluded from any sort automatically. This would have the advantage that more than one row (or column) could be used for this purpose. It would also have the advantage of making that "contains headings" checkbox redundant, whilst avoiding problems of the kind that have been experienced here.
Just some thoughts...
/Gary

#16

It's perhaps worth a reminder that, in the sense that you mean it, spreadsheets can never be "trusted". Since much of their essential functionality is hidden (formulae, formatting, significant options) and they are untestable, you should never rely on the results of a spreadsheet. Your judgement, then (but not mine) is that spreadsheet programs generally are "totally worthless".

See, for example:

http://lemire.me/blog/archives/2014/05/23/you-shouldnt-use-a-spreadsheet-for-important-work-i-mean-it/
"I will happily use a spreadsheet to estimate the grades of my students, my retirement savings, or how much tax I paid last yearÂ… but I will not use Microsoft Excel to run a bank or to compute the trajectory of the space shuttle. Spreadsheets are convenient but error prone."

http://baselinescenario.com/2013/02/09/the-importance-of-excel/
"But while Excel the program is reasonably robust, the spreadsheets that people create with Excel are incredibly fragile."

http://mba.tuck.dartmouth.edu/spreadsheet/product_pubs_files/Literature.pdf
"Among those who study spreadsheet use, it is widely accepted that errors are prevalent in operational spreadsheets and that errors can lead to poor decisions and cost millions of dollars."
"Panko summarized this literature by reporting that 94% of spreadsheets have errors, with an average cell error rate of 5.2%."

Brian Barker

#17

This is undoubtedly a serious bug... and nobody has detected this
before? How strange.

Hi,

With older versions of LO and with OpenOffice you can define named
ranges which remember their sort orders, filter arguments and options,
so called database ranges. Somewhere in the LO development process the
feature got lost. You can still select a cell range, call
menu:Data>Define... and specify that this should be the list named
"List" and that it has no column labels on top. But this setting has no
effect. As soon as you push any of the sort buttons or call
menu:Data>Sort, the automatic detection identifies a first row of text
values as column labels.

This is how the auto-sort buttons [A-Z] and [Z-A] work:

https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=72449&p=326195

And like always: Even the most simple list is way easier to handle in a
dBase connected database. Lists saved in a database integreate almost
seamlessly in spreadsheets if you still find some reason why you want to
process your text data in an arithmetic calculator.

Hope this helps a litte. As a work-around I would simply add column
labels on top of every list.

Cheers, Andreas

#18

1) This seems to be an old known problem (see e.g. [1])

It's older than that by a bit, I reported it back in 2012 I think. (I'd
have to hunt it down.) However, "known" isn't as accurate as we'd like
if people seem to find this as "new" on a semi-regular basis.

2) I just tested 3.4.1 (LibO+AOO) - its already present there, so IMHO it's
inherited from OOo

Also, from my perspective, I'm not accusing/blaming those working on
this for it having happened, it did, time to move on, now I'd like to
get it fixed.

3) an easy Workaround exists (go over Data > Sort, choose proper column
label Options)

A point I'd tried to make back "then" but got no where with was that
it's not an "easy" work-around when you have to do it multiple times
over and over in the process of using a collection of data means you
waste a *lot* of time during this "work around" which, when you have to
do it multiple times, stops being easy and becomes long and tedious.

I have a spreadsheet of ~450 entries, one for each machine in a data
center. Sometimes I have to sort it on the names, sometimes the serial
number, sometimes the rack location, etc. In my cases, when I was
building this list, to, EACH TIME, bring up the menu option, go and
click off *AGAIN* the button to choose the second page of the dialog,
click the "label" button, then click "Ok" *AGAIN* wasted a lot of time
and introduced new places for me to trigger an error in the process when
really, "assuming" the minimum choices was the correct approach.

Maybe that's what we need, a button to set the sort options, just once
to "smart" or "standard" meaning don't *ass*u*me* anything when trying
to decide what to do with the data.

So IMO the next questions are,
- how important is this bug?
- is there general agreement about a default behavior / column label
recognition algorithm?

Then the bug could be pushed to a higher prio / dev visibility.

The "philosophical" arguments I'd like to make is that using the
assumption of "lowest common expectation" (for lack of a better term) is
that you assume the least number of optional choices that you can unless
explicitly told otherwise.

As someone previously stated, if I select/highlight a number of words in
a word processor to effect change on them such as bold/italic/underline,
I expect the operation to be done in the simplest of terms, I don't want
you to avoid doing capital letters because that's an option somewhere I
didn't expect.

#19

I meant to say, I'd be happy with the menu buttons performing the sort
using the last chosen options.

#20

Hi :slight_smile:
That is exactly the most perfect use-case that would be better in a
database than in a spreadsheet.

What makes it even more perfect is that Base can use the existing
spreadsheet as an external back-end. This means that while you struggle to
get Base set-up you can keep using the existing spreadsheet in the way you
are familiar with. Also at any random moment in the future, even after
Base is set-up, you can just use the spreadsheet method that you are using
all the time at the moment.

Regards from
Tom :slight_smile: