ERROR: The same comparison operation (LIKE or =) must be specified for all OR conditions

Question

I'm getting a malformed query exception: "The same comparison operation (LIKE or =) must be specified for all OR conditions".

The select statement contains something like this:

SELECT Id FROM <MedataTypeObject> WHERE Field_1__c = 'val' AND (Field_2__c = null OR Field_2__c like '%Example%') ORDER BY Sortable_Field__c

This occurs when trying to execute a query built out in a string (via Database.query(queryString)).

This has worked before. Maybe it has something to do with Winter '21 release.

I've tried "like ''" and "like null", neither work.

What are alternatives to make a query like this work (ideally in the query string itself, i.e. not filtering out objects after executing the query)? Specifically, checking for null in an OR condition that includes a "like" keyword.

Thanks!

Answer 1

Please see the known issue: Soql on metadata type records does not work properly when using LIKE in OR relationship

Summary
Soql on metadata type records does not work properly when using Like used with OR sometimes. Below query doesn't work fine. There are records with Name__c value "Hello, World" and "HelloWorld" but the below query doesn't give records.
Select DeveloperName, Label, name__c from Test__mdt WHERE name__c LIKE '%Hello%' OR name__c LIKE '%World%'

---

This appears to be something introduced in Winter `21 and is specific to SOQL queries on custom metadata types.

I ran the following SOQL query in my Summer '20 org without issue (note the omission of the OR namespace__field1__c = null condition):

select Id
from namespace__SomeCustomMetadataType__mdt
where
    (namespace__field1__c like '%Month%') AND 
    namespace__field2__c != null

And that returned the expected record.

I then modified it to include your additional "or not null" condition in the where clause. Again against a Summer '20 org:

select Id
from namespace__SomeCustomMetadataType__mdt
where
    (namespace__field1__c like '%Month%' OR namespace__field1__c = null) AND 
    namespace__field2__c != null

And the returned... nothing!? Which seemed odd, as it should have found the record from the prior query plus any additional matches on field1__c being null.

Trying that exactly same query against a sandbox on Winter '21 gives the Malformed_Query error that you encountered:

[object Object]: The same comparison operation (LIKE or =) must be specified for all OR conditions

So it appears, to me at least, that this error is specifically acknowledging a limitation on how CMDT's can be queried.

As Phil noted in his answer, OR is not one of the SOQL operators supported in the where conditionExpression for custom metadata types.

Answer 2

As per the documentation for Custom Metadata Types, the OR operator is not actually supported at all in SOQL for querying these objects:

• You can use the following operators.
  • IN and NOT IN
  • =, >, >=, <, <=, and !=
  • LIKE, including wild cards
  • AND

I think you were just "lucky" that your original expression appeared to work; the only time we tried to have an OR condition we actually got a compilation error, and that was on a Spring or Summer '20 org.