Opened 7 months ago

Closed 5 months ago

#28380 closed Bug (fixed)

Wrong handling of NULL values in spatialite geometry comparison functions

Reported by: Fabian Schindler Owned by: nobody
Component: GIS Version: 1.11
Severity: Normal Keywords: spatialite geometry
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

For several geometry comparison functions (e.g: ST_Intersects, ST_Equals, ...) the spatialite documentation states that

The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN corresponding to a function invocation on NULL arguments.

Unfortunately, the -1 value is not handled separately, so (e.g:) intersection filters will include database records where the geometry is NULL.

A fix would be to change the lookup from:

...
WHERE Intersects("modelname"."fieldname", GeomFromText('...',4326));

to

...
WHERE Intersects("modelname"."fieldname", GeomFromText('...',4326)) > 0;

Change History (6)

comment:2 Changed 6 months ago by Simon Charette

Has patch: set
Needs tests: set
Patch needs improvement: set
Triage Stage: UnreviewedAccepted

The patch needs adjustments as some tests are failing and some additional regression tests would be required for the handling of None arguments.

comment:3 Changed 6 months ago by Fabian Schindler

New PR (old one was closed because of wrong base)

comment:4 Changed 6 months ago by Tim Graham

Needs tests: unset
Patch needs improvement: unset

comment:5 Changed 6 months ago by Tim Graham

Patch needs improvement: set

comment:6 Changed 5 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: newclosed

In da0fb5b1:

Fixed #28380 -- Excluded null geometries in SpatiaLite geometry lookups.

Note: See TracTickets for help on using tickets.
Back to Top