12.0p/13.1d General HSQLDB sorting issue in showcase

[Blama]

Hi Isomorphic,

I checked out some samples in the 13.1d showcase again. Please see the UnionDataSource-sample (SNAPSHOT_v13.1d_2023-09-13).
At first it seems the data is not sorted correctly.

Please try the following:

• Start sample
• Scroll down and find some rows with "Agency Name" entries (the sample description also says that they exist)
• Restart sample
• Click "Agency Name" to sort ascending by agencyName
• No entries with "Agency Name" on top
• Click "Agency Name" again to sort descending by agencyName
• No entries with "Agency Name" on top

I assumed a framework-SQL-generation bug first, but now I think it's just HSQLDB behavior.
Add dataProperties: {fetchMode: "local"} to the sample and you will see that now on sorting descending the first rows have values.
I think your clientside sorting is asc -> nulls first and desc -> nulls last (is this true?), which comes more apparent if you also add useClientSorting:false to dataProperties.
Then it's always a server call and never values on top, as this is the HSQLDB default behavior.
But it seems HSQLDB also supports your clientside asc -> nulls first and desc -> nulls mode via sql.nulls_order: false. Perhaps this should be set in your showcase server.properties.
Postgres already defaults to your client side sort, I think. See here ("By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.").

Best regards
Blama

[Blama]

Actually, this is even weirder.

See also the fetchOperationFS-sample with added dataProperties: {fetchMode: "local"}.
Here the data shows in the "Units" column when sorting ascending.
In the sample from #1 with added dataProperties: {fetchMode: "local"}, the "Agency Name"-column shows data when sorting descending.

So it seems the client side is not behaving the same here in these cases. Something seems off to me.

Best regards
Blama