`filter(INTERSECTS(...))` produces malformed CQL

[achubaty]

filter(INTERSECTS(...)) produces malformed CQL — extra parens + TRUE AS "drop_null" artifacts cause WFS 400 on openmaps.gov.bc.ca

Summary

bcdata 0.5.2 generates CQL that GeoServer rejects with HTTP 400 / <ows:ExceptionReport exceptionCode="NoApplicableCode">, surfacing as the generic "There was an issue sending this WFS request" message from catch_wfs_error(). Affects any WFS query built via bcdc_query_geodata() |> filter(...).

Two distinct problems in the generated CQL:

1. Extra outer parentheses. A single-clause filter produces:

CQL_FILTER=((INTERSECTS(GEOMETRY, POLYGON ((...))))

instead of:

CQL_FILTER=INTERSECTS(GEOMETRY, POLYGON ((...)))

The server's CQL parser rejects the wrapped form.

2. TRUE AS "drop_null" SQL artifacts. Multi-clause filters leak a SQL-side TRUE AS "drop_null" placeholder into the CQL (CQL has no AS aliasing — that's SQL):

CQL_FILTER=((INTERSECTS(...)) AND TRUE AS "drop_null" AND ("BGC_LABEL" != 'ZZZ') AND TRUE AS "drop_null")

Minimal reproducer

library(bcdata); library(sf); library(dplyr)

## Any sf polygon over BC works; using a small bbox here for brevity.
geom <- st_as_sfc(st_bbox(c(xmin = 800000, ymin = 1200000, xmax = 850000, ymax = 1250000),
                          crs = st_crs(3005)))

## (1) Single filter clause -- fails because of extra outer parens
bcdc_query_geodata("f358a53b-ffde-4830-a325-a5a03ff672c3") |>
  filter(INTERSECTS(geom)) |>
  collect()
##> Error: There was an issue sending this WFS request

## (2) Same query via direct httr POST (no extra parens) returns 200:
httr::POST("https://openmaps.gov.bc.ca/geo/pub/wfs",
  body = list(
    SERVICE = "WFS", VERSION = "2.0.0", REQUEST = "GetFeature",
    outputFormat = "application/json",
    typeNames = "WHSE_FOREST_VEGETATION.BEC_BIOGEOCLIMATIC_POLY",
    SRSNAME = "EPSG:3005",
    CQL_FILTER = sprintf("INTERSECTS(GEOMETRY, %s)", st_as_text(geom[[1]]))
  ),
  encode = "form"
) |> httr::status_code()
##> [1] 200

Server response (decoded)

catch_wfs_error() short-circuits on status ≥ 300 before parsing the response body, hiding the underlying diagnostic. Tracing bcdata:::bcdc_number_wfs_records() exposes the actual exception:

<?xml version="1.0" encoding="UTF-8"?>
<ows:ExceptionReport ... version="2.0.0">
  <ows:Exception exceptionCode="NoApplicableCode">
    <ows:ExceptionText></ows:ExceptionText>
  </ows:Exception>
</ows:ExceptionReport>

The <ExceptionText> is empty, but the server's pre-exception log includes a Parsing : ((INTERSECTS(... line that pinpoints the outer wrapping as the trigger.

Suggested fix directions

• Drop the outermost (...) wrap when there's only one CQL clause (or unconditionally — single-clause expressions don't need it).
• In the dplyr → CQL translator, strip columns of the form TRUE AS "drop_null" before serialising. These look like dbplyr's "guarantee non-null projection" placeholders that aren't valid CQL.

Suggested UX improvement

catch_wfs_error() could surface the response body (or at least an <ows:ExceptionText> extract) in its error message — the current generic "There was an issue sending this WFS request" made the actual server diagnostic impossible to recover without trace()-ing internals.

Environment

bcdata 0.5.2  (CRAN, current)
R 4.6.0
Ubuntu 24.04 noble / 25.04 resolute
WFS endpoint: https://openmaps.gov.bc.ca/geo/pub/wfs  (GeoServer-based)

[boshek]

Hey @achubaty thanks for reporting. Can you also provide us your package versions a la sessionInfo?

[achubaty]

> packageVersion("bcdata")
[1] ‘0.5.2’
> sessionInfo()
R version 4.6.0 (2026-04-24)
Platform: x86_64-pc-linux-gnu
Running under: Ubuntu 26.04 LTS

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3 
LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/libopenblasp-r0.3.32.so;  LAPACK version 3.12.0

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8   
 [6] LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

time zone: America/Edmonton
tzcode source: system (glibc)

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] geotargets_0.3.1 targets_1.12.0   sf_1.1-1         dplyr_1.2.1     

