1

I have a new instance of Apache Superset and SQL queries to PostgreSQL databases from SQLLab is working. The exception is SQL which use the <<= 'is contained within or equals' network address operator:

WITH subnets AS (
SELECT UNNEST(networks) AS subnet,
       network_owner,
       owner_country,
       provider_country,
       registrar
  FROM network_data
)
   SELECT network_owner,
          owner_country,
          provider_country,
          registrar,
          subnet,
          (2 ^ (32 - MASKLEN(subnet)) - 2) AS subnet_size,
          (
              SELECT COUNT(DISTINCT(q.ip_address))
                FROM request q
               WHERE q.ip_address <<= subnet
          ) AS num_logged,
          u.timestamp AS blocked_timestamp
     FROM subnets
LEFT JOIN ufw_blocked u ON subnet = u.blocked_address_space
 ORDER BY network_owner, subnet

This works perfectly in pgadmin4 but SQLLab seems to mistake the <<= operator for 'less than or equals' and returns the errors below:

Unable to parse SQL:
Error parsing near '<=' at line 18:37
WHERE q.ip_address <<= subnet
                     ^
DB Engine Error: This database does not allow for DDL/DML, and the query could not be parsed to confirm it is a read-only query. Please contact your administrator for more assistance
This may be triggered by:
Issue 1022 - Database does not allow data manipulation

The <<= operator is described in the PostgreSQL documentation at:

https://www.postgresql.org/docs/current/functions-net.html

Is there an alternative way of presenting this operator in Superset SQLLab?

6
  • It would be great if you could paste the error message as text, not a picture. Did you type that <<= out directly in SQLLab, paste it, or open a script from somewhere? It looks like you're dealing with 3 different fonts and something silly tries to translate the three characters in <<= for a single . As a temp workaround you could change that line to where network_subeq(q.ip_address,subnet) because network_subeq is the function behind the <<= operator, but that will disable index use. Commented Jun 25 at 14:25
  • Or, using some other client that doesn't do this sort of forced mapping/translation, define a wrapper: create function netsubeq(inet,inet)returns boolean return $1<<=$2; and use that instead of direct <<=. Since that wrapper function is inlineable, Postgres will swap it out for the operator under the hood and enable index use. Commented Jun 25 at 14:34
  • Hi @Zegarek, I copy/pasted the SQL from pgadmin4 into the Superset SQLLab query window. I'll try with network_subeq() instead. Commented Jun 25 at 15:04
  • Hi @Zegarek, errors now in the post as text Commented Jun 25 at 15:04
  • Hi @Zegarek, network_subeq() works, so I'll use that in Superset for now and see how I get on. Thanks for your input. Commented Jun 25 at 15:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.