Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commit628c3f2

Browse files
committed
Fix deparsing of Consts in postgres_fdw ORDER BY
For UNION ALL queries where a union child query contained a foreigntable, if the targetlist of that query contained a constant, and thetop-level query performed an ORDER BY which contained the column for theconstant value, then postgres_fdw would find the EquivalenceMember withthe Const and then try to produce an ORDER BY containing that Const.This caused problems with INT typed Consts as these could appear to berequests to order by an ordinal column position rather than the constantvalue. This could lead to either an error such as:ERROR: ORDER BY position <int const> is not in select listor worse, if the constant value is a valid column, then we could justsort by the wrong column altogether.Here we fix this issue by just not including these Consts in the ORDERBY clause.In passing, add a new section for testing ORDER BY in the postgres_fdwtests and move two existing tests which were misplaced in the WHEREclause testing section into it.Reported-by: Michał KłeczekReviewed-by: Ashutosh Bapat, Richard GuoBug: #18381Discussion:https://postgr.es/m/0714C8B8-8D82-4ABB-9F8D-A0C3657E7B6E%40kleczek.orgDiscussion:https://postgr.es/m/18381-137456acd168bf93%40postgresql.orgBackpatch-through: 12, oldest supported version
1 parent473babd commit628c3f2

File tree

3 files changed

+114
-36
lines changed

3 files changed

