this query that always succeeded now fails;
((email LIKE ‘null’) AND + (email NOT LIKE ‘’) AND (email IS NOT NULL)) OR ((ip LIKE ‘’) AND + (ip NOT LIKE ‘’) AND (ip IS NOT NULL)) OR ((userAdvertisingId LIKE ‘c51bf3e4-1f6f-4af7-995f-94fb3b8d7aae’) AND + (userAdvertisingId NOT LIKE ‘’) AND (userAdvertisingId IS NOT NULL))
the message:
Invalid where clause at position 34: no viable alternative at input ‘emailNOT’
Thanks for reporting! We’re already aware of that and are in the process of rollback of the latest changes. We shall notify you as soon as possible.
Please accept our apologies for the inconvenience.
The failure is not due to a change in the query, but the problem started months after this query is running well.
String blacklistWhereClause = “((” + Consts.BLACKLIST_COL_EMAIL + " LIKE ‘" + email + "’) AND +" +
" (" + Consts.BLACKLIST_COL_EMAIL + " NOT LIKE ‘’) AND " +
" (" + Consts.BLACKLIST_COL_EMAIL + " IS NOT NULL)) OR " +
"((" + Consts.BLACKLIST_COL_IP + " LIKE '" + ipAddress + "') AND +" +
" (" + Consts.BLACKLIST_COL_IP + " NOT LIKE '') AND " +
" (" + Consts.BLACKLIST_COL_IP + " IS NOT NULL)) OR " +
"((" + Consts.BLACKLIST_COL_USER_ADVERTISING_ID + " LIKE '" + userAdvertisingId + "') AND +" +
" (" + Consts.BLACKLIST_COL_USER_ADVERTISING_ID + " NOT LIKE '') AND " +
" (" + Consts.BLACKLIST_COL_USER_ADVERTISING_ID + " IS NOT NULL))";
QueryOptions blacklistQueryOptions = new QueryOptions();
BackendlessDataQuery blacklistDataQuery = new BackendlessDataQuery(blacklistWhereClause);
blacklistDataQuery.setQueryOptions(blacklistQueryOptions);
Backendless.Data.of(Consts.TABLE_NAME_BLACKLIST).find(blacklistDataQuery, callback);
this is our APP ID: AF8A6FD1-7119-6D55-FF83-BC9AA0072B00.
and this is the query from debug mode that is going to find() method:
((email LIKE ‘null’) AND + (email NOT LIKE ‘’) AND (email IS NOT NULL)) OR ((ip LIKE ‘’) AND + (ip NOT LIKE ‘’) AND (ip IS NOT NULL)) OR ((userAdvertisingId LIKE ‘removed_this_for_confidentiality’) AND + (userAdvertisingId NOT LIKE ‘’) AND (userAdvertisingId IS NOT NULL))
This is not a valid query. If you take out all the “+” signs (which do not really make sense), then it works:
((email LIKE ‘null’) AND (email NOT LIKE ‘’) AND (email IS NOT NULL)) OR ((ip LIKE ‘’) AND (ip NOT LIKE ‘’) AND (ip IS NOT NULL)) OR ((userAdvertisingId LIKE ‘removed_this_for_confidentiality’) AND (userAdvertisingId NOT LIKE ‘’) AND (userAdvertisingId IS NOT NULL))
Also, any time you use LIKE, the purpose of it is to perform pattern matching. For example, the following finds all @gmail.com emails:
email LIKE ‘%@gmail.com’
When you do something like this:
email LIKE ‘null’
You would be MUCH better off changing it to the following syntax instead:
I’m really interested in where you found that syntax to use. It definitely has never been described in our documentation and it’s a lucky (or not) coincidence for you that it even worked earlier.
Hi.
And what about “is not null” statement? Is it allowed to be used, or should be changed to something like “object!=null”? Because it worked perfectly till yesterday evening, but now we are suffering from it - data tables with such statement in the query return not relevant results.
The “is not null” queries also work fine. Possibly, you’re running into a case with Strings, where empty String won’t be counter as null string.
Please describe your case more thoroughly so that we could help you better.
Hi Sergey.
Thank you for reply. I`ve just tested via the REST tab in the backendless admin console.
The problem query is for the Invites Data Table
event_object is not null AND to_user is not null AND from_user is not null AND to_user.social_user_id = 4044463 AND accepted = false AND viewed = false
And respond to this query is wrong and contains objects where “event_object” is null.
App id is CFE8A5F9-172A-0D0B-FF08-615FF8B99100
I remember there had been some issues with relation fields and “is null” queries. Did this same query work as expected earlier?
Also, don’t you mind if I export the data from your app to perform some tests?
