diff options
Diffstat (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql')
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 32 |
1 files changed, 26 insertions, 6 deletions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 80aa7fb..fdbbb4f 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -354,12 +354,6 @@ WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7); -- bug before 9.3.5 due to sloppy handling of remote-estimate parameters SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5)); SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5)); --- 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(); -EXPLAIN (VERBOSE, COSTS OFF) - SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C"; -- user-defined operator/function CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$ @@ -452,6 +446,32 @@ SELECT c1, to_tsvector('custom_search'::regconfig, c3) FROM ft1 WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0; -- =================================================================== +-- 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(); +EXPLAIN (VERBOSE, COSTS OFF) + SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C"; + +-- 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; + +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; + +-- =================================================================== -- JOIN queries -- =================================================================== -- Analyze ft4 and ft5 so that we have better statistics. These tables do not |