Thread: BUG #18613: Incorrect output for foreign tables with NOT NULL constraints
BUG #18613: Incorrect output for foreign tables with NOT NULL constraints
From
PG Bug reporting form
Date:
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).
Re: BUG #18613: Incorrect output for foreign tables with NOT NULL constraints
From
David Rowley
Date:
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