Thread: BUG #18613: Incorrect output for foreign tables with NOT NULL constraints

The following bug has been logged on the website:

Bug reference:      18613
Logged by:          Maxim Michkov
Email address:      m.michkov@arenadata.io
PostgreSQL version: 17rc1
Operating system:   Ubuntu 22.04
Description:

If foreign table data contains nulls while the table definition specifies a
NOT NULL constraint, some queries return wrong outputs. For example, for
file /data.csv with the following contents:

1,10
2,20
3,null
4,40

The following queries returns incorrect result:

CREATE EXTENSION file_fdw;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE t(a int, b int not null) SERVER file_server OPTIONS (
filename '/data.csv', format 'csv', null 'null' );
SELECT * FROM t WHERE b IS NOT NULL;
SELECT * FROM t WHERE b IS NULL;

Outputs:

postgres=# SELECT * FROM t WHERE b IS NOT NULL;
 a | b  
---+----
 1 | 10
 2 | 20
 3 |   
 4 | 40
(4 rows)

postgres=# SELECT * FROM t WHERE b IS NULL;
 a | b 
---+---
(0 rows)

Expected an error (stating that the foreign table contains NULLs) or the
following output:

postgres=# SELECT * FROM t WHERE b IS NOT NULL;
 a | b  
---+----
 1 | 10
 2 | 20
 4 | 40
(3 rows)

postgres=# SELECT * FROM t WHERE b IS NULL;
 a | b 
---+---
 3 |  
(1 row)

The bug appeared after this patch:
https://www.postgresql.org/message-id/E1rS92d-002gbO-EJ%40gemulon.postgresql.org
The patch added optimizations for IS [NOT] NULL checks for columns with NOT
NULL, however it is possible for foreign tables to contain nulls even in
that case. A possible solutions could be reverting that patch, or adding a
special case for foreign tables, or ignoring NOT NULL constraints on foreign
tables in general (because we cannot check they are valid).

On Thu, 12 Sept 2024 at 21:21, PG Bug reporting form
<noreply@postgresql.org> wrote:
> CREATE EXTENSION file_fdw;
> CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
> CREATE FOREIGN TABLE t(a int, b int not null) SERVER file_server OPTIONS (
> filename '/data.csv', format 'csv', null 'null' );
> SELECT * FROM t WHERE b IS NOT NULL;
> SELECT * FROM t WHERE b IS NULL;
>
> Outputs:
>
> postgres=# SELECT * FROM t WHERE b IS NOT NULL;
>  a | b
> ---+----
>  1 | 10
>  2 | 20
>  3 |
>  4 | 40

hmm, it's pretty simple to fix, just;

@@ -2653,7 +2653,8 @@ add_base_clause_to_rel(PlannerInfo *root, Index relid,
         * qual which is either of these for a partitioned table must
also be that
         * for all of its child partitions.
         */
-       if (!rte->inh || rte->relkind == RELKIND_PARTITIONED_TABLE)
+       if ((!rte->inh || rte->relkind == RELKIND_PARTITIONED_TABLE) &&
+               rte->relkind != RELKIND_FOREIGN_TABLE)


but...

CREATE FOREIGN TABLE t(a int check(a >100), b int not null) SERVER
file_server OPTIONS (filename '/data.csv', format 'csv', null 'null'
);
SET constraint_exclusion=on;
explain select * from t where a< 100;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)

it doesn't seem much different from that. Maybe the bug is in your
foreign table definition. The documents [1] seem to agree:

"Constraints on foreign tables (such as CHECK or NOT NULL clauses) are
not enforced by the core PostgreSQL system, and most foreign data
wrappers do not attempt to enforce them either; that is, the
constraint is simply assumed to hold true. There would be little point
in such enforcement since it would only apply to rows inserted or
updated via the foreign table, and not to rows modified by other
means, such as directly on the remote server. Instead, a constraint
attached to a foreign table should represent a constraint that is
being enforced by the remote server."

David

[1] https://www.postgresql.org/docs/devel/sql-createforeigntable.html

PG Bug reporting form <noreply@postgresql.org> writes:
> If foreign table data contains nulls while the table definition specifies a
> NOT NULL constraint, some queries return wrong outputs.

How is that not user error?

            regards, tom lane