BUG #18467: postgres_fdw (deparser) ignores LimitOption - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #18467: postgres_fdw (deparser) ignores LimitOption
Date
Msg-id 18467-7bb89084ff03a08d@postgresql.org
Whole thread Raw
Responses Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption
List pgsql-bugs
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


pgsql-bugs by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: BUG #18362: unaccent rules and Old Greek text
Next
From: Etsuro Fujita
Date:
Subject: Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption