The following bug has been logged on the website:
Bug reference: 18467
Logged by: Onder Kalacı
Email address: onderkalaci@gmail.com
PostgreSQL version: 16.2
Operating system: MacOs
Description:
Hi, it seems the same query with `LimitOption` on a heap table and on an
postgres_fdw table pointing to the same heap table is returning different
results.
Steps to reproduce:
-- create heap table, and insert 2 rows
CREATE TABLE heap_table (a int);
INSERT INTO heap_table VALUES (1), (1), (1);
-- create a foreign table, pointing to the same heap_table
CREATE FOREIGN TABLE ft1 (
a int
) SERVER loopback OPTIONS (table_name 'heap_table');
-- same query returning different results
SELECT * FROM heap_table ORDER BY 1 FETCH FIRST 2 ROWS WITH TIES ;
a
---
1
1
1
(3 rows)
SELECT * FROM ft1 ORDER BY 1 FETCH FIRST 2 ROWS WITH TIES ;
a
---
1
1
(2 rows)
-- seems like the deparser doesn't properly handle LimitOption
explain (verbose) SELECT * FROM ft1 ORDER BY 1 FETCH FIRST 2 ROWS WITH TIES
;
QUERY PLAN
-----------------------------------------------------------------------------------------
Foreign Scan on public.ft1 (cost=100.00..100.07 rows=2 width=4)
Output: a
Remote SQL: SELECT a FROM public.heap_table ORDER BY a ASC NULLS LAST
LIMIT 2::bigint
(3 rows)
fdw-setup steps I used:
-- setup
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$'
)$$;
END;
$d$;
CREATE USER MAPPING FOR public SERVER testserver1 OPTIONS (user 'value',
password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
Thanks,
Onder
