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?
<<=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 towhere network_subeq(q.ip_address,subnet)becausenetwork_subeqis the function behind the<<=operator, but that will disable index use.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.network_subeq()works, so I'll use that in Superset for now and see how I get on. Thanks for your input.