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).
