postgres_fdw: correct regression test for parameterized scan for foreign table

Started by Etsuro Fujitaalmost 9 years ago1 messages

fujita.etsuro@lab.ntt.co.jp

almost 9 years ago

1 attachment(s)

Hi,

While working on adding support for parameterized foreign joins to
postgres_fdw, I noticed that commit
e4106b2528727c4b48639c0e12bf2f70a766b910 forgot to modify a test query
for execution of a parameterized foreign scan for a foreign table:

--- parameterized remote path
+-- parameterized remote path for foreign table
  EXPLAIN (VERBOSE, COSTS false)
-  SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
+  SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
  SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;

The query in the last line as-is would test to see if the remote join
works correctly, which isn't the right thing to do here. Attached is a
small patch for addressing that.

Best regards,
Etsuro Fujita

Attachments:

postgresql-fdw-regress.patchbinary/octet-stream; name=postgresql-fdw-regress.patchDownload

*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 671,680 **** EXPLAIN (VERBOSE, COSTS OFF)
           Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))
  (8 rows)
  
! SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
!  c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  | c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
! ----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+-------+------------------------------+--------------------------+----+------------+-----
!  47 |  7 | 00047 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7  | 7          | foo |  7 |  7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7  | 7          | foo
  (1 row)
  
  -- check both safe and unsafe join conditions
--- 671,680 ----
           Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))
  (8 rows)
  
! SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
!  C 1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  | c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
! -----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+-------+------------------------------+--------------------------+----+------------+-----
!   47 |  7 | 00047 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7  | 7          | foo |  7 |  7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7  | 7          | foo
  (1 row)
  
  -- check both safe and unsafe join conditions
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 281,287 **** EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo';  -- can't be
  -- parameterized remote path for foreign table
  EXPLAIN (VERBOSE, COSTS OFF)
    SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
! SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
  
  -- check both safe and unsafe join conditions
  EXPLAIN (VERBOSE, COSTS OFF)
--- 281,287 ----
  -- parameterized remote path for foreign table
  EXPLAIN (VERBOSE, COSTS OFF)
    SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
! SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
  
  -- check both safe and unsafe join conditions
  EXPLAIN (VERBOSE, COSTS OFF)