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

Commitab64b27

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 parent0fe82e4 commitab64b27

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
@@ -3624,13 +3624,12 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
36243624
{
36253625
ListCell*lcell;
36263626
intnestlevel;
3627-
constchar*delim=" ";
36283627
StringInfobuf=context->buf;
3628+
boolgotone= false;
36293629

36303630
/* Make sure any constants in the exprs are printed portably */
36313631
nestlevel=set_transmission_modes();
36323632

3633-
appendStringInfoString(buf," ORDER BY");
36343633
foreach(lcell,pathkeys)
36353634
{
36363635
PathKey*pathkey=lfirst(lcell);
@@ -3663,6 +3662,26 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
36633662

36643663
em_expr=em->em_expr;
36653664

3665+
/*
3666+
* If the member is a Const expression then we needn't add it to the
3667+
* ORDER BY clause. This can happen in UNION ALL queries where the
3668+
* union child targetlist has a Const. Adding these would be
3669+
* wasteful, but also, for INT columns, an integer literal would be
3670+
* seen as an ordinal column position rather than a value to sort by.
3671+
* deparseConst() does have code to handle this, but it seems less
3672+
* effort on all accounts just to skip these for ORDER BY clauses.
3673+
*/
3674+
if (IsA(em_expr,Const))
3675+
continue;
3676+
3677+
if (!gotone)
3678+
{
3679+
appendStringInfoString(buf," ORDER BY ");
3680+
gotone= true;
3681+
}
3682+
else
3683+
appendStringInfoString(buf,", ");
3684+
36663685
/*
36673686
* Lookup the operator corresponding to the strategy in the opclass.
36683687
* The datatype used by the opfamily is not necessarily the same as
@@ -3677,7 +3696,6 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
36773696
pathkey->pk_strategy,em->em_datatype,em->em_datatype,
36783697
pathkey->pk_opfamily);
36793698

3680-
appendStringInfoString(buf,delim);
36813699
deparseExpr(em_expr,context);
36823700

36833701
/*
@@ -3687,7 +3705,6 @@ appendOrderByClause(List *pathkeys, bool has_final_sort,
36873705
appendOrderBySuffix(oprid,exprType((Node*)em_expr),
36883706
pathkey->pk_nulls_first,context);
36893707

3690-
delim=", ";
36913708
}
36923709
reset_transmission_modes(nestlevel);
36933710
}

‎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
@@ -1183,6 +1157,73 @@ WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
11831157
642 | '00642':1
11841158
(1 row)
11851159

1160+
-- ===================================================================
1161+
-- ORDER BY queries
1162+
-- ===================================================================
1163+
-- we should not push order by clause with volatile expressions or unsafe
1164+
-- collations
1165+
EXPLAIN (VERBOSE, COSTS OFF)
1166+
SELECT * FROM ft2 ORDER BY ft2.c1, random();
1167+
QUERY PLAN
1168+
-------------------------------------------------------------------------------
1169+
Sort
1170+
Output: c1, c2, c3, c4, c5, c6, c7, c8, (random())
1171+
Sort Key: ft2.c1, (random())
1172+
-> Foreign Scan on public.ft2
1173+
Output: c1, c2, c3, c4, c5, c6, c7, c8, random()
1174+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1175+
(6 rows)
1176+
1177+
EXPLAIN (VERBOSE, COSTS OFF)
1178+
SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
1179+
QUERY PLAN
1180+
-------------------------------------------------------------------------------
1181+
Sort
1182+
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
1183+
Sort Key: ft2.c1, ft2.c3 COLLATE "C"
1184+
-> Foreign Scan on public.ft2
1185+
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
1186+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1187+
(6 rows)
1188+
1189+
-- Ensure we don't push ORDER BY expressions which are Consts at the UNION
1190+
-- child level to the foreign server.
1191+
EXPLAIN (VERBOSE, COSTS OFF)
1192+
SELECT * FROM (
1193+
SELECT 1 AS type,c1 FROM ft1
1194+
UNION ALL
1195+
SELECT 2 AS type,c1 FROM ft2
1196+
) a ORDER BY type,c1;
1197+
QUERY PLAN
1198+
---------------------------------------------------------------------------------
1199+
Merge Append
1200+
Sort Key: (1), ft1.c1
1201+
-> Foreign Scan on public.ft1
1202+
Output: 1, ft1.c1
1203+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
1204+
-> Foreign Scan on public.ft2
1205+
Output: 2, ft2.c1
1206+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
1207+
(8 rows)
1208+
1209+
EXPLAIN (VERBOSE, COSTS OFF)
1210+
SELECT * FROM (
1211+
SELECT 1 AS type,c1 FROM ft1
1212+
UNION ALL
1213+
SELECT 2 AS type,c1 FROM ft2
1214+
) a ORDER BY type;
1215+
QUERY PLAN
1216+
---------------------------------------------------
1217+
Merge Append
1218+
Sort Key: (1)
1219+
-> Foreign Scan on public.ft1
1220+
Output: 1, ft1.c1
1221+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
1222+
-> Foreign Scan on public.ft2
1223+
Output: 2, ft2.c1
1224+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
1225+
(8 rows)
1226+
11861227
-- ===================================================================
11871228
-- JOIN queries
11881229
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

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

364358
-- user-defined operator/function
365359
CREATEFUNCTIONpostgres_fdw_abs(int) RETURNSintAS $$
@@ -451,6 +445,32 @@ WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
451445
SELECT c1, to_tsvector('custom_search'::regconfig, c3)FROM ft1
452446
WHERE c1=642AND length(to_tsvector('custom_search'::regconfig, c3))>0;
453447

448+
-- ===================================================================
449+
-- ORDER BY queries
450+
-- ===================================================================
451+
-- we should not push order by clause with volatile expressions or unsafe
452+
-- collations
453+
EXPLAIN (VERBOSE, COSTS OFF)
454+
SELECT*FROM ft2ORDER BYft2.c1, random();
455+
EXPLAIN (VERBOSE, COSTS OFF)
456+
SELECT*FROM ft2ORDER BYft2.c1,ft2.c3 collate"C";
457+
458+
-- Ensure we don't push ORDER BY expressions which are Consts at the UNION
459+
-- child level to the foreign server.
460+
EXPLAIN (VERBOSE, COSTS OFF)
461+
SELECT*FROM (
462+
SELECT1AS type,c1FROM ft1
463+
UNION ALL
464+
SELECT2AS type,c1FROM ft2
465+
) aORDER BY type,c1;
466+
467+
EXPLAIN (VERBOSE, COSTS OFF)
468+
SELECT*FROM (
469+
SELECT1AS type,c1FROM ft1
470+
UNION ALL
471+
SELECT2AS type,c1FROM ft2
472+
) aORDER BY type;
473+
454474
-- ===================================================================
455475
-- JOIN queries
456476
-- ===================================================================

0 commit comments

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