Distance query stops working in my app

Bug
1

Hello Team,

Distance query is not working properly I am facing this issue since yesterday, earlier it was working.
it gives me

      {"code": 1316, "errorData": {}, "message": "Expression for generated column \"f2\" is not valid."}, "code": 1316, "headers": {"access-control-allow-methods": "POST, GET, OPTIONS, PUT, DELETE, PATCH", "access-control-allow-origin": "*", "connection": "close", "content-length": "93", "content-type": "application/json", "date": "Wed, 16 Feb 2022 06:18:22 GMT"}, "message": "Expression for generated column \"f2\" is not valid.", "status": 400}
2

Hi @Ayaz_Khan ,

Could you please provide your App ID and name of the table for which you receive such error?

Regards, Andriy

3

Hi @Andriy_Konoz,

My AppID : 820FBB35-552C-F405-FFB9-B4D60982E100
Table Name : CharitiesFinal
sometime it works: distance( ‘36.00’, ‘-84.32’, latitude, longitude) < mi(100)
but did not work with other conditions like : distance( ‘36.00’, ‘-84.32’, latitude, longitude) < mi(100) AND Featured = true

4

@Ayaz_Khan ,

I do not see the “f2” column in CharitiesFinal table. Have you removed it?

To summarize. You created generated column “f2” with expression “distance( ‘36.00’, ‘-84.32’, latitude, longitude) < mi(100)” and it worked fine but when you extended it you received error. Am I correct?

Regards, Andriy

5

I did not create “f2” column. and did not make any changes in database schema of this table

6

In this case could you recreate query in Data service REST Console, export it as cURL, remove API Key from it and post it here?

7

Please check

   curl -X GET 'https://api.backendless.com/820FBB35-552C-F405-FFB9-B4D60982E100/<API_KEY>/data/CharitiesFinal?pageSize=100&where=distance(%20%2736.00%27%2C%20%27-84.32%27%2C%20latitude%2C%20longitude)%20%3C%20mi(100)%20%20AND%20Featured%20%3D%20true'
8

@Ayaz_Khan ,

I think that I found reason of your problem. In whereClause you pass nullable columns as params to distance function. This cause an exception on the database level. This exception is not correctly parsed by Backendless and you receive strange error about invalid generated column expression.

I have created an internal ticket BKNDLSS-27809 to return correct error message for this case.

Regards, Andriy

9

Hi @Andriy_Konoz,

But earlier it was working fine then suddenly this error occur?
Some rows have empty ‘latitude’ and ‘longitude’ values
Do I need to add lat long in those rows ??

10

@Ayaz_Khan ,

But earlier it was working fine then suddenly this error occur?

Maybe you remember if at that moment all rows had filled "latitude’ and ‘longitude’ columns?

Regards, Andriy

11

No they are empty from starting.

12

In this case it looks like regression in our logic.
We will investigate this issue in scope of BKNDLSS-27809.

Right now I can only advice you to add some default value (0 for example) for these columns to make your query behave appropriately.

Regards, Andriy