Table case sensitive sorting with paging --> "wrong" results

Support
#1

Hi,

i’ve experienced the following bug (i assume it is one):

Given: i have a entity (customer with attribute name) with a browse screen and a table with 28 entries (27 for ‘Customer A’…‘Customer Z’ + 1 for ‘Customer a’).

When i sort by name asc and set the page size to 20 and i press “search”

Then i get the first page with ‘Customer A’ --> ‘Customer T’ and the second page with ‘Customer a’, ‘Customer U’…‘Customer Z’

Expected behavior: i to get the first page with ‘Customer A’, ‘Customer a’, --> ‘Customer S’ and the second page with ‘Customer T’…‘Customer Z’

Attached you’ll find the corresponding example application.

I tried this with HSQLDB as well as Oracle. The resulting SQL statement (for HSQLDB) looks like this:


SELECT LIMIT 0 20 ID AS a1, DELETE_TS AS a2, DELETED_BY AS a3, NAME AS a4, VERSION AS a5 FROM CPTSSL_CUSTOMER WHERE (DELETE_TS IS NULL) ORDER BY NAME;
fab798cb-61d3-384d-976d-553f658fd161			Customer A	1
23b5fbd8-49a6-d39c-d20a-d0df6abf8566			Customer B	1
4ee103d7-79e7-f0e7-4a62-94e04fad1818			Customer C	1
f7d66a6e-94c9-ef15-2ad6-4fc3ff7d4d9f			Customer D	1
35a02fd1-18ef-d919-7e61-fbfa6d2bd865			Customer E	1
484b8b4d-fa51-832f-b442-323970d77635			Customer F	1
2f31f64d-cd5e-2f5e-a91b-1ac91b817f9a			Customer G	1
89f35cda-7377-26e2-014d-e02e471e99f4			Customer H	1
94310bb6-4e41-0e56-c838-49ad8206bbd3			Customer I	1
d681ad35-2c52-44c5-224b-56a4002ecf8c			Customer J	1
31f1f3c2-2830-3e67-b40b-660f901aa2e3			Customer K	1
97f28b7d-a3f3-ccb9-c414-3edb356b8a7e			Customer L	1
2332bf8e-0414-ceb8-16f4-994988e3989f			Customer M	1
22f0a276-4f85-216c-9a21-fc18c4c16cc2			Customer N	1
862df6d2-f54b-a097-164b-eef4598b5be0			Customer P	1
9534da6e-1509-5018-dfac-921fca23f199			Customer Q	1
37d3464e-0d2c-14aa-428b-d01fbe183d2d			Customer R	1
1e270c30-42e6-deff-db5b-fe516d954a6d			Customer S	1
313089e1-c1a5-19b1-28f7-7fba03ca831a			Customer T	1
1abf197d-adac-09ae-452b-f4067f7bb068			Customer U	1

SELECT LIMIT 20 40 ID AS a1, DELETE_TS AS a2, DELETED_BY AS a3, NAME AS a4, VERSION AS a5 FROM CPTSSL_CUSTOMER WHERE (DELETE_TS IS NULL) ORDER BY NAME
0a4bf5d0-720b-ac6f-b7b5-e06de812639b			Customer V	1
33d78e6b-9915-b9cb-b32e-f44978cf63b3			Customer W	1
d6db75c0-58d1-12f5-6a7f-d01f9cb02b72			Customer X	1
7040d63c-c3da-ba32-1f1f-1d24db9429f9			Customer Y	1
095d0ce2-7bba-94cf-4b84-1d5cb2f139bc			Customer Z	1
e113f3ee-5bb0-162a-94f6-246f36878173			Customer a	1

