SQL reliably crashes Server

[Robins]

This was a (very) large SQL that I tried my best to cut down but couldn't beyond what is given below.

This SQL crashes reliably on Postgres v13.3 and PostGIS v3.0.3 (for me). I was going to post this to Postgres hackers list, when I saw that the top end of the backtrace was mostly PostGIS functions and then thought I'd post this here instead (and that the SQL is apparently doing overlaps on geography type).

(Feel free to rename the Issue title, since I couldn't find a better name)

SQL

SELECT 1
FROM (SELECT NULL::int a LIMIT 0) AS ref_0
WHERE CASE 
    WHEN CASE 
        WHEN pg_catalog.jsonb_build_array() ? version()
          THEN cast(NULL AS point)
        ELSE cast(NULL AS point)
        END @(SELECT NULL::lseg LIMIT 0)
      THEN cast(NULL AS GEOGRAPHY)
    END && CASE 
    WHEN (
        CASE 
          WHEN ref_0.a IS NULL
          THEN cast(NULL AS float4)
          ELSE cast(NULL AS float4)
        END <= (
          SELECT NULL::real LIMIT 0
          )
        )
      THEN NULL::GEOGRAPHY
    END 
	limit 58;

Backtrace

#0  detoast_attr_slice (attr=attr@entry=0x0, sliceoffset=sliceoffset@entry=0, slicelength=slicelength@entry=40) at detoast.c:226
#1  0x00000000008a3b55 in pg_detoast_datum_slice (datum=datum@entry=0x0, first=first@entry=0, count=count@entry=40) at fmgr.c:1754
#2  0x000014e217f4f793 in gserialized_datum_get_gidx_p (gsdatum=gsdatum@entry=0, gidx=gidx@entry=0x7fffb1ba7730) at gserialized_gist.c:185
#3  0x000014e217f4faa9 in gserialized_datum_get_gbox_p (gsdatum=0, gbox=gbox@entry=0x7fffb1ba77d0) at gserialized_gist.c:157
#4  0x000014e217ef971b in gserialized_sel_internal (root=<optimized out>, args=<optimized out>, varRelid=<optimized out>, mode=0) at gserialized_estimate.c:2253
#5  0x000014e217ef9799 in gserialized_gist_sel (fcinfo=fcinfo@entry=0x7fffb1ba7850) at gserialized_estimate.c:2281
#6  0x00000000008a24f4 in DirectFunctionCall5Coll (func=func@entry=0x14e217ef9780 <gserialized_gist_sel>, collation=collation@entry=0, arg1=<optimized out>,
    arg2=<optimized out>, arg3=<optimized out>, arg4=<optimized out>, arg5=0) at fmgr.c:908
#7  0x000014e217ef96a4 in gserialized_gist_sel_nd (fcinfo=<optimized out>) at gserialized_estimate.c:2204
#8  0x00000000008a2c6e in FunctionCall4Coll (flinfo=0x7fffb1ba7970, collation=<optimized out>, arg1=<optimized out>, arg2=<optimized out>, arg3=<optimized out>,
    arg4=<optimized out>) at fmgr.c:1216
#9  0x00000000008a3247 in OidFunctionCall4Coll (functionId=<optimized out>, collation=collation@entry=0, arg1=arg1@entry=49189016, arg2=arg2@entry=1118028,
    arg3=arg3@entry=50164232, arg4=arg4@entry=0) at fmgr.c:1452
#10 0x000000000070b45d in restriction_selectivity (root=root@entry=0x2ee9098, operatorid=1118028, args=0x2fd7208, inputcollid=0, varRelid=varRelid@entry=0)
    at plancat.c:1785
.
.
.

$ psql
psql (13beta2, server 13.3)
Type "help" for help.

r=> \dx postgis
                                   List of installed extensions
  Name   | Version | Schema |                             Description
---------+---------+--------+---------------------------------------------------------------------
 postgis | 3.0.3   | public | PostGIS geometry, geography, and raster spatial types and functions
(1 row)

[Robins]

