PR#2357 (aws plugin 1.5.0) breaks aws_ecr_image_scan_finding - 'List' call for table 'aws_ecr_image_scan_finding' is missing 1 required qual: column:'repository_name'

[captainfalcon23]

Describe the bug
When using AWS provider 1.4.0, the following query works fine and returns results with no errors as expected and works fine. However, after upgrading to aws plugin 1.5.0, we get the following error:

Error: rpc error: code = Internal desc = myawsaccount: rpc error: code = Internal desc = 'List' call for table 'aws_ecr_image_scan_finding' is missing 1 required qual: column:'repository_name' operator: =
 (SQLSTATE HV000)

select
    distinct REPLACE(
        REPLACE(innerq."AWS_ACCOUNT_NAME", 'aws_', ''),
        '_',
        '-'
    ) as "AWS_ACCOUNT_NAME",
    innerq.region as "AWS_REGION",
    innerq.repository_name as "AWS_REPO",
    innerq.image_tag as "ECR_IMAGE_TAG",
    innerq.name as "COMMON_VULN_ID",
    innerq.uri as "VULN_INFO_URL",
    innerq.severity as "VULN_SEVERITY",
    innerq.package_version as "VULN_PACKAGE_VERSION",
    innerq.package_name as "VULN_PACKAGE_NAME"
from
    (
        with latest_image_ts as (
            select
                repository_name,
                max(image_pushed_at) as image_pushed_at
            from
                myawsaccount.aws_ecr_image
            group by
                repository_name
        ),
        images_with_tags as (
            select
                _ctx,
                region,
                repository_name,
                image_pushed_at,
                jsonb_array_elements_text(image_tags) :: text as image_tag
            from
                myawsaccount.aws_ecr_image
        )
        select
            i._ctx ->> 'connection_name' as "AWS_ACCOUNT_NAME",
            i.region,
            i.repository_name,
            i.image_pushed_at,
            i.image_tag,
            f.name,
            f.uri,
            f.severity,
            f.description,
            (
                jsonb_path_query(f.attributes, '$[*] ? (@.Key == "package_name")') -> 'Value' #>>'{}')::text as package_name,
                (
                    jsonb_path_query(
                        f.attributes,
                        '$[*] ? (@.Key == "package_version")'
                    ) -> 'Value' #>>'{}')::text as package_version
                    from
                        images_with_tags i
                        join latest_image_ts l on (l.repository_name, l.image_pushed_at) = (i.repository_name, i.image_pushed_at)
                        join myawsaccount.aws_ecr_image_scan_finding f on (f.repository_name, f.image_tag) = (l.repository_name, i.image_tag)
                    order by
                        repository_name,
                        image_tag,
                        severity,
                        name,
                        package_name
                ) innerq;

Steampipe version (steampipe -v)
Steampipe v1.0.1

Plugin version (steampipe plugin list)
hub.steampipe.io/plugins/turbot/[email protected] | 1.4.0
hub.steampipe.io/plugins/turbot/aws@latest | 1.5.0

To reproduce
Run the query above using aws plugin 1.40 and 1.5.0

Expected behavior
Query should continue to work, as from what I can see in the PR, only an additional qual was added, which I wouldn't have expected would affect this functionaility.

Additional context
Refer #2356 and #2357

[captainfalcon23]

pinging @ParthaI as you worked on the PR and other issue

[ParthaI]

Hello @captainfalcon23, I’ve raised a draft PR with some updates to the table design to better handle complex queries. However, the design hasn’t been finalized yet. If you have some time, could you please try it out on the PR branch and share your feedback? Thank you!

[captainfalcon23]

Hey @ParthaI sorry for the delay. I tested this today. So good news and bad news. Good news is the query now runs to completion. Bad news is that vulnerabilities for ALL image tags in a repo are being returned, instead of just the image specified in the join here:

  join aws_all.aws_ecr_image_scan_finding f on (f.repository_name,f.image_tag) = (l.repository_name,i.image_tag)

On another topic, while the old plugin works, I noticed a while ago that due to the same above join, I assume aws_ecr_image_scan_finding has a parent hydrate using listAwsEcrImageTags. What happens is, this parent hydrate doesn't pass the context of which account it is from, so when using an aggregator, it tries all accounts defined, so the logs fill with:

1737498382703: aws_ecr_image.listAwsEcrImageTags: api_error="operation error ECR: DescribeImages, https response error StatusCode: 400, RequestID: 0adb282c-eeec-4cc1-8429-04463389a531, RepositoryNotFoundException: The repository with name 'blah' does not exist in the registry with id '0000000'"

Just thought to mention it while working on this particular table.