IssueTracker

I am performing select with a filter over searchable fields and regular fields. The query fails in the scenarios with OR operand.

Index over properly tokenized fields:
CREATE SEARCH INDEX FIRST_NAME_SUBSTR ON USER(FirstName_Tokens_substr, LastName_Tokens_substr, MiddleName_Tokens_substr);

Query fails with OR operator with regular field (doesn't matter if it is a primary key or not):
select * from USER where (SEARCH_SUBSTRING(FirstName_Tokens_substr,"Adam") OR SEARCH_SUBSTRING(LastName_Tokens_substr, "Adam"))
OR USER_ID like "123"
Error message:
The statement failed: SEARCH_SUBSTRING is not supported in this query. Possible reasons are: There is no appropriate search index to use, or the function is used in unsupported query shapes.

However, the same query executed successfully in the case with AND operation over the same regular field:
select * from USER where (SEARCH_SUBSTRING(FirstName_Tokens_substr,"Adam") OR SEARCH_SUBSTRING(LastName_Tokens_substr, "Adam"))
AND USER_ID like "123"

The only way to make it work is to store USER_ID within the index, but it leads to performance degradation and it is not feasible to store all fields needed for filtration:

CREATE SEARCH INDEX FIRST_NAME_SUBSTR ON USER(FirstName_Tokens_substr, LastName_Tokens_substr, MiddleName_Tokens_substr) STORING (USER_ID)