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(dot)michkov(at)arenadata(dot)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).