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

Commit20b85b3

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 parent9fbe072 commit20b85b3

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
@@ -3249,13 +3249,12 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
32493249
{
32503250
ListCell*lcell;
32513251
intnestlevel;
3252-
constchar*delim=" ";
32533252
StringInfobuf=context->buf;
3253+
boolgotone= false;
32543254

32553255
/* Make sure any constants in the exprs are printed portably */
32563256
nestlevel=set_transmission_modes();
32573257

3258-
appendStringInfoString(buf," ORDER BY");
32593258
foreach(lcell,pathkeys)
32603259
{
32613260
PathKey*pathkey=lfirst(lcell);
@@ -3288,6 +3287,26 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
32883287

32893288
em_expr=em->em_expr;
32903289

3290+
/*
3291+
* If the member is a Const expression then we needn't add it to the
3292+
* ORDER BY clause. This can happen in UNION ALL queries where the
3293+
* union child targetlist has a Const. Adding these would be
3294+
* wasteful, but also, for INT columns, an integer literal would be
3295+
* seen as an ordinal column position rather than a value to sort by.
3296+
* deparseConst() does have code to handle this, but it seems less
3297+
* effort on all accounts just to skip these for ORDER BY clauses.
3298+
*/
3299+
if (IsA(em_expr,Const))
3300+
continue;
3301+
3302+
if (!gotone)
3303+
{
3304+
appendStringInfoString(buf," ORDER BY ");
3305+
gotone= true;
3306+
}
3307+
else
3308+
appendStringInfoString(buf,", ");
3309+
32913310
/*
32923311
* Lookup the operator corresponding to the strategy in the opclass.
32933312
* The datatype used by the opfamily is not necessarily the same as
@@ -3302,7 +3321,6 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
33023321
pathkey->pk_strategy,em->em_datatype,em->em_datatype,
33033322
pathkey->pk_opfamily);
33043323

3305-
appendStringInfoString(buf,delim);
33063324
deparseExpr(em_expr,context);
33073325

33083326
/*
@@ -3312,7 +3330,6 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
33123330
appendOrderBySuffix(oprid,exprType((Node*)em_expr),
33133331
pathkey->pk_nulls_first,context);
33143332

3315-
delim=", ";
33163333
}
33173334
reset_transmission_modes(nestlevel);
33183335
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

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

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

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

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 26 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -339,12 +339,6 @@ WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
339339
-- bug before 9.3.5 due to sloppy handling of remote-estimate parameters
340340
SELECT*FROM ft1WHERE c1= ANY (ARRAY(SELECT c1FROM ft2WHERE c1<5));
341341
SELECT*FROM ft2WHERE c1= ANY (ARRAY(SELECT c1FROM ft1WHERE c1<5));
342-
-- we should not push order by clause with volatile expressions or unsafe
343-
-- collations
344-
EXPLAIN (VERBOSE, COSTS OFF)
345-
SELECT*FROM ft2ORDER BYft2.c1, random();
346-
EXPLAIN (VERBOSE, COSTS OFF)
347-
SELECT*FROM ft2ORDER BYft2.c1,ft2.c3 collate"C";
348342

349343
-- user-defined operator/function
350344
CREATEFUNCTIONpostgres_fdw_abs(int) RETURNSintAS $$
@@ -407,6 +401,32 @@ WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
407401
SELECT c1, to_tsvector('custom_search'::regconfig, c3)FROM ft1
408402
WHERE c1=642AND length(to_tsvector('custom_search'::regconfig, c3))>0;
409403

404+
-- ===================================================================
405+
-- ORDER BY queries
406+
-- ===================================================================
407+
-- we should not push order by clause with volatile expressions or unsafe
408+
-- collations
409+
EXPLAIN (VERBOSE, COSTS OFF)
410+
SELECT*FROM ft2ORDER BYft2.c1, random();
411+
EXPLAIN (VERBOSE, COSTS OFF)
412+
SELECT*FROM ft2ORDER BYft2.c1,ft2.c3 collate"C";
413+
414+
-- Ensure we don't push ORDER BY expressions which are Consts at the UNION
415+
-- child level to the foreign server.
416+
EXPLAIN (VERBOSE, COSTS OFF)
417+
SELECT*FROM (
418+
SELECT1AS type,c1FROM ft1
419+
UNION ALL
420+
SELECT2AS type,c1FROM ft2
421+
) aORDER BY type,c1;
422+
423+
EXPLAIN (VERBOSE, COSTS OFF)
424+
SELECT*FROM (
425+
SELECT1AS type,c1FROM ft1
426+
UNION ALL
427+
SELECT2AS type,c1FROM ft2
428+
) aORDER BY type;
429+
410430
-- ===================================================================
411431
-- JOIN queries
412432
-- ===================================================================

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp