steampipe-postgres-fdw
steampipe-postgres-fdw copied to clipboard
Published
20 hours ago •
turbot
turbot
Query is not working when trying to join with a concatenated value
join 2 tables (Here artifact_name is optional key quals in trivy_scan_artifact table)
select artifact_name, artifact_type, metadata from trivy_scan_artifact join aws_ecr_repository on artifact_name = repository_uri
After running the above query, I can get the repository_uri in the table trivy_scan_artifact via the optional column. Expected.
With different table (Here artifact_name is optional key quals in trivy_scan_artifact table)
select artifact_name, artifact_type, metadata from trivy_scan_artifact join aws_ecr_image on artifact_name = concat(account_id,'.dkr.ecr.',region,'.amazonaws.com/',repository_name,'@',image_digest)
Here I should get the concatenated value in the table trivy_scan_artifact via the optional column artifact_name, but I am not getting that.
With CT (Here artifact_name is optional key quals in trivy_scan_artifact table)
with ecr_images as ( select concat(repository_uri,'@',image_digest) as image_uri from aws_ecr_repository as r, aws_ecr_image as i where i.repository_name = r.repository_name )select artifact_name, artifact_type, metadata from trivy_scan_artifact join ecr_images on artifact_name = image_uri
Here also, I am not getting the value in the table trivy_scan_artifact via the optional column artifact_name.
query plan for the above queries-
> explain select
artifact_name,
artifact_type,
metadata
from
trivy_scan_artifact
join aws_ecr_repository on artifact_name = repository_uri
+-------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+-------------------------------------------------------------------------------------------------+
| Nested Loop (cost=0.00..10060002000000.00 rows=5000000000 width=96) |
| -> Foreign Scan on aws_ecr_repository (cost=0.00..10000000000000.00 rows=1000000 width=100) |
| -> Foreign Scan on trivy_scan_artifact (cost=0.00..60000.00 rows=200 width=300) |
| Filter: (artifact_name = aws_ecr_repository.repository_uri) |
+-------------------------------------------------------------------------------------------------+
> explain select
artifact_name,
artifact_type,
metadata
from
trivy_scan_artifact
join aws_ecr_image on artifact_name = concat(account_id,'.dkr.ecr.',region,'.amazonaws.com/',repository_name,'@',image_digest)
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Nested Loop (cost=0.00..30040001000000.00 rows=5000000000 width=96)
| -> Foreign Scan on trivy_scan_artifact (cost=0.00..30000000000000.00 rows=1000000 width=300)
| -> Foreign Scan on aws_ecr_image (cost=0.00..40000.00 rows=100 width=400)
| Filter: ((trivy_scan_artifact.artifact_name = concat(account_id, '.dkr.ecr.', region, '.amazonaws.com/', repository_name, '@', image_digest)) AND (trivy_scan_artifact.artifact_name = concat(account_id, '.
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> explain with ecr_images as (
select
concat(repository_uri,'@',image_digest) as image_uri
from
aws_ecr_repository as r,
aws_ecr_image as i
where
i.repository_name = r.repository_name
)select
artifact_name,
artifact_type,
metadata
from
trivy_scan_artifact
join ecr_images on artifact_name = image_uri
+---------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+---------------------------------------------------------------------------------------------------+
| Nested Loop (cost=0.00..33040400000000.00 rows=25000000000000 width=96) |
| -> Foreign Scan on aws_ecr_repository r (cost=0.00..20000000000000.00 rows=1000000 width=200) |
| -> Nested Loop (cost=0.00..12040400.00 rows=100000000 width=160) |
| -> Foreign Scan on aws_ecr_image i (cost=0.00..40000.00 rows=200 width=200) |
| Filter: (repository_name = r.repository_name) |
| -> Foreign Scan on trivy_scan_artifact (cost=0.00..60000.00 rows=200 width=300) |
| Filter: (artifact_name = concat(r.repository_uri, '@', i.image_digest)) |
+---------------------------------------------------------------------------------------------------+
FDW log label info -
# qual passed -
2023-06-15 08:16:00.779 UTC [INFO] hub: goFdwBeginForeignScan, connection 'trivy', table 'trivy_scan_artifact', explain: false
2023-06-15 08:16:00.780 UTC [INFO] hub: connection 'trivy', table 'trivy_scan_artifact', quals
----------------------------------------------------------------
Column: artifact_name, Operator: '=', Value: '?'
----------------------------------------------------------------
2023-06-15 08:16:00.780 UTC [INFO] hub: StartScan for table: aws_ecr_repository, cache enabled: false, iterator 0x140008b8300, 0 quals (1686816960226)
2023-06-15 08:16:05.285 UTC [INFO] hub: goFdwBeginForeignScan, connection 'trivy', table 'trivy_scan_artifact', explain: false
2023-06-15 08:16:05.286 UTC [INFO] hub: connection 'trivy', table 'trivy_scan_artifact', quals
----------------------------------------------------------------
Column: artifact_name, Operator: '=', Value: '097350876455.dkr.ecr.ap-south-1.amazonaws.com/hello'
----------------------------------------------------------------
2023-06-15 08:16:05.286 UTC [INFO] hub: StartScan for table: trivy_scan_artifact, cache enabled: false, iterator 0x140008b83c0, 1 quals (1686816965866)
2023-06-15 08:16:10.048 UTC [INFO] hub: goFdwBeginForeignScan, connection 'trivy', table 'trivy_scan_artifact', explain: false
2023-06-15 08:16:10.050 UTC [INFO] hub: connection 'trivy', table 'trivy_scan_artifact', quals
----------------------------------------------------------------
Column: artifact_name, Operator: '=', Value: '097350876455.dkr.ecr.ap-south-1.amazonaws.com/development/turbot'
----------------------------------------------------------------
2023-06-15 08:16:10.050 UTC [INFO] hub: StartScan for table: trivy_scan_artifact, cache enabled: false, iterator 0x14000136480, 1 quals (1686816970545)
2023-06-15 08:16:10.369 UTC [INFO] hub: goFdwBeginForeignScan, connection 'trivy', table 'trivy_scan_artifact', explain: false
2023-06-15 08:16:10.370 UTC [INFO] hub: connection 'trivy', table 'trivy_scan_artifact', quals
----------------------------------------------------------------
Column: artifact_name, Operator: '=', Value: '097350876455.dkr.ecr.ap-south-1.amazonaws.com/password-rotation'
----------------------------------------------------------------
2023-06-15 08:16:10.371 UTC [INFO] hub: StartScan for table: trivy_scan_artifact, cache enabled: false, iterator 0x140008b8780, 1 quals (1686816970745)
2023-06-15 08:16:10.645 UTC [57504] LOG: duration: 9865.055 ms execute stmtcache_9: select
artifact_name,
artifact_type,
metadata
from
trivy_scan_artifact
join aws_ecr_repository on artifact_name = repository_uri
# no qual passed
2023-06-15 08:17:40.897 UTC [INFO] hub: could not convert OpExpr to canonical form - NOT adding qual for OpExpr
2023-06-15 08:17:40.897 UTC [WARN] hub: RestrictionsToQuals: failed to convert 2 restrictions to quals
2023-06-15 08:17:40.898 UTC [INFO] hub: --------
2023-06-15 08:17:40.898 UTC [INFO] hub: no quals
2023-06-15 08:17:40.898 UTC [INFO] hub: --------
2023-06-15 08:17:40.898 UTC [INFO] hub: StartScan for table: trivy_scan_artifact, cache enabled: false, iterator 0x1400067a840, 0 quals (1686817060882)
2023-06-15 08:17:45.989 UTC [INFO] hub: goFdwBeginForeignScan, connection 'aws', table 'aws_ecr_image', explain: false
2023-06-15 08:17:45.989 UTC [INFO] hub: could not convert OpExpr to canonical form - NOT adding qual for OpExpr
2023-06-15 08:17:45.989 UTC [INFO] hub: could not convert OpExpr to canonical form - NOT adding qual for OpExpr
2023-06-15 08:17:45.989 UTC [WARN] hub: RestrictionsToQuals: failed to convert 2 restrictions to quals
2023-06-15 08:17:45.990 UTC [INFO] hub: --------
2023-06-15 08:17:45.990 UTC [INFO] hub: no quals
2023-06-15 08:17:45.990 UTC [INFO] hub: --------
2023-06-15 08:17:45.990 UTC [INFO] hub: StartScan for table: aws_ecr_image, cache enabled: false, iterator 0x14001335980, 0 quals (1686817065434)
2023-06-15 08:17:47.004 UTC [57504] LOG: duration: 6105.481 ms execute stmtcache_10: select
artifact_name,
artifact_type,
metadata
from
trivy_scan_artifact
join aws_ecr_image on artifact_name = concat(account_id,'.dkr.ecr.',region,'.amazonaws.com/',repository_name,'@',image_digest)
# no qual passed
2023-06-15 08:18:51.287 UTC [INFO] hub: no quals
2023-06-15 08:18:51.287 UTC [INFO] hub: --------
2023-06-15 08:18:51.287 UTC [INFO] hub: StartScan for table: trivy_scan_artifact, cache enabled: false, iterator 0x140000f7380, 0 quals (1686817131196)
2023-06-15 08:18:51.317 UTC [INFO] hub: goFdwBeginForeignScan, connection 'trivy', table 'trivy_scan_artifact', explain: false
2023-06-15 08:18:51.317 UTC [INFO] hub: failed to convert qual value; QualDefsToQuals: unsupported qual value (type T_FuncExpr), skipping
2023-06-15 08:18:51.317 UTC [WARN] hub: RestrictionsToQuals: failed to convert 1 restriction to quals
2023-06-15 08:18:51.318 UTC [INFO] hub: --------
2023-06-15 08:18:51.318 UTC [INFO] hub: no quals
2023-06-15 08:18:51.318 UTC [INFO] hub: --------
2023-06-15 08:18:51.318 UTC [INFO] hub: StartScan for table: trivy_scan_artifact, cache enabled: false, iterator 0x14000be3740, 0 quals (1686817131668)
2023-06-15 08:18:51.348 UTC [INFO] hub: goFdwBeginForeignScan, connection 'trivy', table 'trivy_scan_artifact', explain: false
2023-06-15 08:18:51.348 UTC [INFO] hub: failed to convert qual value; QualDefsToQuals: unsupported qual value (type T_FuncExpr), skipping
2023-06-15 08:18:51.349 UTC [WARN] hub: RestrictionsToQuals: failed to convert 1 restriction to quals
2023-06-15 08:18:51.349 UTC [INFO] hub: --------
2023-06-15 08:18:51.349 UTC [INFO] hub: no quals
2023-06-15 08:18:51.349 UTC [INFO] hub: --------
2023-06-15 08:18:51.349 UTC [INFO] hub: StartScan for table: trivy_scan_artifact, cache enabled: false, iterator 0x14001441440, 0 quals (1686817131662)
2023-06-15 08:18:51.381 UTC [INFO] hub: goFdwBeginForeignScan, connection 'trivy', table 'trivy_scan_artifact', explain: false
2023-06-15 08:18:51.381 UTC [INFO] hub: failed to convert qual value; QualDefsToQuals: unsupported qual value (type T_FuncExpr), skipping
2023-06-15 08:18:51.381 UTC [WARN] hub: RestrictionsToQuals: failed to convert 1 restriction to quals
2023-06-15 08:18:51.381 UTC [INFO] hub: --------
2023-06-15 08:18:51.381 UTC [INFO] hub: no quals
2023-06-15 08:18:51.381 UTC [INFO] hub: --------
2023-06-15 08:18:51.381 UTC [INFO] hub: StartScan for table: trivy_scan_artifact, cache enabled: false, iterator 0x14001b229c0, 0 quals (1686817131912)
2023-06-15 08:18:51.417 UTC [57504] LOG: duration: 12994.929 ms execute stmtcache_11:
with ecr_images as (
select
concat(repository_uri,'@',image_digest) as image_uri
from
aws_ecr_repository as r,
aws_ecr_image as i
where
i.repository_name = r.repository_name
)select
artifact_name,
artifact_type,
metadata
from
trivy_scan_artifact
join ecr_images on artifact_name = image_uri
