Bug with cache when aggregate

[antoine-conserto]

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

[pskrbasu]

@antoine-conserto Thanks for raising this detailed issue. I'll take a look into it and try to reproduce the issue.

[antoine-conserto]

Any update ?

[vbatychko-modeln]

Can confirm that with v0.24.2 and AWS plugin 0.147.0. This select does not give consistent result. Sometimes, 0, sometimes 2 or more.
Disabling cache fixes bug. Note that I'm using aggregation for multiple AWS accounts.

steampipe=> select count(*) from (select count(*), subnet_arn from aws_vpc_subnet group by subnet_arn having count(*)>1) t;
 count
-------
     2
(1 row)

[pskrbasu]

@antoine-conserto @vbatychko-modeln Apologies for the delay. I'll try this out today and update this thread.

[pskrbasu]

@antoine-conserto A small update on this. I was unable to reproduce the bug using your chaos table. I'm always getting the expected (This query should always return 200,000 in sum and 10,000 in distinct count.). I'll keep digging.

Meanwhile, I'll also try using the AWS query @vbatychko-modeln provided.

[antoine-conserto]

I also retested.

In steampipe v0.24.2

And with v1.0.0

I did not change the sdk version (v5.10.4) on the chaos plugin. Maybe your refactor on the FDW fixed the problem, in any case it no longer seems relevant to me.

Thanks for watching @pskrbasu

[e-gineer]

Nice test results @antoine-conserto ... I think we can close this @pskrbasu?

[vbatychko-modeln]

Can confirm again, with v1.0.0 of steampipe & aws plugin, I cannot reproduce this bug, thanks.

[pskrbasu]

Thanks @vbatychko-modeln @antoine-conserto for confirming.