Description:
ST_PointN(ls, N), ST_InteriorRingN(poly, N) and ST_GeometryN(gc, N) return NULL
when N is 0 or larger than the number of elements. When N is 2^32 or more (or a
negative number whose low 32 bits are a valid index) they return an element
instead, because N is taken modulo 2^32:
SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 3)); -- NULL
SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 4294967297)); -- POINT(0 0)
SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 4294967298)); -- POINT(2 2)
SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), -4294967295)); -- POINT(0 0)
SELECT ST_AsText(ST_InteriorRingN(ST_GeomFromText(
'POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))'), 2)); -- NULL
SELECT ST_AsText(ST_InteriorRingN(ST_GeomFromText(
'POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))'), 4294967297)); -- LINESTRING(4 4,4 6,6 6,6 4,4 4)
SELECT ST_AsText(ST_GeometryN(ST_GeomFromText('GEOMETRYCOLLECTION(POINT(0 0))'), 2)); -- NULL
SELECT ST_AsText(ST_GeometryN(ST_GeomFromText('GEOMETRYCOLLECTION(POINT(0 0))'), 4294967297)); -- POINT(0 0)
No warning. The manual: "ST_PointN(ls, N) Returns the N-th Point in the
Linestring value ls. Points are numbered beginning with 1." MariaDB has the
same defect (reported to MariaDB separately).
Where (trunk 3b99be40): sql/item_geofunc.cc, Item_func_spatial_decomp_n::val_str(),
line 4215:
const long n = (long)args[1]->val_int();
n is passed to Geometry::point_n() / geometry_n() / interior_ring_n(), which take
a uint32 (sql/spatial.h lines 542-550), so the upper 32 bits are dropped.
How to repeat:
SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 3)),
ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 4294967297));
SELECT ST_AsText(ST_GeometryN(ST_GeomFromText('GEOMETRYCOLLECTION(POINT(0 0))'), 4294967297));
-- expected: NULL, NULL / NULL
-- actual: NULL, POINT(0 0) / POINT(0 0)
Suggested fix:
In Item_func_spatial_decomp_n::val_str(), return NULL when the longlong N is
< 1 or > UINT_MAX32 (taking unsigned_flag into account) before calling
point_n() / geometry_n() / interior_ring_n().
Description: ST_PointN(ls, N), ST_InteriorRingN(poly, N) and ST_GeometryN(gc, N) return NULL when N is 0 or larger than the number of elements. When N is 2^32 or more (or a negative number whose low 32 bits are a valid index) they return an element instead, because N is taken modulo 2^32: SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 3)); -- NULL SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 4294967297)); -- POINT(0 0) SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 4294967298)); -- POINT(2 2) SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), -4294967295)); -- POINT(0 0) SELECT ST_AsText(ST_InteriorRingN(ST_GeomFromText( 'POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))'), 2)); -- NULL SELECT ST_AsText(ST_InteriorRingN(ST_GeomFromText( 'POLYGON((0 0,0 10,10 10,10 0,0 0),(4 4,4 6,6 6,6 4,4 4))'), 4294967297)); -- LINESTRING(4 4,4 6,6 6,6 4,4 4) SELECT ST_AsText(ST_GeometryN(ST_GeomFromText('GEOMETRYCOLLECTION(POINT(0 0))'), 2)); -- NULL SELECT ST_AsText(ST_GeometryN(ST_GeomFromText('GEOMETRYCOLLECTION(POINT(0 0))'), 4294967297)); -- POINT(0 0) No warning. The manual: "ST_PointN(ls, N) Returns the N-th Point in the Linestring value ls. Points are numbered beginning with 1." MariaDB has the same defect (reported to MariaDB separately). Where (trunk 3b99be40): sql/item_geofunc.cc, Item_func_spatial_decomp_n::val_str(), line 4215: const long n = (long)args[1]->val_int(); n is passed to Geometry::point_n() / geometry_n() / interior_ring_n(), which take a uint32 (sql/spatial.h lines 542-550), so the upper 32 bits are dropped. How to repeat: SELECT ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 3)), ST_AsText(ST_PointN(ST_GeomFromText('LINESTRING(0 0,2 2)'), 4294967297)); SELECT ST_AsText(ST_GeometryN(ST_GeomFromText('GEOMETRYCOLLECTION(POINT(0 0))'), 4294967297)); -- expected: NULL, NULL / NULL -- actual: NULL, POINT(0 0) / POINT(0 0) Suggested fix: In Item_func_spatial_decomp_n::val_str(), return NULL when the longlong N is < 1 or > UINT_MAX32 (taking unsigned_flag into account) before calling point_n() / geometry_n() / interior_ring_n().