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

Commitc399248

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 parent7b8e2ae commitc399248

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
@@ -3912,13 +3912,12 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
39123912
{
39133913
ListCell*lcell;
39143914
intnestlevel;
3915-
constchar*delim=" ";
39163915
StringInfobuf=context->buf;
3916+
boolgotone= false;
39173917

39183918
/* Make sure any constants in the exprs are printed portably */
39193919
nestlevel=set_transmission_modes();
39203920

3921-
appendStringInfoString(buf," ORDER BY");
39223921
foreach(lcell,pathkeys)
39233922
{
39243923
PathKey*pathkey=lfirst(lcell);
@@ -3951,6 +3950,26 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
39513950

39523951
em_expr=em->em_expr;
39533952

3953+
/*
3954+
* If the member is a Const expression then we needn't add it to the
3955+
* ORDER BY clause. This can happen in UNION ALL queries where the
3956+
* union child targetlist has a Const. Adding these would be
3957+
* wasteful, but also, for INT columns, an integer literal would be
3958+
* seen as an ordinal column position rather than a value to sort by.
3959+
* deparseConst() does have code to handle this, but it seems less
3960+
* effort on all accounts just to skip these for ORDER BY clauses.
3961+
*/
3962+
if (IsA(em_expr,Const))
3963+
continue;
3964+
3965+
if (!gotone)
3966+
{
3967+
appendStringInfoString(buf," ORDER BY ");
3968+
gotone= true;
3969+
}
3970+
else
3971+
appendStringInfoString(buf,", ");
3972+
39543973
/*
39553974
* Lookup the operator corresponding to the strategy in the opclass.
39563975
* The datatype used by the opfamily is not necessarily the same as
@@ -3965,7 +3984,6 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
39653984
pathkey->pk_strategy,em->em_datatype,em->em_datatype,
39663985
pathkey->pk_opfamily);
39673986

3968-
appendStringInfoString(buf,delim);
39693987
deparseExpr(em_expr,context);
39703988

39713989
/*
@@ -3975,7 +3993,6 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
39753993
appendOrderBySuffix(oprid,exprType((Node*)em_expr),
39763994
pathkey->pk_nulls_first,context);
39773995

3978-
delim=", ";
39793996
}
39803997
reset_transmission_modes(nestlevel);
39813998
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

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

896-
-- we should not push order by clause with volatile expressions or unsafe
897-
-- collations
898-
EXPLAIN (VERBOSE, COSTS OFF)
899-
SELECT * FROM ft2 ORDER BY ft2.c1, random();
900-
QUERY PLAN
901-
-------------------------------------------------------------------------------
902-
Sort
903-
Output: c1, c2, c3, c4, c5, c6, c7, c8, (random())
904-
Sort Key: ft2.c1, (random())
905-
-> Foreign Scan on public.ft2
906-
Output: c1, c2, c3, c4, c5, c6, c7, c8, random()
907-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
908-
(6 rows)
909-
910-
EXPLAIN (VERBOSE, COSTS OFF)
911-
SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
912-
QUERY PLAN
913-
-------------------------------------------------------------------------------
914-
Sort
915-
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
916-
Sort Key: ft2.c1, ft2.c3 COLLATE "C"
917-
-> Foreign Scan on public.ft2
918-
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
919-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
920-
(6 rows)
921-
922896
-- user-defined operator/function
923897
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
924898
BEGIN
@@ -1206,6 +1180,73 @@ WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
12061180
642 | '00642':1
12071181
(1 row)
12081182

1183+
-- ===================================================================
1184+
-- ORDER BY queries
1185+
-- ===================================================================
1186+
-- we should not push order by clause with volatile expressions or unsafe
1187+
-- collations
1188+
EXPLAIN (VERBOSE, COSTS OFF)
1189+
SELECT * FROM ft2 ORDER BY ft2.c1, random();
1190+
QUERY PLAN
1191+
-------------------------------------------------------------------------------
1192+
Sort
1193+
Output: c1, c2, c3, c4, c5, c6, c7, c8, (random())
1194+
Sort Key: ft2.c1, (random())
1195+
-> Foreign Scan on public.ft2
1196+
Output: c1, c2, c3, c4, c5, c6, c7, c8, random()
1197+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1198+
(6 rows)
1199+
1200+
EXPLAIN (VERBOSE, COSTS OFF)
1201+
SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
1202+
QUERY PLAN
1203+
-------------------------------------------------------------------------------
1204+
Sort
1205+
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
1206+
Sort Key: ft2.c1, ft2.c3 COLLATE "C"
1207+
-> Foreign Scan on public.ft2
1208+
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
1209+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1210+
(6 rows)
1211+
1212+
-- Ensure we don't push ORDER BY expressions which are Consts at the UNION
1213+
-- child level to the foreign server.
1214+
EXPLAIN (VERBOSE, COSTS OFF)
1215+
SELECT * FROM (
1216+
SELECT 1 AS type,c1 FROM ft1
1217+
UNION ALL
1218+
SELECT 2 AS type,c1 FROM ft2
1219+
) a ORDER BY type,c1;
1220+
QUERY PLAN
1221+
---------------------------------------------------------------------------------
1222+
Merge Append
1223+
Sort Key: (1), ft1.c1
1224+
-> Foreign Scan on public.ft1
1225+
Output: 1, ft1.c1
1226+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
1227+
-> Foreign Scan on public.ft2
1228+
Output: 2, ft2.c1
1229+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
1230+
(8 rows)
1231+
1232+
EXPLAIN (VERBOSE, COSTS OFF)
1233+
SELECT * FROM (
1234+
SELECT 1 AS type,c1 FROM ft1
1235+
UNION ALL
1236+
SELECT 2 AS type,c1 FROM ft2
1237+
) a ORDER BY type;
1238+
QUERY PLAN
1239+
---------------------------------------------------
1240+
Merge Append
1241+
Sort Key: (1)
1242+
-> Foreign Scan on public.ft1
1243+
Output: 1, ft1.c1
1244+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
1245+
-> Foreign Scan on public.ft2
1246+
Output: 2, ft2.c1
1247+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
1248+
(8 rows)
1249+
12091250
-- ===================================================================
12101251
-- JOIN queries
12111252
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

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

365359
-- user-defined operator/function
366360
CREATEFUNCTIONpostgres_fdw_abs(int) RETURNSintAS $$
@@ -462,6 +456,32 @@ WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
462456
SELECT c1, to_tsvector('custom_search'::regconfig, c3)FROM ft1
463457
WHERE c1=642AND length(to_tsvector('custom_search'::regconfig, c3))>0;
464458

459+
-- ===================================================================
460+
-- ORDER BY queries
461+
-- ===================================================================
462+
-- we should not push order by clause with volatile expressions or unsafe
463+
-- collations
464+
EXPLAIN (VERBOSE, COSTS OFF)
465+
SELECT*FROM ft2ORDER BYft2.c1, random();
466+
EXPLAIN (VERBOSE, COSTS OFF)
467+
SELECT*FROM ft2ORDER BYft2.c1,ft2.c3 collate"C";
468+
469+
-- Ensure we don't push ORDER BY expressions which are Consts at the UNION
470+
-- child level to the foreign server.
471+
EXPLAIN (VERBOSE, COSTS OFF)
472+
SELECT*FROM (
473+
SELECT1AS type,c1FROM ft1
474+
UNION ALL
475+
SELECT2AS type,c1FROM ft2
476+
) aORDER BY type,c1;
477+
478+
EXPLAIN (VERBOSE, COSTS OFF)
479+
SELECT*FROM (
480+
SELECT1AS type,c1FROM ft1
481+
UNION ALL
482+
SELECT2AS type,c1FROM ft2
483+
) aORDER BY type;
484+
465485
-- ===================================================================
466486
-- JOIN queries
467487
-- ===================================================================

0 commit comments

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