diff options
Diffstat (limited to 'contrib/postgres_fdw/expected')
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 93 |
1 files changed, 67 insertions, 26 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 08fab73..87c6343 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -893,32 +893,6 @@ SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5)); 4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo (4 rows) --- we should not push order by clause with volatile expressions or unsafe --- collations -EXPLAIN (VERBOSE, COSTS OFF) - SELECT * FROM ft2 ORDER BY ft2.c1, random(); - QUERY PLAN -------------------------------------------------------------------------------- - Sort - Output: c1, c2, c3, c4, c5, c6, c7, c8, (random()) - Sort Key: ft2.c1, (random()) - -> Foreign Scan on public.ft2 - Output: c1, c2, c3, c4, c5, c6, c7, c8, random() - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" -(6 rows) - -EXPLAIN (VERBOSE, COSTS OFF) - SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C"; - QUERY PLAN -------------------------------------------------------------------------------- - Sort - Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text) - Sort Key: ft2.c1, ft2.c3 COLLATE "C" - -> Foreign Scan on public.ft2 - Output: c1, c2, c3, c4, c5, c6, c7, c8, c3 - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" -(6 rows) - -- user-defined operator/function CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$ BEGIN @@ -1207,6 +1181,73 @@ WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0; (1 row) -- =================================================================== +-- ORDER BY queries +-- =================================================================== +-- we should not push order by clause with volatile expressions or unsafe +-- collations +EXPLAIN (VERBOSE, COSTS OFF) + SELECT * FROM ft2 ORDER BY ft2.c1, random(); + QUERY PLAN +------------------------------------------------------------------------------- + Sort + Output: c1, c2, c3, c4, c5, c6, c7, c8, (random()) + Sort Key: ft2.c1, (random()) + -> Foreign Scan on public.ft2 + Output: c1, c2, c3, c4, c5, c6, c7, c8, random() + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" +(6 rows) + +EXPLAIN (VERBOSE, COSTS OFF) + SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C"; + QUERY PLAN +------------------------------------------------------------------------------- + Sort + Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text) + Sort Key: ft2.c1, ft2.c3 COLLATE "C" + -> Foreign Scan on public.ft2 + Output: c1, c2, c3, c4, c5, c6, c7, c8, c3 + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" +(6 rows) + +-- Ensure we don't push ORDER BY expressions which are Consts at the UNION +-- child level to the foreign server. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM ( + SELECT 1 AS type,c1 FROM ft1 + UNION ALL + SELECT 2 AS type,c1 FROM ft2 +) a ORDER BY type,c1; + QUERY PLAN +--------------------------------------------------------------------------------- + Merge Append + Sort Key: (1), ft1.c1 + -> Foreign Scan on public.ft1 + Output: 1, ft1.c1 + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST + -> Foreign Scan on public.ft2 + Output: 2, ft2.c1 + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST +(8 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM ( + SELECT 1 AS type,c1 FROM ft1 + UNION ALL + SELECT 2 AS type,c1 FROM ft2 +) a ORDER BY type; + QUERY PLAN +--------------------------------------------------- + Merge Append + Sort Key: (1) + -> Foreign Scan on public.ft1 + Output: 1, ft1.c1 + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" + -> Foreign Scan on public.ft2 + Output: 2, ft2.c1 + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" +(8 rows) + +-- =================================================================== -- JOIN queries -- =================================================================== -- Analyze ft4 and ft5 so that we have better statistics. These tables do not |