This objection leads to the conclusion that it is not a bug in the platform, because the DB seems to sort by ASCII character (we can see it when the LIMIT statement is removed:


SELECT ID AS a1, DELETE_TS AS a2, DELETED_BY AS a3, NAME AS a4, VERSION AS a5 FROM CPTSSL_CUSTOMER WHERE (DELETE_TS IS NULL) ORDER BY NAME
fab798cb-61d3-384d-976d-553f658fd161			Customer A	1
23b5fbd8-49a6-d39c-d20a-d0df6abf8566			Customer B	1
4ee103d7-79e7-f0e7-4a62-94e04fad1818			Customer C	1
f7d66a6e-94c9-ef15-2ad6-4fc3ff7d4d9f			Customer D	1
35a02fd1-18ef-d919-7e61-fbfa6d2bd865			Customer E	1
484b8b4d-fa51-832f-b442-323970d77635			Customer F	1
2f31f64d-cd5e-2f5e-a91b-1ac91b817f9a			Customer G	1
89f35cda-7377-26e2-014d-e02e471e99f4			Customer H	1
94310bb6-4e41-0e56-c838-49ad8206bbd3			Customer I	1
d681ad35-2c52-44c5-224b-56a4002ecf8c			Customer J	1
31f1f3c2-2830-3e67-b40b-660f901aa2e3			Customer K	1
97f28b7d-a3f3-ccb9-c414-3edb356b8a7e			Customer L	1
2332bf8e-0414-ceb8-16f4-994988e3989f			Customer M	1
22f0a276-4f85-216c-9a21-fc18c4c16cc2			Customer N	1
862df6d2-f54b-a097-164b-eef4598b5be0			Customer P	1
9534da6e-1509-5018-dfac-921fca23f199			Customer Q	1
37d3464e-0d2c-14aa-428b-d01fbe183d2d			Customer R	1
1e270c30-42e6-deff-db5b-fe516d954a6d			Customer S	1
313089e1-c1a5-19b1-28f7-7fba03ca831a			Customer T	1
1abf197d-adac-09ae-452b-f4067f7bb068			Customer U	1
0a4bf5d0-720b-ac6f-b7b5-e06de812639b			Customer V	1
33d78e6b-9915-b9cb-b32e-f44978cf63b3			Customer W	1
d6db75c0-58d1-12f5-6a7f-d01f9cb02b72			Customer X	1
7040d63c-c3da-ba32-1f1f-1d24db9429f9			Customer Y	1
095d0ce2-7bba-94cf-4b84-1d5cb2f139bc			Customer Z	1
e113f3ee-5bb0-162a-94f6-246f36878173			Customer a	1

But as the platform seems to sort the second page case once again (in memory?) the ‘Customer a’ is on the top, not on the bottom (as the DBMS suggests).

So here’s the question: How can i change this behavior (probably on the DBMS side)? Why is HSQLDB as well as Oracle not by default configured in a way to not sort by ASCII character but instead by alphanumeric order?

Bye,
Mario

cuba-problem-table-sort-small-letters.zip (43.2K)

#2

Hi there,

does anyone had the time to look at the problem? Or is this just totally unrelated to CUBA?

Bye
Mario

#3

Hi Mario,

You are right:

  1. Query results are sorted with the default collation (which is case-sensitive in HSQLDB and Oracle DB).
  2. The results are also sorted in memory when displaying a page.
    So that is why “Customer a” is on the top of the second page.

The issue is reproduced on HSQLDB.
But with the Oracle DB an interesting thing occurs:

  1. When I open the Customer browse-screen in the application it is sorted OK (it means the collation is case-insensitive).
  2. But when the query(which I took from logs) is executed on the database using DB-tool, the results are ordered case-sensitively.

SELECT * FROM (SELECT a.*, ROWNUM rnum  FROM (SELECT ID AS a1, DELETE_TS AS a2, DELETED_BY AS a3, NAME AS a4, VERSION AS a5 
FROM CPTSSL_CUSTOMER WHERE (DELETE_TS IS NULL) ORDER BY NAME) a WHERE ROWNUM <= 20) WHERE rnum > 0

So could you please, give some more details of your environment? Which versions of the DB server and OJDBC driver do you use?

#4

Hi Rostislav,

thanks for looking at the problem.

The oracle installation has the following properties:

  • Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  • PL/SQL Release 11.2.0.2.0 - Production
  • “CORE 11.2.0.2.0 Production”
  • TNS for Linux: Version 11.2.0.2.0 - Production
  • NLSRTL Version 11.2.0.2.0 - Production

Driver:

  • (ojdbc6.jar) Oracle JDBC Drivers 11.2.0.4

Did you have any glue how to change HSQLDB to sort without having the above mentioned problem?

Bye
Mario

#5

Hi Mario,

This is certainly a problem related to CUBA: we sort case-insensitively in memory, while a database can sort differently. The issue is on our backlog and will deal with it ASAP.

#6

:ticket: See the following issue in our bug tracker:

https://youtrack.cuba-platform.com/issue/PL-8391