From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: m.michkov@arenadata.io
Subject: BUG #18613: Incorrect output for foreign tables with NOT NULL constraints
Date: Thu, 12 Sep 2024 07:28:32 +0000
Message-ID: <18613-08a94418126ae1a0@postgresql.org> (raw)
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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, m.michkov@arenadata.io
Subject: Re: BUG #18613: Incorrect output for foreign tables with NOT NULL constraints
In-Reply-To: <18613-08a94418126ae1a0@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox