Load Table Object block - Page size effecting sort order?

General
1

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 -

image
image969×908 63.9 KB

image
image1027×818 64.6 KB

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.

[
	{
		"list15ObjectId": "DDA67824-F8C1-44B1-804A-2E915B44E317",
		"list20ObjectId": "DDA67824-F8C1-44B1-804A-2E915B44E317"
	},
	{
		"list15ObjectId": "E56E7DE2-0C19-4F6D-B139-2ECEC15754E0",
		"list20ObjectId": "E56E7DE2-0C19-4F6D-B139-2ECEC15754E0"
	},
	{
		"list15ObjectId": "11832FAB-1F66-4FBD-B76E-DA6DC0D72E27",
		"list20ObjectId": "ACA755BF-60DE-433C-A481-899337149CD0"
	},
	{
		"list15ObjectId": "F536BF2B-DC72-4A27-864E-2D140235D3CA",
		"list20ObjectId": "F536BF2B-DC72-4A27-864E-2D140235D3CA"
	},
	{
		"list15ObjectId": "49725D33-3D14-45FC-92E6-E4E243CB7A8D",
		"list20ObjectId": "E40D30DF-20FA-4FD0-A637-CFE049155668"
	},
	{
		"list15ObjectId": "6F3DF907-AD20-4138-97E4-19BDEB38B512",
		"list20ObjectId": "E077307E-8D23-4A29-9F7D-1244BD8D4189"
	},
	{
		"list15ObjectId": "850F2791-68AB-46FE-9859-95C3C284B93D",
		"list20ObjectId": "D78E06BF-2605-48F6-B2DF-4EEDD4C17180"
	},
	{
		"list15ObjectId": "8D0B0606-2E38-434B-BAA9-D4DC16BA64CF",
		"list20ObjectId": "B67B6B13-0C2D-4649-A986-969B061670C4"
	},
	{
		"list15ObjectId": "903EC5B0-DF81-49E6-878D-A4BF9AEDE692",
		"list20ObjectId": "B02A6E11-400C-431B-A713-CD3D96B3AB2E"
	},
	{
		"list15ObjectId": "9FD03F3D-A7DF-41C7-AFC3-C4EC99DBDF7E",
		"list20ObjectId": "11832FAB-1F66-4FBD-B76E-DA6DC0D72E27"
	},
	{
		"list15ObjectId": "AC817853-1E3E-46D2-B4EE-877FAB9F4D28",
		"list20ObjectId": "AC817853-1E3E-46D2-B4EE-877FAB9F4D28"
	},
	{
		"list15ObjectId": "ACA755BF-60DE-433C-A481-899337149CD0",
		"list20ObjectId": "9FD03F3D-A7DF-41C7-AFC3-C4EC99DBDF7E"
	},
	{
		"list15ObjectId": "B02A6E11-400C-431B-A713-CD3D96B3AB2E",
		"list20ObjectId": "903EC5B0-DF81-49E6-878D-A4BF9AEDE692"
	},
	{
		"list15ObjectId": "B67B6B13-0C2D-4649-A986-969B061670C4",
		"list20ObjectId": "8D0B0606-2E38-434B-BAA9-D4DC16BA64CF"
	},
	{
		"list15ObjectId": "D78E06BF-2605-48F6-B2DF-4EEDD4C17180",
		"list20ObjectId": "850F2791-68AB-46FE-9859-95C3C284B93D"
	}
]

Thoughts?

2

Please try the same in REST Console (on the Data screen) and see if the results are different.

3

@mark-piller

Same results in the console. Results for page size 15 -

image
image1177×942 42.2 KB

Results for page size 20 -

image
image1154×996 44.6 KB

4

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?

5

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.

6

Are the results in both queries still ordered by StartDate ?

7

@mark-piller

Yes.

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.

8

Does excluding the property solve the issue then?

9

Organizations[Fundraisers].ProfileImage is a file reference -

image
image1069×39 1.8 KB

Tim

10

Not really. I need that data if it exists.

Tim

11

No problem. We will investigate this further. Please provide the following information:

  1. App ID
  2. Where clause (as text, no a screenshot)
  3. List of columns you’re requesting in the query

Thanks,
Mark

12

Thanks @mark-piller!

  1. EEE25B20-17FA-97DD-FF29-EC45A5072A00

Users[SponsorFundraisers].objectId='09BA80CB-AF9F-469E-86DF-DAB221B1820D' AND Season.objectId = '2F991BCC-688F-419A-B011-9326DD0D2FA3' AND Status NOT IN ('Hold', 'Cancelled')

AppealMessage
,StartDate
,Formatted2StartDate
,EndDate
,Formatted2EndDate
,AutoReminderEmails
,AutoReminderTexts
,OfficeOrganizationId
,MemberCount
,Organizations[Fundraisers].objectId as OrgObjectId
,Organizations[Fundraisers].MicrostoreId as OrgMicrostoreId
,Organizations[Fundraisers].MicrostorePath as OrgMicrostorePath
,Organizations[Fundraisers].ProfileImage as OrgProfileImage

13

hello @Tim_Jones

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

14

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!

Thanks again,
Tim