whereclause for ownerId doesn't work in certain case

General
1

The SQL where clause with ownerId = ‘AB…’ works as expected on a custom created Table. The case using != doesn’t work for me on ownerId columns only so far.

This is what I am finding:

ownerId = ‘A1BBDA41-2D4C-AE8B-FF70-2D985E1EDD00’ <----- Works

ownerId != ‘A1BBDA41-2D4C-AE8B-FF70-2D985E1EDD00’ <---- Doesn’t work

objectId !=‘A1BBDA41-2D4C-AE8B-FF70-2D985E1EDD00’ <----- Works

Note: I am also using the Backendless REST Console to quickly test this.

Also, the ownerId column contains 15 rows with data values and the remaining rows for ownerId are empty. Total rows in the Table is 53.

2

Hi Roy,

When you say it does not work, what is the result you’re getting? Do the records with that ownerId show up? Or nothing shows up? Any chance you could attach screenshots showing both a working and not-working queries? (I assume you’re testing with the “SQL Search” feature in console)

Regards,
Mark

3

I am getting valid JSON with NULL values. I am using the SQL Search in the REST Console using a GET request.

WhereClause_search.jpg
WhereClause_search.jpg1476×481 77 KB

4

Could you try the same thing in the Data Browser tab? Make sure to set the “SQL Search” toggle to on.

5

Hi, Roy,

Try to omit whitespaces in whereClause. Like the following:

ownerId='A1BBDA41-2D4C-AE8B-FF70-2D985E1EDD00
ownerId!='A1BBDA41-2D4C-AE8B-FF70-2D985E1EDD00
objectId!='A1BBDA41-2D4C-AE8B-FF70-2D985E1EDD00
6

I tried the exact search but in the DataBrowser. The attachment picture show without URL encoding but I also applied URL encoding and received the same empty list.

WhereClause_search_From_DataBrowser.jpg
WhereClause_search_From_DataBrowser.jpg1787×481 77.7 KB

7

I cannot reproduce this problem in my app. Any chance you could export your data and send me the ZIP file? The instructions for data export are here:

https://backendless.com/feature-70-export-application-data-settings-data-tables-geo-points/

Thanks,
Mark

8

I tried with no spaces and I’m getting the same results

9

Roy,

I played some more with it and determined that the records with no data in the ownerId field are not discovered by the “not equal” query. If you try modifying the ownerId value in any of the records in the MList table and then re-run the query, the record will be found.

This is not correct behavior, an empty value should match the not equal condition and I am opening an internal ticket to get this fixed.

Regards,
Mark

10

Any update on this issue?

Kind regards

11

@Nik_van_der_Wel Yes, this had been fixed a while ago.

12

Thanks, Mark, for a quick reply, great!

I’m asking since I couldn’t find a status of this ticket and since I’m having an issue with the where API as well, which I couldn’t find on the forum yet.

Please let me know if I need to create a new topic.

It concern the standard API for where-clause. The ‘contains’ request doesn’t reply as I would expact. The array is empty, but other where clauses being “ending with” do work for the same objectID. Hope this make sense and you can explain what I am doing wrong or confirm this is an issue.

Thanks in advance. Case URL’s below.

BASE URL (open access):
http://api.backendless.com/C52F919B-42FB-F700-FFC9-109FEB3EBC00/1FEBD4ED-AC34-415E-ABEE-6A66718AC9BC/data/users

EXTENTION:
—works—
?where=objectid=‘A98F4A6B-A431-401E-BF3C-B7DBAE085989’

URL: LINK

—works—
?where=objectid LIKE ‘A98F4A6B-A431-401E-BF3C-B7DBAE08598%’

URL: LINK

— doesn’t work since empty—
?where=objectid LIKE ‘%98F4A6B-A431-401E-BF3C-B7DBAE08598%’

URL: LINK

13

To get it fixed, open Backendless Console, switch to the Data screen and select the REST Console tab.

Enter your where clause into the “Where Clause” field:

objectid LIKE '%98F4A6B-A431-401E-BF3C-B7DBAE08598%'

and click GET. If you get the data back, the where clause is working. Then you can see how the entire URL is structured right in the “Request URL” field.

Regards,
Mark

14

Hi Mark,
Sorry for my late reply.

It appears to be an issue in the tool I use for my API’s (Insomnia).

Thank you!