loaded via a namespace (and not attached):
 [1] generics_0.1.4     renv_1.2.3         class_7.3-23       KernSmooth_2.23-26 httpcode_0.3.0     bcdata_0.5.2       hms_1.1.4         
 [8] magrittr_2.0.5     evaluate_1.0.5     grid_4.6.0         cellranger_1.1.0   jsonlite_2.0.0     processx_3.9.0     e1071_1.7-17      
[15] backports_1.5.1    secretbase_1.3.0   DBI_1.3.0          crul_1.6.0         ps_1.9.3           urltools_1.7.3.1   purrr_1.2.2       
[22] codetools_0.2-20   cli_3.6.6          rlang_1.2.0        triebeard_0.4.1    dbplyr_2.6.0       units_1.0-1        yaml_2.3.12       
[29] otel_0.2.0         tools_4.6.0        tzdb_0.5.0         base64url_1.4      curl_7.1.0         vctrs_0.7.3        R6_2.6.1          
[36] proxy_0.4-29       lifecycle_1.0.5    classInt_0.4-11    pkgconfig_2.0.3    callr_3.8.0        pillar_1.11.1      data.table_1.18.4 
[43] glue_1.8.1         Rcpp_1.1.1-1.1     xfun_0.59          tibble_3.3.1       tidyselect_1.2.1   knitr_1.51         igraph_2.3.2      
[50] readr_2.2.0        compiler_4.6.0     prettyunits_1.2.0  readxl_1.5.0

[boshek]

Ah ok. I cannot reproduce with dbplyr version 2.5.2 so this much be related to version 2.6.0. And then here is the query I get:

bcdc_query_geodata("f358a53b-ffde-4830-a325-a5a03ff672c3") |>
   filter(INTERSECTS(geom)) |> 
   show_query()
<url>
<body>
SERVICE: WFS VERSION: 2.0.0 REQUEST: GetFeature outputFormat:
 application/json typeNames:
 WHSE_FOREST_VEGETATION.BEC_BIOGEOCLIMATIC_POLY SRSNAME: EPSG:3005
 CQL_FILTER: (INTERSECTS(GEOMETRY, POLYGON ((8e+05 1200000, 850000
 1200000, 850000 1250000, 8e+05 1250000, 8e+05 1200000))))

<full query url>
https://openmaps.gov.bc.ca/geo/pub/wfs?SERVICE=WFS&VERSION=2.0.0&REQUEST=GetFeature&outputFormat=application%2Fjson&typeNames=WHSE_FOREST_VEGETATION.BEC_BIOGEOCLIMATIC_POLY&SRSNAME=EPSG%3A3005&CQL_FILTER=%28INTERSECTS%28GEOMETRY%2C%20POLYGON%20%28%288e%2B05%201200000%2C%20850000%201200000%2C%20850000%201250000%2C%208e%2B05%201250000%2C%208e%2B05%201200000%29%29%29%29

@achubaty I'd be curious what you get with dbplyr 2.6.0

[achubaty]

> library(bcdata); library(sf); library(dplyr)
+ geom <- st_as_sfc(st_bbox(c(xmin = 800000, ymin = 1200000, xmax = 850000, ymax = 1250000),
+                           crs = st_crs(3005)))
+ bcdc_query_geodata("f358a53b-ffde-4830-a325-a5a03ff672c3") |>
+    filter(INTERSECTS(geom)) |> 
+    show_query()

Attaching package: ‘bcdata’

The following object is masked from ‘package:stats’:

    filter

<url>
<body>
SERVICE: WFS VERSION: 2.0.0 REQUEST: GetFeature outputFormat: application/json typeNames:
 WHSE_FOREST_VEGETATION.BEC_BIOGEOCLIMATIC_POLY SRSNAME: EPSG:3005 CQL_FILTER: ((INTERSECTS(GEOMETRY, POLYGON ((8e+05 1200000,
 850000 1200000, 850000 1250000, 8e+05 1250000, 8e+05 1200000)))) AND TRUE AS "drop_null")

<full query url>
https://openmaps.gov.bc.ca/geo/pub/wfs?SERVICE=WFS&VERSION=2.0.0&REQUEST=GetFeature&outputFormat=application%2Fjson&typeNames=WHSE_FOREST_VEGETATION.BEC_BIOGEOCLIMATIC_POLY&SRSNAME=EPSG%3A3005&CQL_FILTER=%28INTERSECTS%28GEOMETRY%2C%20POLYGON%20%28%288e%2B05%201200000%2C%20850000%201200000%2C%20850000%201250000%2C%208e%2B05%201250000%2C%208e%2B05%201200000%29%29%29%29

[boshek]

@achubaty are you able to try this dev version and see if this fixed your problem?

pak::pak("bcgov/bcdata@fix/cql-translation")

[achubaty]

it works! thank you for the quick fix 🎉