Thursday, November 21, 2013

Here's an important bit of SOQL "SELECT * FROM Lead" trivia I bet you do not know...

Quick quiz: If you run the SOQL statement:

  •  SELECT id, LastModifiedDate FROM Lead WHERE LastModifiedDate
will it ever return records whose LastModifiedDate is TODAY?

If your answer is "No" then you are wrong. In fact it is pretty simple to make the SOQL return lots of records from TODAY. Here's how.

  1. Start the SOQL statement
  2. Modified one or more Lead records that match the SOQL criteria BUT have not yet been returned by the query.
  3. The query will return the modified records WITH LastModifiedDate value outside the range of the SOQL statement.

I discovered this "feature" working with a customer who has a highly active Lead table (upserts every couple of seconds). Once in a blue moon a lead record would be copied to SQL/Server even though it did not meet the criteria of the SOQL (the app was CopyStorm -- Though it took a while to figure out the problem (since it appeared to be random and rare), reproducing it was easy once the root cause was understood.

No comments: