Re: BUG #18764: server closed the connection unexpectedly

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> CREATE TABLE t0 (c1 INT, c2 DECIMAL);
> INSERT INTO t0 VALUES (0, NULL);
> INSERT INTO t0 VALUES (8, NULL);
> SELECT c2 AS ca2, c2 AS ca3 FROM t0 UNION SELECT DISTINCT ca8 AS ca5, ca7 AS
> ca6 FROM (SELECT c1 AS ca7, c2 AS ca8 FROM t0) AS ta1 JOIN (SELECT c1 AS
> ca10, c1 AS ca11 FROM t0) AS ta2 ON TRUE; -- server closed the connection
> unexpectedly

Thanks for the report! It's crashing here:

#0 pg_detoast_datum (datum=0x0) at fmgr.c:1834
#1 0x0000000000951603 in DatumGetNumeric (X=0)
at ../../../../src/include/postgres.h:314
#2 numeric_fast_cmp (x=0, y=8, ssup=<optimized out>) at numeric.c:2301
#3 0x0000000000a19019 in ApplySortComparator (datum1=0, isNull1=false,
datum2=8, isNull2=false, ssup=0x1d92f60)
at ../../../../src/include/utils/sortsupport.h:224
#4 0x0000000000a1acc5 in comparetup_heap_tiebreak (a=0x1d98a78, b=0x1d98a90,
state=0x1d92b10) at tuplesortvariants.c:1133
#5 0x0000000000a1ab1b in comparetup_heap (a=0x1d98a78, b=0x1d98a90,
state=0x1d92b10) at tuplesortvariants.c:1086
#6 0x0000000000a133e7 in qsort_tuple (data=0x1d98a78, n=2,
compare=0xa1aa99 <comparetup_heap>, arg=0x1d92b10)
at ../../../../src/include/lib/sort_template.h:316
#7 0x0000000000a17cfe in tuplesort_sort_memtuples (state=0x1d92b10)
at tuplesort.c:2721
#8 0x0000000000a15529 in tuplesort_performsort (state=0x1d92b10)
at tuplesort.c:1382
#9 0x0000000000722b13 in ExecSort (pstate=0x1d61db0) at nodeSort.c:160
#10 0x00000000006f717f in ExecScanFetch (
recheckMtd=0x724f30 <SubqueryRecheck>, accessMtd=0x724f40 <SubqueryNext>,
node=0x1d61c10) at execScan.c:131

I think what is happening is that the executor thinks the sort column
is numeric but it's actually integer. If you EXPLAIN VERBOSE the
query you see

...
-> Subquery Scan on "*SELECT* 2"
Output: "*SELECT* 2".ca5, "*SELECT* 2".ca6
-> Sort
Output: t0_1.c2, t0_1.c1
Sort Key: t0_1.c2, t0_1.c1 USING <
-> HashAggregate
Output: t0_1.c2, t0_1.c1
...

That "USING <" annotation is unexpected. I'm pretty sure it's
there because the sort operator doesn't match the default btree
opclass of the column type, which would be the case if we were
trying to use numeric_lt to sort the integer "c1" column.

git bisect'ing shows that the failure started here:

66c0185a3d14bbbf51d0fc9d267093ffec735231 is the first bad commit
commit 66c0185a3d14bbbf51d0fc9d267093ffec735231
Author: David Rowley <drowley(at)postgresql(dot)org>
Date: Mon Mar 25 14:31:14 2024 +1300

Allow planner to use Merge Append to efficiently implement UNION

But it may be that this is a pre-existing problem that just happened
to be exposed by the change in plan shape following that commit.
Needs more digging.

regards, tom lane