BUG #17395: Assert failed on merge join when foreign range partition contains out-of-range value - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17395
Logged by:          Alexander Lakhin
Email address:      exclusion@gmail.com
PostgreSQL version: 14.1
Operating system:   Ubuntu 20.04
Description:

The following query:
CREATE EXTENSION postgres_fdw;
DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;

CREATE TABLE fprt2 (a int) PARTITION BY RANGE(a);
CREATE TABLE fprt2_p1 (LIKE fprt2);
CREATE TABLE fprt2_p2 (LIKE fprt2);
INSERT INTO fprt2_p1 VALUES (2);
INSERT INTO fprt2_p2 VALUES (1);

CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (0) TO
(1)
    SERVER loopback OPTIONS (table_name 'fprt2_p1');
CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (1) TO
(2)
    SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate
'true');

SELECT t1.a FROM fprt2 t1 JOIN fprt2 t2 ON (t1.a = t2.a);

causes an assertion failure with the following stack:
Core was generated by `postgres: law regression [local] SELECT
                        '.
Program terminated with signal SIGABRT, Aborted.
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
50      ../sysdeps/unix/sysv/linux/raise.c:  No such file or directory.
(gdb) bt
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007fbae1737859 in __GI_abort () at abort.c:79
#2  0x00005566c7d3e72c in ExceptionalCondition (conditionName=0x5566c7ed0a1a
"compareResult < 0", 
    errorType=0x5566c7ed07f3 "FailedAssertion", fileName=0x5566c7ed095a
"nodeMergejoin.c", lineNumber=898)
    at assert.c:69
#3  0x00005566c794c208 in ExecMergeJoin (pstate=0x5566c850faa0) at
nodeMergejoin.c:898
#4  0x00005566c79041ba in ExecProcNode (node=0x5566c850faa0) at
../../../src/include/executor/executor.h:257
#5  0x00005566c7906dd8 in ExecutePlan (estate=0x5566c850f830,
planstate=0x5566c850faa0, use_parallel_mode=false, 
    operation=CMD_SELECT, sendTuples=true, numberTuples=0,
direction=ForwardScanDirection, dest=0x5566c857a560, 
    execute_once=true) at execMain.c:1551
#6  0x00005566c79048f1 in standard_ExecutorRun (queryDesc=0x5566c8441bb0,
direction=ForwardScanDirection, count=0, 
    execute_once=true) at execMain.c:361
#7  0x00005566c79046dc in ExecutorRun (queryDesc=0x5566c8441bb0,
direction=ForwardScanDirection, count=0, 
    execute_once=true) at execMain.c:305
#8  0x00005566c7b81d38 in PortalRunSelect (portal=0x5566c848f1a0,
forward=true, count=0, dest=0x5566c857a560)
    at pquery.c:921
#9  0x00005566c7b8195c in PortalRun (portal=0x5566c848f1a0,
count=9223372036854775807, isTopLevel=true, run_once=true, 
    dest=0x5566c857a560, altdest=0x5566c857a560, qc=0x7ffc64d269f0) at
pquery.c:765
#10 0x00005566c7b7a846 in exec_simple_query (
    query_string=0x5566c84202a0 "SELECT t1.a FROM fprt2 t1 JOIN fprt2 t2 ON
(t1.a = t2.a);") at postgres.c:1214
#11 0x00005566c7b7f735 in PostgresMain (argc=1, argv=0x7ffc64d26c10,
dbname=0x5566c844c3f8 "regression", 
    username=0x5566c844c3d8 "law") at postgres.c:4486
#12 0x00005566c7aa3e3d in BackendRun (port=0x5566c8443d10) at
postmaster.c:4530
#13 0x00005566c7aa3698 in BackendStartup (port=0x5566c8443d10) at
postmaster.c:4252
#14 0x00005566c7a9f48d in ServerLoop () at postmaster.c:1745
#15 0x00005566c7a9ebea in PostmasterMain (argc=3, argv=0x5566c841a560) at
postmaster.c:1417
#16 0x00005566c798e0f7 in main (argc=3, argv=0x5566c841a560) at main.c:209

Reproduced on REL_12_STABLE..master. On REL_11_STABLE and also when the
server is built without asserts SELECT returns two rows. The offending
commit is 959d00e9d.