Looking at slicelength=slicelength@entry=40, it seems that PostGIS defaults to fetching 40 bytes from detoast_attr_slice() which is probably unnecessary in this case.

	/*
	** The most info we need is the 8 bytes of serialized header plus the 32 bytes
	** of floats necessary to hold the 8 floats of the largest XYZM index
	** bounding box, so 40 bytes.
	*/
	gpart = (GSERIALIZED*)PG_DETOAST_DATUM_SLICE(gsdatum, 0, 40);

[Robins]

Am running a regression suite and it is (constantly) tripping on this backtrace every few minutes (and effectively making this test suite useless). Noting it here, to mention that this isn't a corner case or one-off.

It seems this crash / backtrace has special affinity to range operators and set comparisons.

Another example:

select 1
from public.spatial_ref_sys as sample_0 tablesample bernoulli (6.8)
where case when ((cast(null as geometry) && cast(null as geometry))
        and (NULL is NULL))
       then case when sample_0.auth_srid is NULL then cast(null as geometry) else cast(null as geometry) end
       else case when sample_0.auth_srid is NULL then cast(null as geometry) else cast(null as geometry) end
       end
     &/& case when (pg_catalog.pg_current_snapshot() is not NULL)
      and (cast(null as timestamptz) >= (select backend_start from pg_catalog.pg_stat_activity limit 1 offset 4)
          ) then public.geometry(
      cast(cast(null as geography) as geography)) else public.geometry(
      cast(cast(null as geography) as geography)) end;
	  

#0  detoast_attr_slice (attr=attr@entry=0x0, sliceoffset=sliceoffset@entry=0, slicelength=slicelength@entry=40) at detoast.c:226
#1  0x00000000008a3b55 in pg_detoast_datum_slice (datum=datum@entry=0x0, first=first@entry=0, count=count@entry=40) at fmgr.c:1754
#2  0x000014e217f4f793 in gserialized_datum_get_gidx_p (gsdatum=gsdatum@entry=0, gidx=gidx@entry=0x7fffb1ba7730) at gserialized_gist.c:185

[Robins]

Comment from Tom Lane confirming that this is not an engine bug.

​https://www.postgresql.org/message-id/3430325.1621435835%40sss.pgh.pa.us

[pramsey]

Milestone renamed

[robe]

Good one, crashes on my PG13 too.

[robe]

adding Tom Lane's comment for completeness.

The trace shows that gserialized_datum_get_gidx_p is passing a NULL
datum pointer to pg_detoast_datum_slice.  Whether the slice length
is appropriate seems like an academic question.


(It does look like that code validates sliceoffset and slicelength
and does something appropriate if they're outside the bounds of
the datum's size.  But you gotta have a datum.)

and for my testing I was testing against 3.1.2dev (just before release against PostgreSQL 13)

For testing with pg14dev, PostGIS 3.2.0dev, the query does not run,

Complains that

 operator does not exist: point @ lseg

Indeed if I run this query in PostgreSQL 14

SELECT cast(NULL AS point) @(SELECT NULL::lseg LIMIT 0)

I get the same error so I guess they took out the @ operator in PostgreSQL 14 for point @ lseg.

[robe]

aha - PostgreSQL 14 release notes says:

Remove deprecated containment operators @ and ~ for built-in geometric data types and contrib modules cube, hstore, intarray, and seg (Justin Pryzby)

Would be nice if we can get a version of this query that doesn't involve the @ operator on geometric data types

[Paul Ramsey <pramsey@…>]

In c84b3f9/git:

Catch crash when query somehow sneaks a NULL query into the selectivity calculator, references #4919

[Paul Ramsey <pramsey@…>]

In bbf341d/git:

Catch crash when query somehow sneaks a NULL query into the selectivity calculator, references #4919

[Paul Ramsey <pramsey@…>]

In bd77ef8/git:

Catch crash when query somehow sneaks a NULL query into the selectivity calculator, references #4919

[pramsey]

Also wonderfully obscure, patched back to 3.0.