steampipe-postgres-fdw
steampipe-postgres-fdw copied to clipboard
Published
20 hours ago •
turbot
turbot
Quals issue with multiple column key in github plugin
I don't understand why the final query in this list doesn't work?
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f'
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name | sha | author_login | author_date | comments_url | commit
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10 | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
| | | | | |
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
>
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha in ('7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name | sha | author_login | author_date | comments_url | commit
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10 | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
| | | | | |
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
>
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha in (select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name | sha | author_login | author_date | comments_url | commit
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10 | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
| | | | | |
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
>
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = any(select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name | sha | author_login | author_date | comments_url | commit
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10 | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
| | | | | |
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
>
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = any(array['7ae796c3e3b1aefbe60a5810ca8f971dbd05745f'])
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name | sha | author_login | author_date | comments_url | commit
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10 | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
| | | | | |
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
>
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = (select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+----------------------+-----+--------------+-------------+--------------+--------+-----------------+----------------+-------+----------+---------+---------+---------+-------+-----+----------+
| repository_full_name | sha | author_login | author_date | comments_url | commit | committer_login | committer_date | files | html_url | message | node_id | parents | stats | url | verified |
+----------------------+-----+--------------+-------------+--------------+--------+-----------------+----------------+-------+----------+---------+---------+---------+-------+-----+----------+
+----------------------+-----+--------------+-------------+--------------+--------+-----------------+----------------+-------+----------+---------+---------+---------+-------+-----+----------+
>
This occurs because we fail to resolve the qual expression value of select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f
Looking into it...
in works
select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha in (select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------
| repository_full_name | sha | author_login | author_date | comments_url | commit
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10 | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author":{"date":"2021-04-15T21:09:43Z","email":"[email protected]","name":"Cody Bruno"},"comment_count":0,"committer":{"date":"2021-04-15T21:09:
| | | | | |
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------
the query plan shows why:
explain select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = (select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+-----------------------------------------------------------------------------------------+
| QUERY PLAN |
+-----------------------------------------------------------------------------------------+
| Foreign Scan on github_commit (cost=0.01..160000000000000.00 rows=1000000 width=1600) |
| Filter: ((repository_full_name = 'turbot/steampipe-plugin-aws'::text) AND (sha = $0)) |
| InitPlan 1 (returns $0) |
| -> Result (cost=0.00..0.01 rows=1 width=32) |
+-----------------------------------------------------------------------------------------+
when = is used the planner selects a foreign scan on github_commit with the filter
repository_full_name = 'turbot/steampipe-plugin-aws'::text) AND (sha = $0))
presumably the $0 is populated by postgres duting evaluation
explain select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha in (select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+---------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+---------------------------------------------------------------------------------------------------------------------------------------+
| Foreign Scan on github_commit (cost=0.00..160000000000000.00 rows=1000000 width=1600) |
| Filter: ((repository_full_name = 'turbot/steampipe-plugin-aws'::text) AND (sha = '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f'::text)) |
+---------------------------------------------------------------------------------------------------------------------------------------+
when in is used the query plan uses a foreign scan with the filter
((repository_full_name = 'turbot/steampipe-plugin-aws'::text) AND (sha = '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f'::text))
I'm unclear on whether we can access the value of $0 during the github_commit scan
If we explain a slightly more complex query with a non-trivial inner select:
explain select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = ( select sha from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and committer_date='2021-04-15 21:09:43' limit 1)
we get
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Foreign Scan on github_commit (cost=30000000.00..160000030000000.00 rows=1000000 width=1600) |
| Filter: ((repository_full_name = 'turbot/steampipe-plugin-aws'::text) AND (sha = $0)) |
| InitPlan 1 (returns $0) |
| -> Limit (cost=0.00..30000000.00 rows=1 width=300) |
| -> Foreign Scan on github_commit github_commit_1 (cost=0.00..30000000000000.00 rows=1000000 width=300) |
| Filter: ((repository_full_name = 'turbot/steampipe-plugin-aws'::text) AND (committer_date = '2021-04-15 21:09:43'::timestamp without time zone)) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
details on InitPlan here: https://www.postgresql.org/message-id/4572.1280671706%40sss.pgh.pa.us It is an inner query which can be executed first as it has no external dependencies.
So it may be possible to access the value somehow, although our current expression evaluation code fails to evaluate it (We use
exprState := C.ExecInitExpr(valueExpression, (*C.PlanState)(unsafe.Pointer(node)))
econtext := node.ss.ps.ps_ExprContext
value = C.ExecEvalExpr(exprState, econtext, &isNull)
)