Hi,

Describe the bug
When using cache on a table that calls an api with field selection and filter, then an aggregation like sum or count disctint, sometimes there is a data inconsistency.

Steampipe version (steampipe -v)
Steampipe v0.23.5

To reproduce

Use this sample with chaos plugin and table chaos_bug_cache_sum : https://github.com/antoine-conserto/steampipe-plugin-chaos/tree/feature/bug-cache-sum

Run steampipe service
Run the query :
SELECT SUM(c.amount) AS "sum", COUNT(distinct id) AS "count" FROM chaos.chaos_bug_cache_sum c

Same problem with steampipe query directly.

Expected behavior

This query should always return 200,000 in sum and 10,000 in distinct count.

database-2024-09-05_ok.log
plugin-2024-09-05_ok.log

Additional context

It is not systematic. Sometimes I have the right result, if this is the case you have to restart the service or clear cache

Sometimes I only got the sum wrong but the distinct count was good.

I don't have the problem if I disable the cache on the table.

It's only the first query when caching that seems problematic. If I rerun after caching, I get the correct result and don't reproduce.

Exemple KO 1

database-2024-09-05_ko_1.log
plugin-2024-09-05_ko_1.log

Exemple KO 2

database-2024-09-05_ko_2.log
plugin-2024-09-05_ko_2.log