2

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!

  • Without the full context of the filter, it's impossible to say for sure. Maybe it's dependent on how it's used. Context matters. – sfdcfox Sep 15 at 20:47
  • @sfdcfox Thank you, I have updated the question with a more complete query. – Brandon Sep 15 at 21:02
  • I tested your query and it worked fine. Your issue must be in the way you're constructing your queryString. Perhaps try to debug it to see the exact query that's being run, and see if you can spot any issues in it. This is the code I'd tested in an anonymous execution: String queryString = 'SELECT Id FROM Account WHERE Type=\'Customer\' and (Industry = null or Industry like \'%Bank%\') order by Name'; system.debug(Database.query(queryString)); – Instread Sep 15 at 21:37
2

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

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.

| improve this 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.