[V3] wrong sorting

[Dennis1993]

If I sort the room overview with the down arrow the sorting is wrong:

[scouillard]

Hi,
I can't reproduce an error.
The sorting seems to be working fine.

Besides, it seems like only the first room, the one that starts with "eis", is disordered?
Is the "e" a special character?

[drudgede]

In greenlight 3.0.4, I can reproduce the error as well.
About 98% of the rooms were migrated from Greenlight v2.
When I click on sort by "name" and the arrow's direction is up, greenlight sorts the rooms in the following order:

1. Rooms where the name starts with a number
2. followed by: Rooms ordered by A-Z whereby the room starts with an uppercase
3. followed by: Rooms with brackets, e.g. [Weekly] Team meeting
4. followed by: Rooms ordered by a-z whereby rooms start with a lowercase
5. followed by: Rooms starting with a § character
6. followed by: Rooms starting with German umlauts (Ä, Ö, Ü)

Strangely, before the first ordered set (rooms where the room starts with a number), I have a single room which starts with a normal uppercase V.

According to my company policy, I cannot attach any screenshots since there are personal names, but if it helps, I can send you them somehow privately. However, here are some very limited ones:

First rooms, the room with V and the numbers:

Followed by A to Z :

Followed by Room with brackets:

Followed by a to z:

Followed by § and umlauts:

[drudgede]

By the way, if I don't sort at all, I expect greenlight to sort rooms by date of the last session. This works in general, too, however, I sometime have situations like this:

Is there any difference in storing the date between GL v2 and GL v3?

[drudgede]

I found out that the single room on top of the list starting with a V has a prepended whitespace, so that solves this riddle and is not considered as a problem on my side.

For the other things: if I execute select name from rooms order by name in the Postgres database, I have the same order as in the screenshots.

I can mitigate this issue if I execute select name from rooms order by lower(name). Then it sorts by 0-9, [, A-Za-z case-insensitive, §, umlauts. I don't know why both special characters are treated differently, but at least I can live with this result way more.

By the way, I found an interesting quote:

It is worth noting that for data comparisons, PostgreSQL is case sensitive and there is no option to change this behaviour. You will have to force data to upper or lower on both sides of text comparisons if case is not important to the operation and there is a chance of it being different. This conversion might be a good time to force data used in joins and comparisons to all upper or lower case.

Edit: I found out why Postgres threats both special characters differently.
If you execute select name, ascii(name) from rooms order by name asc you can see that the sort order is dependent on the ASCII-value of the first char of the room name. In my case, here are example mappings:

char => ascii_value
-------------------
whitespace => 32
0 => 48
8 => 57
A => 65
Z => 90
[ => 91
a => 97
e => 101
§ => 167
Ö => 214
Ü => 220

This explains the strange sort order, also for both special characters. I would recommend to at least implement the lower() function in the order by clause to get some consistent results. For the special characters, I think it's not worth the effort to create a workaround to have them at the end or in the beginning of the result set since you would have to cover all possible special characters. At least, I can live with that.

I'll keep on searching why the dates are sorted in a wrong order as apparent in my second answer.

[drudgede]

I also found out why the dates are not ordered as given in the screenshot above. The sorting generally works, however, there is a single "point" in the list of rooms where the rooms get out of the sort order.

The rooms are generated in this piece of code, I guess:

greenlight/app/controllers/api/v1/admin/server_rooms_controller.rb

Lines 34 to 35 in d25819b

	rooms = Room.includes(:user).joins(:user).where(users: { provider: current_provider }).order(sort_config, online: :desc)
	.order('last_session DESC NULLS LAST')&.admin_search(params[:search])

So I ran the query manually in postgres:

select name, online, last_session from rooms order by online desc, last_session desc nulls last;

The part of the result set which corresponds with the screenshot above is this one:

                                    name                                    | online |      last_session
----------------------------------------------------------------------------+--------+-------------------------
 Besprechung                                                                | t      | 2023-06-27 08:55:58.006
 Startraum                                                                  | t      | 2023-06-26 11:02:53.831
 F***********'s Room                                                        | t      | 2023-06-26 09:14:19.6
 J**********'s Room                                                         | f      | 2023-07-06 12:35:08.117
 N**********r                                                               | f      | 2023-07-06 11:42:15.739
 d***********                                                               | f      | 2023-07-06 08:57:06.413
 Startraum                                                                  | f      | 2023-07-06 06:29:47.013
 ****intern                                                                 | f      | 2023-07-05 06:58:05.089

The relevant part is the online column. All rows above the snippet have the value t for online, all values below have f for online. This is also the "point" where the rooms in greenlight do not look anymore like sorted by the last conference date. Since it sorts by online first and secondly by last_session, thats the reason why this sort order looks so scrambled in the list of rooms in greenlight.

I am not sure what exactly the semantic of the online column is. I guess it should indicate whether a room is running or not. However, no rooms are currently running on my instance. If there is a room running in BigBlueButton, this is shown correctly in the greenlight admin backend in the list of rooms.

It is possible that this value is set to t if a room a running but not set back to f if the session is over? I guess the running state is determined in the greenlight backend by the BBB API and not based on the database state, isn't it? Or does the colums have a different semantic?

[scouillard]

Good investigation 👍
It is indeed possible that a room is not set back to f when the session is over.
When a session is over, the BBB server will send a callback to GL at external_controller#meeting_ended
If for whatever reason that call misses, the session will still be registered as online.
A solution would be to create a rake task & background job that asks the BBB server if a meeting is still running every X minutes to double check and re-write the status of a meeting if necessary.

[scouillard]

We will be starting to work on a Rake task to sync the meeting status in the upcoming days.

[drudgede]

@scouillard I found a way to create a way more consistent result for the user.
If you execute select lc_collate; in Postgres, you'll notice that the default collation us en_US.utf8
However, if you use a collation which support International Components for Unicode, the result looks like a charm.

The docker container has all required components, so no additional software has to be installed.

If you change the query to:

 select name from rooms order by name collate "en-x-icu" asc;

the result set now looks like the following:

1. Rooms with special characters such as [ or §
2. Followed by rooms starting with 0-9
3. Followed by rooms starting with a-z and A-Z case-independently as expected, and which also include language specific letters such as Ö in the right position. E.g. Ä is directly before A, Ö is directly before O and Ü is directly before U where Germans would expect this (rather more than in the ordering in the other comments after Z and some special characters).

For me, this is a perfect and consistent result set, way more than my previously proposed solution to just implement the lower() function in the order by clause - in fact, this is not even needed anymore. So I propose to either specify the collation in all order by clauses or to simply change the collation for the whole database. Since the collation only influences the order sort and not the data itself, I'd tend to the latter.