The interesting aspect of this problem is it only fails in a certain company. Yet the data in ledgerTable is the same as in other companies. Also, there's no problem if I use other fields in the where clause.

After playing around with parameters and settings I've figured:i) If I change the caching from Entire Table cache to another type of cache, the problem's gone but this impact the performance of the system. [The problem comes back if I change it back to Entire Table cache]ii) I can change the "Primary index" to another index to solve the problem. [The problem actually disappear even after I change the primary index back to its original one]

During the debug process I read through some articles and other resources. While I have no logical explanation on this, it seems that there's a slight chance the client side record caching would fail. Which in turn leads to problem in record selection related to primary key. [For record cache populates itself with primary key]

P.S. Anyone who has a better idea of what's happening please kindly share it with me. Thanks in advance.