Bug 58000 - Inconsistent alphabetization between Base and other apps
Summary: Inconsistent alphabetization between Base and other apps
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2012-12-07 21:32 UTC by David F Smith
Modified: 2013-12-15 22:57 UTC (History)
1 user (show)

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

Database file containing a table with a list of titles (6.59 KB, application/vnd.oasis.opendocument.base)
2012-12-07 21:32 UTC, David F Smith
Spreadsheet comparing the sorting behavior of Base, Calc, and MS Excel (16.03 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-12-07 21:33 UTC, David F Smith

Note You need to log in before you can comment on or make changes to this bug.
Description David F Smith 2012-12-07 21:32:31 UTC
Created attachment 71158 [details]
Database file containing a table with a list of titles

Base alphabetizes a list of text items in a way that is inconsistent with the behavior of Writer and Calc (and also inconsistent with Microsoft Word and Excel).  The attached file (Alphabetical Order.odb) illustrates this.

The database contains a table, List, with a single column of hymn titles, all of which begin with I.  When the table is displayed in Base, either by Opening it from the table view or by a query, and then sorted on the Name column (by clicking the "Sort Ascending" button on the toolbar), the list is sorted in a way that uses commas and apostrophes but disregards spaces.  For example, "I, the Lord", "I'm Gonna" and "I've Got" come before any other title, and "If You" comes between "I Am" and "I Greet".  This is not the order that most people would expect when alphabetizing a list of titles.

For the record, the same order is produced by the SQL statement 
Select "Name" from "List" order by "Name"

When the database table is copied into a Calc spreadsheet and sorted there, the order is different, with spaces, apostrophes, and commas treated as sorting characters.  The most obvious result is that all titles beginning with the single word "I" appear first in the list.  That's what I would expect.

The sorting done by MS Excel and Word is similar to Calc, but apostrophes are disregarded.

The Chicago Manual of Style devotes part of chapter 16 to alphabetizing, describing the two main methods as letter-by-letter (like Base) and word-by-word (like Calc).

The attached file Alphabetical Order Comparison.ods shows the result of sorting the same list of titles in Base, Calc, and MS Excel.  (Writer is identical to Calc on this test, and Word is identical to Excel.)  In my opinion, the order produced by Base is weird for a list of titles, although it may be appropriate for other text.  (For a column containing only single words, it makes no difference.)  In any event, I would prefer to see consistency among the LibreOffice apps, and ideally would like to be able to control the sorting method (word-by-word or letter-by-letter, at least) with a program option.

(Note: my installation of LibreOffice uses the default database, presumably HSQL.  I don't know how to verify that that's the case.)
Comment 1 David F Smith 2012-12-07 21:33:26 UTC
Created attachment 71160 [details]
Spreadsheet comparing the sorting behavior of Base, Calc, and MS Excel
Comment 2 Robert Großkopf 2012-12-07 22:15:53 UTC
There is no possibility to change this behavior with the internal HSQLDB. If you link Base to a MySQL-database it is sorting like a MySQL-database sorts the values. The sorting of MySQL looks like the sorting of Calc. 
The sorting is the output of a query of the database. So it could not be changed by Base. If you wish a consistent sorting-order, you have to change the database, for example to MySQL.
I set this bug to "Resolved" and "Notourbug", because it's a behavior of the included database. This behavior could be changed, when the database included in Libre Office would be changed.