This is driving me nuts. Is this a bug, or am I missing something? I think the page size input to a Load Table Object Block returns that many rows of the query and should not impact the sort order. But in my testing that isn’t turning out to be the case -
I built a test instance. The same where clause is run twice. Once with a page size input of 15, once with a page size input of 20, and the two results are matched up in a loop -
EDIT for clarity - As you can see in the results, the objects are not being returned in the same order from the two queries. This doesn’t seem right to me.
Please try the same in REST Console (on the Data screen) and see if the results are different.
Same results in the console. Results for page size 15 -
Results for page size 20 -
I do not know the exact reason, my hypothesis is this:
The where clause query references another table and the list of properties pulls columns from another table, when you expand the page size, it expands the scope of the objects from both tables and subsequently, the final page contents are somewhat different. I assume this is how inner joins work underneath. As a validation point, could you try excluding the
Organization[Fundraisers].... properties from the query and see if the results are more consistent?
Without the additional table, the results are consistent.
There is something about joining in the other table that is causing the issue, but the data in the second table is 1-1, so the size of the result shouldn’t change.
Are the results in both queries still ordered by
I found the specific column in the second table, that when added, causes the issue. It is - Organizations[Fundraisers].ProfileImage as OrgProfileImage - The data is NULL for all rows in this query.
Does excluding the property solve the issue then?
Organizations[Fundraisers].ProfileImage is a file reference -
Not really. I need that data if it exists.
No problem. We will investigate this further. Please provide the following information:
- App ID
- Where clause (as text, no a screenshot)
- List of columns you’re requesting in the query
Users[SponsorFundraisers].objectId='09BA80CB-AF9F-469E-86DF-DAB221B1820D' AND Season.objectId = '2F991BCC-688F-419A-B011-9326DD0D2FA3' AND Status NOT IN ('Hold', 'Cancelled')
,Organizations[Fundraisers].objectId as OrgObjectId
,Organizations[Fundraisers].MicrostoreId as OrgMicrostoreId
,Organizations[Fundraisers].MicrostorePath as OrgMicrostorePath
,Organizations[Fundraisers].ProfileImage as OrgProfileImage
the reason you see the behavior is that you have identical value for
StartDate. In this case, it is up to mysql(we use it on the back side) in what order to return data, and it may vary depending on page size(mysql limit) selected properties etc. To fix the issue you can store the date with milliseconds. Also, you can add one more field for sorting, for example, you can add
objectId in this case the result will be always sorted by StartDate and then by objectId
Wow, that is really interesting. I would have expected the results to be the same every time, and the page count takes the requested number of rows from those results.
I appreciate you and @mark-piller looking into this for me. I can’t say enough good things about the support that comes along with Backendless!