Apex Code Development You need to sign in to do that
Don't have an account?
Caffeine Strange SOQL Realities
SOQL that compares against the value null gives indeterminate results.
Example:
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002v1x' and PublicGroupId__c != null
Result=> returns no records (The PublicGroupId__c field does has a value)
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002v1x' and PublicGroupId__c = null
Result=> returns no records!
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002v1x' and PublicGroupId__c != ' '
Result=> returns 1 record!
But if we query for another nearly identical object like this:
select Id, PublicGroupId__c from WorkGroup__c where Id = ' a0OR00000002xqp ' and PublicGroupId__c != null
Result=> returns 1 record!!! (The PublicGroupId__c field does have a value)
select Id, PublicGroupId__c from WorkGroup__c where Id = ' a0OR00000002xqp ' and PublicGroupId__c = null
Result => returns no records
Does anyone know something about this SOQL ‘feature’?
Example:
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002v1x' and PublicGroupId__c != null
Result=> returns no records (The PublicGroupId__c field does has a value)
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002v1x' and PublicGroupId__c = null
Result=> returns no records!
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002v1x' and PublicGroupId__c != ' '
Result=> returns 1 record!
But if we query for another nearly identical object like this:
select Id, PublicGroupId__c from WorkGroup__c where Id = ' a0OR00000002xqp ' and PublicGroupId__c != null
Result=> returns 1 record!!! (The PublicGroupId__c field does have a value)
select Id, PublicGroupId__c from WorkGroup__c where Id = ' a0OR00000002xqp ' and PublicGroupId__c = null
Result => returns no records
Does anyone know something about this SOQL ‘feature’?
Contradictions do not exist. Whenever you think you are facing a contradiction, check your premises. You will find that one of them is wrong. - Ayn Rand
What is the type of the field PublicGroupId__c? Is it a string? Null generally does not apply to String-typed fields, but the empty string does, so if you're comparing a string to null you might get something you don't expect.
Werewolf,
Thanks for the reply.
I don't think there are contradictions. I think there's an out and out bug. PublicGroupId__c is a String, but the problem is that the return in non-deterministic. Programmers are generally against that sort of thing.
To expand, here are queries that do not return anything:
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002v1x' and PublicGroupId__c != null
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002v1x' and PublicGroupId__c != ''
But this one returns the record:
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002v1x' and PublicGroupId__c != ' '
Now I have an identical record, except with a different PublicGroupId__c:
And this query returns the record:
select Id, PublicGroupId__c from WorkGroup__c where Id = 'a0OR00000002xqp' and PublicGroupId__c != null
Now if both queries comparing to null either returned something or didn't return something, I'd be cool with it. But because you don't know whether it return a record or not, it seems like something needs a little fixing somewhere.