Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Query is not working when trying to join with a concatenated value #322

Open
bigdatasourav opened this issue Jun 15, 2023 · 0 comments
Open

Query is not working when trying to join with a concatenated value #322

bigdatasourav opened this issue Jun 15, 2023 · 0 comments
Assignees
@kaidaguerre

Comments

@bigdatasourav
Copy link

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@kaidaguerre kaidaguerre

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@kaidaguerre @bigdatasourav