Thread: BUG #17395: Assert failed on merge join when foreign range partition contains out-of-range value

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.

PG Bug reporting form <noreply@postgresql.org> writes:
> The following query:
> [ foreign partition that doesn't contain the expected data ]
> causes an assertion failure with the following stack:

Hm.  I suppose we have to reduce that assert to an actual runtime
test.  More generally, though, it seems like the documentation is
not nearly clear enough that it's on the user's head to make sure
that a foreign partition's contents meet the partition constraint.
Sec. 5.11.2 punts the matter to the CREATE FOREIGN TABLE man page,
which is okay, but CREATE FOREIGN TABLE doesn't lay it out with
any degree of clarity either.

            regards, tom lane