pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18613: Incorrect output for foreign tables with NOT NULL constraints
3+ messages / 3 participants
[nested] [flat]
* BUG #18613: Incorrect output for foreign tables with NOT NULL constraints
@ 2024-09-12 07:28 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2024-09-12 07:28 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: m.michkov@arenadata.io
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).
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18613: Incorrect output for foreign tables with NOT NULL constraints
@ 2024-09-12 10:56 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: David Rowley @ 2024-09-12 10:56 UTC (permalink / raw)
To: m.michkov@arenadata.io; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18613: Incorrect output for foreign tables with NOT NULL constraints
@ 2024-09-12 13:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2024-09-12 13:54 UTC (permalink / raw)
To: m.michkov@arenadata.io; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-09-12 13:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-12 07:28 BUG #18613: Incorrect output for foreign tables with NOT NULL constraints PG Bug reporting form <noreply@postgresql.org>
2024-09-12 10:56 ` David Rowley <dgrowleyml@gmail.com>
2024-09-12 13:54 ` Tom Lane <tgl@sss.pgh.pa.us>
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