(Setting-aside (for the moment ...) the apparently-immediate issue of “wrong results” ...), the immediate potential-issue that occurs to me here is SQLite’s handling of transactions.

Basically, unless your request (whether read or write ...) is wrapped in a transaction, SQLite will not avail itself of any niceties such a “laziness.”   It will physically repeat every read, and(!) it will physically verify every write!

(We might reasonably assume that SQLite Studio Editor “would, of course, be fully aware of such things,” whereas (likewise, maybe, “of course”) your Perl code – just like mine, back in the day – might not yet know to do that.)

Short-Answer:   Do This Now:   “Bracket all of your SQLite calls, whether(!) read or write, in transactions.”

Even though the SQLite engine “earnestly attempts to maintain” the abstract niceties of other SQL engines, it ultimately must physically-confront the physical-reality that there is no(!) “SQL server.”   Its actual physical situation is therefore no more “evolutionarily advanced,” in any-and-all “shared-file” situation, than that of dBase, Paradox, or Clipper (say).   The implementation has obviously therefore decided to use “a transaction,” regardless of (apparent, but irrelevant) kind, as “a file-lock,” and thus the ability to use “lazy™” in-memory caching.

“Personally been there.   Personally whacked my head against that.”

HTH™ ...