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

Commitfba999c

Browse files
committed
Allow ORDER BY/GROUP BY/etc items to match targetlist items regardless of
any implicit casting previously applied to the targetlist item. This isreasonable because the implicit cast, by definition, wasn't written by theuser; so we are preserving the expected behavior that ORDER BY items matchtextually equivalent tlist items. The case never arose before because therecouldn't be any implicit casting of a top-level SELECT item before we processORDER BY etc. But now it can arise in the context of aggregates containingORDER BY clauses, since the "targetlist" is the already-casted list ofarguments for the aggregate. The net effect is that the datatype used forORDER BY/DISTINCT purposes is the aggregate's declared input type, not thatof the original input column; which is a bit debatable but not horrendous,and to do otherwise would require major rework that doesn't seem justified.Per bug #5564 from Daniel Grace. Back-patch to 9.0 where aggregate ORDER BYwas implemented.
1 parent1b51018 commitfba999c

File tree

3 files changed

+42
-2
lines changed

3 files changed

+42
-2
lines changed

‎src/backend/parser/parse_clause.c

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.198 2010/02/26 02:00:50 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.199 2010/07/18 19:37:48 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -21,6 +21,7 @@
2121
#include"commands/defrem.h"
2222
#include"nodes/makefuncs.h"
2323
#include"nodes/nodeFuncs.h"
24+
#include"optimizer/clauses.h"
2425
#include"optimizer/tlist.h"
2526
#include"optimizer/var.h"
2627
#include"parser/analyze.h"
@@ -1430,8 +1431,20 @@ findTargetlistEntrySQL99(ParseState *pstate, Node *node, List **tlist)
14301431
foreach(tl,*tlist)
14311432
{
14321433
TargetEntry*tle= (TargetEntry*)lfirst(tl);
1434+
Node*texpr;
14331435

1434-
if (equal(expr,tle->expr))
1436+
/*
1437+
* Ignore any implicit cast on the existing tlist expression.
1438+
*
1439+
* This essentially allows the ORDER/GROUP/etc item to adopt the same
1440+
* datatype previously selected for a textually-equivalent tlist item.
1441+
* There can't be any implicit cast at top level in an ordinary SELECT
1442+
* tlist at this stage, but the case does arise with ORDER BY in an
1443+
* aggregate function.
1444+
*/
1445+
texpr=strip_implicit_coercions((Node*)tle->expr);
1446+
1447+
if (equal(expr,texpr))
14351448
returntle;
14361449
}
14371450

‎src/test/regress/expected/aggregates.out

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -830,3 +830,24 @@ select string_agg(a,',') from (values(null),(null)) g(a);
830830

831831
(1 row)
832832

833+
-- check some implicit casting cases, as per bug #5564
834+
select string_agg(distinct f1 order by f1) from varchar_tbl; -- ok
835+
string_agg
836+
------------
837+
aababcd
838+
(1 row)
839+
840+
select string_agg(distinct f1::text order by f1) from varchar_tbl; -- not ok
841+
ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
842+
LINE 1: select string_agg(distinct f1::text order by f1) from varcha...
843+
^
844+
select string_agg(distinct f1 order by f1::text) from varchar_tbl; -- not ok
845+
ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
846+
LINE 1: select string_agg(distinct f1 order by f1::text) from varcha...
847+
^
848+
select string_agg(distinct f1::text order by f1::text) from varchar_tbl; -- ok
849+
string_agg
850+
------------
851+
aababcd
852+
(1 row)
853+

‎src/test/regress/sql/aggregates.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -362,3 +362,9 @@ select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
362362
select string_agg(a,',')from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
363363
select string_agg(a,',')from (values(null),(null),('bbbb'),('cccc')) g(a);
364364
select string_agg(a,',')from (values(null),(null)) g(a);
365+
366+
-- check some implicit casting cases, as per bug #5564
367+
select string_agg(distinct f1order by f1)from varchar_tbl;-- ok
368+
select string_agg(distinct f1::textorder by f1)from varchar_tbl;-- not ok
369+
select string_agg(distinct f1order by f1::text)from varchar_tbl;-- not ok
370+
select string_agg(distinct f1::textorder by f1::text)from varchar_tbl;-- ok

0 commit comments

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