All my users are experiencing issues,
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))
Invalid where clause at position 34: no viable alternative at input ‘emailNOT’
something similar happens when trying to query from the web console:
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 NOT LIKE syntax works now.
The problem is solved now.
thank you for your support
This is happening again!!
no user can log in.
Please solve this permenantly. it really hearts our app and reviews
The NOT LIKE syntax still works. What error do you receive and under what circumstances?
please provide us example of query that fails. Your previous query works without errors.
This is the query that fails.
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);
Could you please provide the same query as a string literal (exactly how you did it in the original post). Please also include your APP ID.
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:
email = ‘null’
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.
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.
Yes this was the problem. there was mistakenly a + sign in the query.
Oddly enough it was working anyway for the last few months, a lucky coincidence as you say.
but it’s great to solve the problem and have everything working again.
thank you for your support,
Closing this issue as “Solved”. Please let us know if the problem occurs again.
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?
If “to_usser” is a relation column, the proper syntax must be “to_user.objectId is not null”
Yes, this query was working just fine. We`ve noticed such problem along with this: http://support.backendless.com/t/search-result-from-data-table-wrong-order
But as soon the sorting order was fixed, this problem also disappeared.
Yes, please, export data if it helps to solve this problem.