+ Start a Discussion
harry.freeharry.free 

Non-selective queries issue with special characters.

Hi Guys,

 

I'm running into the issue while working with a trigger. The query is something like:

 

[select Id from Lead where Name in :newNames and Phone in :newPhones]

It works fine when the newNames values are {'Harry Zhang', 'Tom'}, but if the values are {'/// ////', '__ __'} or some othe special characters like '-, ., *, the non-selective queries error happen. Can anyone give any suggestion why these special characters would cause the error? 

 

To be clear, this is not a syntax error with the query, even the newNames with the value of {'/// ////', '__ __'} can get the correct lead in the system log window, it just doesn't work in the trigger. Following are details about non-selective queries issue from SF documentation.


For more efficient SOQL queries, particularly for queries inside of triggers, use selective (indexed) queries. Selective queries filter on primary keys, foreign keys, names, audit dates (such as LastModifiedDate), or External ID fields. In large organizations, non-selective queries could be stopped at runtime in order to prevent very long running operation times. If you need them for your application, contact your salesforce.com representative.


Note: If you use a non-selective query in a trigger against an object that contains more than 100000 records an error is generated. You should include indexed fields in the WHERE clause to avoid these exceptions.

kiranmutturukiranmutturu

i think you have to use escapeSingleQuotes method to treat all single quotation marks as enclosing strings, instead of database commands

harry.freeharry.free

Sorry I didn't describe the issue clearly. {'Harry Zhang', 'Tom'} means a string collection with two strings Harry Zhang and Tom.

 

Ankit AroraAnkit Arora

When you are putting values in list, just use this String.escapeSingleQuotes(YOUR_VALUE).

 

 

Thanks
Ankit Arora

 

harry.freeharry.free

I believe escapeSingleQuotes really helps in some situation, but not in this scenario. 

kiranmutturukiranmutturu

then you have to identify the characters and use replace method....

Ankit AroraAnkit Arora

Yes, if you have a particular set of special characters then you can replace them using "\".

 

 

Thanks
Ankit Arora

 

harry.freeharry.free

Thanks guys, I've updated my question with more details.

Ankit AroraAnkit Arora
harry.freeharry.free

Thanks Ankit, that's exactly my question. I know the name field is indexed field, that's why I added it to the trigger, but I'm not sure why name like //// ///// or ___ ___ causes the issue. Maybe I need to contact SF support to solve the problem.

zettaneer1.3904945477293455E12zettaneer1.3904945477293455E12
After spending a day on this same issue, we too got the misleading error:

"Non-selective query against large object type (more than 100000 rows)"

when doing the following query in a trigger:

List<Account> accounts = [SELECT Id,Name FROM Account WHERE Name IN :mapCompanyNamesEntered.keySet()];

Turns out the culprit was special characters! (Non-word characters and punctuation!)

This is a undocumented feature or bug in Salesforce! The error is completely misleading! For example, the query above will fail with the following types of strings (all hyphens, all asterisks, all periods, a single period). Apparently these characters act as wildcards and cause the the above error?

mapCompanyNamesEntered.put('---',1);
mapCompanyNamesEntered.put('**',1);
mapCompanyNamesEntered.put('...',1);m
apCompanyNamesEntered.put('.',1);


How did we resolve our particular problem? With regular expressions:

// ignore company names with all non-word characters
string regexAllNonWordCharacters =  '\\W+';

//The following should evaluate to TRUE
//Boolean test = Pattern.matches(regexAllNonWordCharacters, '----');
//System.debug('Hello world - matches: ' + test);

Hope this helps someone, and harry.free, hope you figured out how to work around it.