Can not join from aws_vpc_security_group_rules to ipstack_ip

[dboeke]

Describe the bug
Attempting to join aws_vpc_security_group_rules.cidr_ip to ipstack_ip.ip fails with a quals error: Error: 'List' call requires an '=' qual for column: ip error

Steampipe version (steampipe -v)
Example: v0.3.6

To reproduce

  select
    i.ip,
    i.region_code,
    i.country_code
  from 
    aws_vpc_security_group_rule r,
    ipstack_ip i
  where
    i.ip = r.cidr_ip
    and masklen(r.cidr_ip) >= 16

or

select
    ip,
    region_code,
    country_code
  from
    ipstack_ip
  where
    ip in (
      select
        cidr_ip
      from
        aws_vpc_security_group_rule
      where
        masklen(cidr_ip) >= 32
    )

Expected behavior
Query should return a result e.g.:

select 
  city, 
  region_code,
  country_code
from 
  ipstack_ip 
where 
  ip in (inet '8.8.8.8/32', inet '1.1.1.1/32')
+---------------+-------------+--------------+
| city          | region_code | country_code |
+---------------+-------------+--------------+
| Mountain View | CA          | US           |
| Sydney        | NSW         | AU           |
+---------------+-------------+--------------+

Additional context
Add any other context about the problem here.

[johnsmyth]

@dboeke
Im not sure it this is part of the issue, but the data types don't match -- The aws_vpc_security_group_rule.cidr_ip table is a cidr data type, where as the ipstack_ip.ip is an inet. Im not that familiar with the ipstack api (maybe @e-gineer knows?), but if the api supports looking up cidrs, we may need to add a cidr_ip column as a cidr type, and do AnyColumns on the List in the ipstack plugin. (Or add a separate ip_stack_cidr table?)

[johnsmyth]

@dboeke - I did a quick hack of the ipstack plugin to change the data type, but I believe that this might require an FDW change. I changed the ipstack to a cidr , but it still fails with the error. @kaidaguerre I believe this may require some FDW changes as well - I think this is related to the error we see when we cast the keycolumn in the qualifier. Notice that postgres converts the query to cast to inet:

> select
    i.ip,
    i.region_code,
    i.country_code
  from 
    aws_vpc_security_group_rule r,
    ipstack_ip i
  where
    i.ip = r.cidr_ip
Error: 'List' call requires an '=' qual for column: ip
> 
> 
> explain select
    i.ip,
    i.region_code,
    i.country_code
  from 
    aws_vpc_security_group_rule r,
    ipstack_ip i
  where
    i.ip = r.cidr_ip
+------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                 |
+------------------------------------------------------------------------------------------------------------+
| Nested Loop  (cost=0.00..10000300010000.00 rows=5000000000 width=96)                                       |
|   ->  Foreign Scan on aws_vpc_security_group_rule r  (cost=0.00..10000000000000.00 rows=1000000 width=100) |
|   ->  Foreign Scan on ipstack_ip i  (cost=0.00..300.00 rows=1 width=300)                                   |
|         Filter: ((ip)::inet = (r.cidr_ip)::inet)                                                           |
+------------------------------------------------------------------------------------------------------------+

Time: 921.533µs
> 

[dboeke]

@johnsmyth @kaidaguerre

From what I read, you can cast cidr type to inet, so this should work without need for changing the data types:

select
  i.ip,
  i.region_code,
  i.country_code
from 
  aws_vpc_security_group_rule r,
  ipstack_ip i
where
  i.ip = r.cidr_ip::inet
  and masklen(r.cidr_ip) >= 32;

but it doesn't

also this doesn't work:

select
  ip,
  region_code,
  country_code
from
  ipstack_ip
where
  ip in (
    select
      cidr_ip::inet
    from
      aws_vpc_security_group_rule
    where
      masklen(cidr_ip) >= 32
  )

[kaidaguerre]

we are failing to convert the quals to a form we can use

qualFromOpExpr fails to convert the quals to canonical form - the left qual is of type function - the canonicalOpExpr function only supports Var types for the left operand

2021-04-14 11:49:12.249 BST [9846] WARNING:  canonicalOpExpr, arg length: 2, base_relids 32813278
2021-04-14 11:49:12.249 BST [9846] WARNING:  l arg: {FUNCEXPR :funcid 697 :funcresulttype 23 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 9 :vartype 650 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 9 :location 142} :resulttype 869 :resulttypmod -1 :resultcollid 0 :relabelformat 2 :location -1}) :location 134}
2021-04-14 11:49:12.249 BST [9846] WARNING:  r arg: {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 156 :constvalue 4 [ 32 0 0 0 0 0 0 0 ]}
2021-04-14 11:49:12.250 BST [9846] WARNING:  before if (IsA(l, Var)
2021-04-14 11:49:12.250 BST [9846] WARNING:  L is not a Var
2021-04-14 11:49:12.250 BST [9846] WARNING:  canonicalOpExpr returning

No quick solution suggests itself, this will take a little digging