Sorting on a specific column causing incorrect result

Sorting on a specific column causing incorrect result

  

This is weird issue others can try themselves.

1. We have a search screen which gets data from an entity. 

2. It has 2 such columns where all values are same for (34) records. 

3. My page size is 20 . 

4. When I apply the sorting on column with different values , then it works fine. 

5. when I apply the sorting on the column which has all values same , Not sure if that works fine.

6. I click on page 2 to see remaining 14 records . It shows the 14 records but result is not expected one. 

7. When I change my page size to 40 and then try the same search criteria then it shows me expected 34 records. 


Weird issue. If somebody has that kind of data then try it and let me know if you observe the same issue. 

It could be bug in that case . let me know if somebody has any pointer to solve this problem. 

Problem is , we can not debug it beyond a limit in outsystems . As paging + sorting handled by outsystems

 

Hi Shailendra,

This is not a bug. The order of records returned from the database is undefined for records with the same values in all columns sorted on. This is not an OutSystems bug, this is just how databases work. Just make sure that your sort always provides an order for all records, e.g. by also sorting on the Id, and it works fine.

Solution

1. We do not have any "ID" in our table/view. plus  , it is dynamic sorting which user can apply by clicking on column header. 

2. It does seem undefined as records on first page are always in the same order when we sort based on that those particular columns .  

3. we have existing systems and there it seems working fine. 

DB engine will not change its optimization approach between search and going next page. 

You have a point but that does not seem that valid . 

Solution

Hi Shailendra,

"Undefined" doesn't mean "random". And yes, the DB engine may return a different set of records between selecting only 20 records (needed for the first page) or 40 records (needed for the second page), in case the sorting doesn't create a unique ordering, whether you believe it or not. Existing software may use different sorting, unless you compare the exact commands sent to the DB you can't tell.

As for not having an Id, what kind of unique identifier does the table have? None at all?

Kilian Hekhuis wrote:

Hi Shailendra,

"Undefined" doesn't mean "random". And yes, the DB engine may return a different set of records between selecting only 20 records (needed for the first page) or 40 records (needed for the second page), in case the sorting doesn't create a unique ordering, whether you believe it or not. Existing software may use different sorting, unless you compare the exact commands sent to the DB you can't tell.

As for not having an Id, what kind of unique identifier does the table have? None at all?

Thanks sir for you reply. We found the fix but did not find the reason but that is ok. 


What was the fix?

one of the used component was playing the trick