ERROR: GDAL AppDefined [1] SQL Expression Parsing Error: syntax error, unexpected IS. Occurred around : ( IS NOT NULL) ^ SQL state: XX000

[robe2]

I have to check this on more common dataset. I was linking a DXF which is a bunch of MULTIPOLYGONZ (essentally 3D triangles).

CREATE SERVER svr_ditch_dxf
   FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (datasource '\Dike\tanks\Tank 21.dxf',format 'DXF');

CREATE SCHEMA staging;
IMPORT FOREIGN SCHEMA ogr_all FROM SERVER svr_ditch_dxf INTO staging;

And this query:

SELECT * FROM staging.entities WHERE geom IS NOT NULL;

triggers the error: ERROR: GDAL AppDefined [1] SQL Expression Parsing Error: syntax error, unexpected IS. Occurred around : ( IS NOT NULL) ^ SQL state: XX000

I'm running with GDAL 3.0.1 against PostgreSQL 12

[pramsey]

Yeah, the error being around 'IS NOT NULL' speaks to maybe (a) a change in the SQL dialect handling in version 3 or (b) just something we never tested before.

[pramsey]

Hm, I'd not expect the format to matter, but when I test a table using geom is not null I don't get an error, it looks like it correctly does not attempt to push that query down... mind you, I'm testing the latest code. Have things gotten better?

[robe2]

Still an issue. I think it might be driver specific though cause when I did it with a SQL Server table it returned no records and didn't error. But if I did with a shape file it returns that error too.

This is with GDAL 2.4.4 and ogrfdw 1.0.9

[robe2]

Seems to be the geom field.

my ESRI Shapefile foreign table looks like this:

CREATE FOREIGN TABLE staging.t2020_06_19_1592575768224(
    fid bigint NULL,
    geom geometry(Point,4326) NULL,
    lognumber bigint NULL,
    date character varying(10) NULL COLLATE pg_catalog."default",
    "time" character varying(8) NULL COLLATE pg_catalog."default",
    utilitytyp character varying(100) NULL COLLATE pg_catalog."default",
    incrementa double precision NULL,
    accumulate double precision NULL,
    current double precision NULL,
    direction character varying(100) NULL COLLATE pg_catalog."default",
    depth double precision NULL,
    frequency integer NULL,
    locateview character varying(100) NULL COLLATE pg_catalog."default",
    bargraphmo character varying(100) NULL COLLATE pg_catalog."default",
    gain double precision NULL,
    bargraph double precision NULL,
    battery double precision NULL,
    "offset" double precision NULL,
    locateerr double precision NULL,
    pitch double precision NULL,
    roll double precision NULL,
    compass double precision NULL,
    gpsvalid character varying(10) NULL COLLATE pg_catalog."default",
    gpstime character varying(19) NULL COLLATE pg_catalog."default",
    fix character varying(100) NULL COLLATE pg_catalog."default",
    sattelites integer NULL,
    pdop double precision NULL,
    hdop double precision NULL,
    vdop double precision NULL,
    altitudems double precision NULL,
    geoidsepar double precision NULL,
    automode character varying(10) NULL COLLATE pg_catalog."default",
    gpsmode character varying(100) NULL COLLATE pg_catalog."default",
    fixdimensi character varying(100) NULL COLLATE pg_catalog."default",
    agediff integer NULL,
    refstation integer NULL,
    sdlat double precision NULL,
    sdlon double precision NULL,
    n2drms double precision OPTIONS (column_name '2DRMS') NULL,
    sdaltitude double precision NULL,
    comments character varying(200) NULL COLLATE pg_catalog."default",
    tiltmerged character varying(30) NULL COLLATE pg_catalog."default"
)
    SERVER svr_shp_atmos
    OPTIONS (layer 'T2020-06-19-1592575768224');

When I put in debug mode

SET client_min_messages = debug2;
SELECT *
    FROM staging.t2020_06_19_1592575768224
    WHERE geom IS NOT NULL;

This is the error:

DEBUG:  ogrReadColumnData matched 1 FID, 1 GEOM, 40 FIELDS out of 42 PGSQL COLUMNS
DEBUG:  OGR SQL: ( IS NOT NULL)
DEBUG:  ogrReadColumnData matched 1 FID, 1 GEOM, 40 FIELDS out of 42 PGSQL COLUMNS

ERROR:  GDAL AppDefined [1] SQL Expression Parsing Error: syntax error, unexpected IS. Occurred around :
( IS NOT NULL)
  ^
SQL state: XX000

Interestingly though, using a different field doesn't yield error. The below works fine

SELECT *
    FROM staging.t2020_06_19_1592575768224
    WHERE date is not null;

outputs this in the logs

DEBUG:  ogrReadColumnData matched 1 FID, 1 GEOM, 40 FIELDS out of 42 PGSQL COLUMNS
DEBUG:  OGR SQL: (Date IS NOT NULL)
DEBUG:  ogrReadColumnData matched 1 FID, 1 GEOM, 40 FIELDS out of 42 PGSQL COLUMNS
DEBUG:  processed 125 rows from OGR

The SQL Server table that didn't have the issue I should add is not really a spatial table, so has a stupid geometry column that is nothing.

but debug of it shows the same stuff as the shapefile but without the error

DEBUG:  ogrReadColumnData matched 1 FID, 1 GEOM, 1 FIELDS out of 3 PGSQL COLUMNS
DEBUG:  OGR SQL: ( IS NOT NULL)
DEBUG:  ogrReadColumnData matched 1 FID, 1 GEOM, 1 FIELDS out of 3 PGSQL COLUMNS
DEBUG:  processed 0 rows from OGR

but doesn't error.

[robe2]

Just tried again with 1.0.12 ogr_fdw build and still the same issue. Weird it happens only when filtering against the geom field. Perhaps some issue with the push down logic.

[robe2]

Note someone provided an example with a wfs service on #189

[pramsey]

Should be fixed at 69b59e9. Note that this doesn't mean "geom IS NULL" actually pushes down, OGR doesn't have a way to express logical operations against geometry. It means those clauses are not pushed down at all.