"unexpected EOF" error when using concatenated text on net_dns_record table

[massyn]

Describe the bug
Using a LEFT JOIN on the net_dns_record table (from the net plugin), while doing a csv-based lookup on a text file results in an "unexpcted EOF" error

Warning: executeQueries: query 1 of 1 failed: unexpected EOF

Steampipe version (steampipe -v)
steampipe version 0.15.2

To reproduce

• Create a simple csv file in the relevant folder where the csv plugin reads the files, called "domains.csv" with the following contents

domain
steampipe.io

• In this example, look for the dmarc record in DNS using the following SQL query

SELECT
    Q.domain,
    N.value
FROM
    domains Q
LEFT JOIN
    net_dns_record N
    ON N.domain = '_dmarc.' || Q.domain 
    AND N.type = 'TXT'

Expected behavior
I am expecting the LEFT JOIN to respect the concatenation statement, and do a lookup of the TXT record in DNS of _dmarc.steampipe.io (as per the CSV file).

Additional context
In another example, I was able to pass a simple SELET 'steampipe.io' as domain query, which worked fine. It would appear the combination of using net_dns_record and csv in the join with the concatenation is a problem. A direct join without the concatenation works fine.

[Subhajit97]

Hi @massyn, thanks for trying out the Net plugin!
I tried the above mentioned steps and was able to reproduce that error. Will take a look at this issue.
In the meantime can you try the below query and check if this works?

-- list domains from domains.csv
with domain_list as (
  select
    concat('_dmarc.', domain) as domain
  from
    domains
  order by
    domain
)
select
  domain,
  value
from
  net_dns_record
where
  domain in (
    select domain from domain_list
  )

[massyn]

The workaround script you've provided is working correctly. I'll use this in the mean time while you're working on the bugfix.

[massyn]

The same bug exists in the net_http_request module too.

[github-actions]

'This issue is stale because it has been open 30 days with no activity. Remove stale label or comment or this will be closed in 30 days.'

[github-actions]

'This issue is stale because it has been open 30 days with no activity. Remove stale label or comment or this will be closed in 30 days.'

[misraved]

Associated FDW issue - turbot/steampipe-postgres-fdw#220

[dalssaso]

Hi, i tried using the net_http_request table and I got a simular error as mentioned above, but even with the workouround I got the exact same error

with service_url_list as (
  SELECT concat('http://url?name=', name) as service_url FROM kubernetes_deployment
)
select 
  k.name,
  k.namespace,
  r.url,
  i -> 'field' ->> 'name' as "Name",
from
  kubernetes_deployment as k,
  net_http_request as r,
  jsonb_array_elements(r.response_body::jsonb -> 'items') as i
where
   r.url in (select service_url::text from service_url_list);

PS.: URLs are normalized because they're internal but the request works just fine

Also, the select concact query works like a charm, but not when using inside of the where clause

Is there any other way that i could get this working before the patch?

[e-gineer]

The main "workaround" is trying to trick the postgres planner into do the subquery first rather than magically collapsing your query to combine them together. This will prevent it attempting a full table scan of the steampipe table that requires a qual.

One trick that sometimes works (and is used above) is to put an order by in the first query, e.g.:

with service_url_list as (
  SELECT concat('http://url?name=', name) as service_url FROM kubernetes_deployment order by service_url
)

Any chance that helps?

You can also better understand the plans by using explain analyze select ..., it should show the order of execution and the quals it will expect.

Sorry we don't have a better answer for now, hoping the workaround nails it!

[dalssaso]

@e-gineer thanks for the advice, it really worked

I didn't really understood the workaround before your explanation as I am not a real SQL person (just getting my feet wet)

Thanks for the help

[github-actions]

'This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.'

[github-actions]

'This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.'

[github-actions]

'This issue was closed because it has been stalled for 90 days with no activity.'