How does sorting a list work in LibreOffice?

[Renel]

J’utilise LibreOffice 7.4.6.2 sous Windows 11.
.
I have a form named ‘FActresses’ associated with a table named ‘TActresses’ which contains a field named ‘NameActress1’. The value ‘Yes’ is selected for parsing the SQL statement and the sort value is “NameActress1” ASC.
.
The form contains a field named ‘NameActress’ associated with the field 'NameActress1 of the table of the form. The form also contains a control table containing a column associated with the ‘NameActress1’ field of the form table.
.
However, the sorting seems illogical to me, here is a region of the list of names in this column:

Bae Geu Rin
Bae Hae Sun
Baek Jin Hee
Beak Ji Won
Bae Nu Ri
Bae Susy

This sorting should logically look like this:

Bae Geu Rin
Bae Hae Sun
Bae Nu Ri
Bae Susy
Beak Ji Won
Baek Jin Hee

What is the problem? Is this a bug or is there something I’m not getting?

This bad sorting occurs in different places in the list of names for this column.

Can someone enlighten me on this.

[Villeroy]

menu:Tools>SQL…

SET DATABASE COLLATION "Korean"

menu:View>Refresh Tables.

SELECT N, ID FROM PERSONS ORDER BY N

qListboxNames|N|ID|
| — | — |
|Bae Geu Rin|0|
|Bae Hae Sun|1|
|Baek Jin Hee|2|
|Bae Nu Ri|4|
|Bae Susy|5|
|Beak Ji Won|3|
names.odb (3.9 KB)

[RobertG]

@Renel : Which (internal) database do you use? I will get the “logically look” directly with internal Firebird …

[Villeroy]

I don’t understand the logic, but here it is:
names_FB.odb (6.9 KB)

[Renel]

@Villeroy, in this column of the control table, the list is a sequence of romanized Korean names and should normally be sorted like any French or English name and all the names (a few hundred) are already associated with a created identifier when entering data; it is absolutely impossible that the ID and the name can be represented as in your example with names placed in alphabetical and numerical order at the same time.

I don’t understand what you are suggesting.

[Renel]

@RobertG, in the connection pool there is no selected or active pool. I simply use a LibreOffice Base file from which I created my personal database.

[Villeroy]

Collation rules are implemented in the database engine. Base is not a database. You can connect a Base document to all kinds of SQL servers, to dBase, text files, spreadsheets or even Writer documents having tables.

Base can create 3 types of databases from scratch:

1. A dBase directory when experimental features and Java are disabled
2. HSQLDB embedded in the Base document when experimental features are disabled.
3. Firebird embedded in the Base document when experimental features are enabled.

P.S. it looks as if HSQL ignores spaces

[Renel]

OK It’s HSQLBD Embedded.

[Villeroy]

Meanwhile I tried various collation rules with recent versions of HSQL according to the chapter on collation in the official documentation Chapter 3. Schemas and Database Objects
HSQL always ignores the spaces in your names.
It sorts like this:
BaeGeuRin
BaeHaeSun
BaekJinHee
BaeNuRi
BaeSusy
BeakJiWon

[Renel]

@Villeroy, now I get it. Isn’t it illogical that space is not considered as an inherent character of any line of text?
.
Should this be considered a malfunction or a choice made by the designers of LibreOffice?
.
Is there any solution to this problem other than replacing the space with a temporary caracter which could play the role of the space, perform sorting on those changed names and replace that temporary caracter again with the initial space?

[RobertG]

Seems to be a bug of the old internal HSQLDB. Internal Firebird will order it the right way …

[Renel]

@Villeroy,

In the database settings, regarding the set database collation statement: SET DATABASE COLLATION [ NO PAD | PAD SPACE ], do the parameters [NO PAD or PAD SPACE] have any role which would imply that existing spaces could be taken into account during a sort or it has nothing to do?
.
Is this really a bug as @RobergtG seems to imply?

[cpb]

it does seem to be a bug.
that’s well spotted by Villeroy, I think that this should resolve the problem without the need to RE-SET DATABASE COLLATION:

order by replace("NameActress1",' ','.')

EDIT:
maybe I need to clarify.
Form Properties > Data > Sort

paste this in the Sort control

replace("NameActress1",' ','.')

[Renel]

@cpb,

This generates the following error: Column not found: NameActress1 in statement [SELECT * FROM “TActrices” ORDER BY REPLACE( “NameActress1”, ’ ', ‘.’ )]

[Villeroy]

@Renel

REPLACE( “NameActress1”, ’ ', ‘.’ )

Excellent idea!
Open my names.odb with the embedded HSQL and modify the query like this:

SELECT "N", "ID" FROM "PERSONS" ORDER BY REPLACE( "N", ' ', '.' ) ASC

N	ID
Bae Geu Rin	0
Bae Hae Sun	1
Bae Nu Ri	4
Bae Susy	5
Baek Jin Hee	2
Beak Ji Won	3

which is the same sort order as with the Firebird database.

[cpb]

@Renel

sort value is “NameActress1” ASC

I only know what you tell and only you know the names of the fields you have.
the issue concerns the sort order of a text field which contains spaces.
here is a tiny database with 1 table “T” which contains 2 fields “ID” PK and “Name” varchar().
“Name” holds the values of:
‘Bae Geu Rin’
‘Bae Hae Sun’
‘Baek Jin Hee’
‘Bae Nu Ri’
‘Bae Susy’
‘Beak Ji Won’

table T is the data-source for the form.
sort order is:

replace("Name",' ','.')

the data is correctly sorted.
Sort.odb (12.1 KB)

[Renel]

Thank you very much @cpb and @Villeroy. Everything works perfectly.