+114
-36
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 21 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3344,13 +3344,12 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
33443344
{
33453345
ListCell*lcell;
33463346
intnestlevel;
3347-
constchar*delim=" ";
33483347
StringInfobuf=context->buf;
3348+
boolgotone= false;
33493349

33503350
/* Make sure any constants in the exprs are printed portably */
33513351
nestlevel=set_transmission_modes();
33523352

3353-
appendStringInfoString(buf," ORDER BY");
33543353
foreach(lcell,pathkeys)
33553354
{
33563355
PathKey*pathkey=lfirst(lcell);
@@ -3383,6 +3382,26 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
33833382

33843383
em_expr=em->em_expr;
33853384

3385+
/*
3386+
* If the member is a Const expression then we needn't add it to the
3387+
* ORDER BY clause. This can happen in UNION ALL queries where the
3388+
* union child targetlist has a Const. Adding these would be
3389+
* wasteful, but also, for INT columns, an integer literal would be
3390+
* seen as an ordinal column position rather than a value to sort by.
3391+
* deparseConst() does have code to handle this, but it seems less
3392+
* effort on all accounts just to skip these for ORDER BY clauses.
3393+
*/
3394+
if (IsA(em_expr,Const))
3395+
continue;
3396+
3397+
if (!gotone)
3398+
{
3399+
appendStringInfoString(buf," ORDER BY ");
3400+
gotone= true;
3401+
}
3402+
else
3403+
appendStringInfoString(buf,", ");
3404+
33863405
/*
33873406
* Lookup the operator corresponding to the strategy in the opclass.
33883407
* The datatype used by the opfamily is not necessarily the same as
@@ -3397,7 +3416,6 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
33973416
pathkey->pk_strategy,em->em_datatype,em->em_datatype,
33983417
pathkey->pk_opfamily);
33993418

3400-
appendStringInfoString(buf,delim);
34013419
deparseExpr(em_expr,context);
34023420

34033421
/*
@@ -3407,7 +3425,6 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
34073425
appendOrderBySuffix(oprid,exprType((Node*)em_expr),
34083426
pathkey->pk_nulls_first,context);
34093427

3410-
delim=", ";
34113428
}
34123429
reset_transmission_modes(nestlevel);
34133430
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 67 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -887,32 +887,6 @@ SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
887887
4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo
888888
(4 rows)
889889

890-
-- we should not push order by clause with volatile expressions or unsafe
891-
-- collations
892-
EXPLAIN (VERBOSE, COSTS OFF)
893-
SELECT * FROM ft2 ORDER BY ft2.c1, random();
894-
QUERY PLAN
895-
-------------------------------------------------------------------------------
896-
Sort
897-
Output: c1, c2, c3, c4, c5, c6, c7, c8, (random())
898-
Sort Key: ft2.c1, (random())
899-
-> Foreign Scan on public.ft2
900-
Output: c1, c2, c3, c4, c5, c6, c7, c8, random()
901-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
902-
(6 rows)
903-
904-
EXPLAIN (VERBOSE, COSTS OFF)
905-
SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
906-
QUERY PLAN
907-
-------------------------------------------------------------------------------
908-
Sort
909-
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
910-
Sort Key: ft2.c1, ft2.c3 COLLATE "C"
911-
-> Foreign Scan on public.ft2
912-
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
913-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
914-
(6 rows)
915-
916890
-- user-defined operator/function
917891
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
918892
BEGIN
@@ -1087,6 +1061,73 @@ WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
10871061
642 | '00642':1
10881062
(1 row)
10891063

1064+
-- ===================================================================
1065+
-- ORDER BY queries
1066+
-- ===================================================================
1067+
-- we should not push order by clause with volatile expressions or unsafe
1068+
-- collations
1069+
EXPLAIN (VERBOSE, COSTS OFF)
1070+
SELECT * FROM ft2 ORDER BY ft2.c1, random();
1071+
QUERY PLAN
1072+
-------------------------------------------------------------------------------
1073+
Sort
1074+
Output: c1, c2, c3, c4, c5, c6, c7, c8, (random())
1075+
Sort Key: ft2.c1, (random())
1076+
-> Foreign Scan on public.ft2
1077+
Output: c1, c2, c3, c4, c5, c6, c7, c8, random()
1078+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1079+
(6 rows)
1080+
1081+
EXPLAIN (VERBOSE, COSTS OFF)
1082+
SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
1083+
QUERY PLAN
1084+
-------------------------------------------------------------------------------
1085+
Sort
1086+
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
1087+
Sort Key: ft2.c1, ft2.c3 COLLATE "C"
1088+
-> Foreign Scan on public.ft2
1089+
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
1090+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1091+
(6 rows)
1092+
1093+
-- Ensure we don't push ORDER BY expressions which are Consts at the UNION
1094+
-- child level to the foreign server.
1095+
EXPLAIN (VERBOSE, COSTS OFF)
1096+
SELECT * FROM (
1097+
SELECT 1 AS type,c1 FROM ft1
1098+
UNION ALL
1099+
SELECT 2 AS type,c1 FROM ft2
1100+
) a ORDER BY type,c1;
1101+
QUERY PLAN
1102+
---------------------------------------------------------------------------------
1103+
Merge Append
1104+
Sort Key: (1), ft1.c1
1105+
-> Foreign Scan on public.ft1
1106+
Output: 1, ft1.c1
1107+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
1108+
-> Foreign Scan on public.ft2
1109+
Output: 2, ft2.c1
1110+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
1111+
(8 rows)
1112+
1113+
EXPLAIN (VERBOSE, COSTS OFF)
1114+
SELECT * FROM (
1115+
SELECT 1 AS type,c1 FROM ft1
1116+
UNION ALL
1117+
SELECT 2 AS type,c1 FROM ft2
1118+
) a ORDER BY type;
1119+
QUERY PLAN
1120+
---------------------------------------------------
1121+
Merge Append
1122+
Sort Key: (1)
1123+
-> Foreign Scan on public.ft1
1124+
Output: 1, ft1.c1
1125+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
1126+
-> Foreign Scan on public.ft2
1127+
Output: 2, ft2.c1
1128+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
1129+
(8 rows)
1130+
10901131
-- ===================================================================
10911132
-- JOIN queries
10921133
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 26 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -349,12 +349,6 @@ WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
349349
-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters
350350
SELECT*FROM ft1WHERE c1= ANY (ARRAY(SELECT c1FROM ft2WHERE c1<5));
351351
SELECT*FROM ft2WHERE c1= ANY (ARRAY(SELECT c1FROM ft1WHERE c1<5));
352-
-- we should not push order by clause with volatile expressions or unsafe
353-
-- collations
354-
EXPLAIN (VERBOSE, COSTS OFF)
355-
SELECT*FROM ft2ORDER BYft2.c1, random();
356-
EXPLAIN (VERBOSE, COSTS OFF)
357-
SELECT*FROM ft2ORDER BYft2.c1,ft2.c3 collate"C";
358352

359353
-- user-defined operator/function
360354
CREATEFUNCTIONpostgres_fdw_abs(int) RETURNSintAS $$
@@ -417,6 +411,32 @@ WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
417411
SELECT c1, to_tsvector('custom_search'::regconfig, c3)FROM ft1
418412
WHERE c1=642AND length(to_tsvector('custom_search'::regconfig, c3))>0;
419413

414+
-- ===================================================================
415+
-- ORDER BY queries
416+
-- ===================================================================
417+
-- we should not push order by clause with volatile expressions or unsafe
418+
-- collations
419+
EXPLAIN (VERBOSE, COSTS OFF)
420+
SELECT*FROM ft2ORDER BYft2.c1, random();
421+
EXPLAIN (VERBOSE, COSTS OFF)
422+
SELECT*FROM ft2ORDER BYft2.c1,ft2.c3 collate"C";
423+
424+
-- Ensure we don't push ORDER BY expressions which are Consts at the UNION
425+
-- child level to the foreign server.
426+
EXPLAIN (VERBOSE, COSTS OFF)
427+
SELECT*FROM (
428+
SELECT1AS type,c1FROM ft1
429+
UNION ALL
430+
SELECT2AS type,c1FROM ft2
431+
) aORDER BY type,c1;
432+
433+
EXPLAIN (VERBOSE, COSTS OFF)
434+
SELECT*FROM (
435+
SELECT1AS type,c1FROM ft1
436+
UNION ALL
437+
SELECT2AS type,c1FROM ft2
438+
) aORDER BY type;
439+
420440
-- ===================================================================
421441
-- JOIN queries
422442
-- ===================================================================

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp