interface conversion error with cold cache when querying aws_route53_record

[tinder-tder]

Describe the bug
when running a new query on aws_route_53_record an interface conversion error is emittied when using an 'in' clause on the zone_id field
Error: interface conversion: interface {} is []interface {}, not string (SQLSTATE HV000)

manipulating the .cache settings shows the issue, a work around was to change the qualifiers to populate the cache, then the followup query with an in clause works

Steampipe version (steampipe -v)
Steampipe v0.21.1

Plugin version (steampipe plugin list)
0.121.1

To reproduce

Welcome to Steampipe v0.21.1
For more information, type .help
> select
      count(*)
    from
      aws_prod.aws_route53_record as a
    where
      a.zone_id in ('XXXXXX','YYYYY')
      and a.type in ('CNAME', 'A');
+-------+
| count |
+-------+
| 226   |
+-------+
> .cache off
> select
      count(*)
    from
      aws_prod.aws_route53_record as a
    where
      a.zone_id in ('XXXXXX','YYYYY')
      and a.type in ('CNAME', 'A');

Error: interface conversion: interface {} is []interface {}, not string (SQLSTATE HV000)


+-------+
| count |
+-------+
+-------+
> .cache on
> select
      count(*)
    from
      aws_prod.aws_route53_record as a
    where
      a.zone_id in ('XXXXXX','YYYYY')
      and a.type in ('CNAME', 'A');

Error: interface conversion: interface {} is []interface {}, not string (SQLSTATE HV000)

+-------+
| count |
+-------+
+-------+
> select
      count(*)
    from
      aws_prod.aws_route53_record as a
    where
      a.zone_id in ('XXXXXX','YYYYY')
      and a.type in ('CNAME', 'A');

Error: interface conversion: interface {} is []interface {}, not string (SQLSTATE HV000)

+-------+
| count |
+-------+
+-------+

> select
      count(*)
    from
      aws_prod.aws_route53_record as a
    where
      a.zone_id = 'XXXXXX' or a.zone_id = 'YYYYY'
      and a.type in ('CNAME', 'A');
+-------+
| count |
+-------+
| 263   |
+-------+
> select
      count(*)
    from
      aws_prod.aws_route53_record as a
    where
      a.zone_id in ('XXXXXX','YYYYY')
      and a.type in ('CNAME', 'A');
+-------+
| count |
+-------+
| 226   |
+-------+
>

Expected behavior
in clause should work from teh first query

[ParthaI]

Hello @tinder-tder, thank you for bringing this issue to our attention, and I truly appreciate your engagement with Steampipe.

I have successfully replicated the error (Error: interface conversion: interface {} is []interface {}, not string (SQLSTATE HV000)), and identified that it originates from using multiple IN operators in a single query. Here are my observations on this matter.

case 1: Using zone_id with IN operator and type with OR operator (Working fine)

> select
  name, type
from
  aws_route53_record as a
where
  a.zone_id in ('Z0932967H1B9NJ7ZL7LX', 'Z02488371E7NPRCAR7QSQ') 
  and a.type = 'CNAME' or a.type = 'A'
+----------------------+-------+
| name                 | type  |
+----------------------+-------+
| test1.test.23.com.   | CNAME |
| test3.test.23.com.   | CNAME |
| tewst44.test.23.com. | A     |
| testee.test343.com.  | CNAME |
| dsads.test343.com.   | A     |
| test.test343.com.    | A     |
+----------------------+-------+

Time: 3.5s. Rows fetched: 12. Hydrate calls: 0.

Case 2: Using zone_id with OR operator and type with IN operator (Working fine)

> select
  name, type
from
  aws_route53_record as a
where
  a.zone_id = 'Z0932967H1B9NJ7ZL7LX' or a.zone_id =  'Z02488371E7NPRCAR7QSQ'
  and a.type in ('CNAME', 'A');
+----------------------+-------+
| name                 | type  |
+----------------------+-------+
| dsads.test343.com.   | A     |
| test.test343.com.    | A     |
| testee.test343.com.  | CNAME |
| test.23.com.         | SOA   |
| test3.test.23.com.   | CNAME |
| test1.test.23.com.   | CNAME |
| txt.test.23.com.     | TXT   |
| tewst44.test.23.com. | A     |
| test.23.com.         | NS    |
+----------------------+-------+

Time: 3.6s. Rows fetched: 12. Hydrate calls: 0.

Case 3: Using IN operator for both of the columns (Throws error)

> select
  name, type
from
  aws_route53_record as a
where
  a.zone_id in ('Z0932967H1B9NJ7ZL7LX', 'Z02488371E7NPRCAR7QSQ')
  and a.type in ('CNAME', 'A');

Error: interface conversion: interface {} is []interface {}, not string (SQLSTATE HV000)

+------+------+
| name | type |
+------+------+
+------+------+

Time: 1.7s.

I suspect the issue might lie with the FDW or the Steampipe Plugin SDK in terms of query parsing. Consequently, I have submitted a support request to the Steampipe Plugin SDK team, specifically addressing this concern.

We can track the issue here: turbot/steampipe-plugin-sdk#702

Thank You!

[tinder-tder]

@ParthaI Thanks will keep an eye out for the fix!

[ParthaI]

Hey @tinder-tder, We are closing this issue because we can track the issue here turbot/steampipe-plugin-sdk#702. Please feel free to reopen the issue if you want to share or discuss anything.