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

Commit751a14e

Browse files
committed
Repair longstanding violation of SQL92 semantics: GROUP BY would
interpret a column name as an output column alias (targetlist AS name),ather than a real column name as it ought to. According to the spec,only ORDER BY should look at output column names. I left in GROUP BY'swillingness to use an output column number ('GROUP BY 2'), even thoughthis is also contrary to the spec --- again, only ORDER BY is supposedto accept that. But there is no possible reason to want to GROUP BYan integer constant, so keeping this old behavior won't break anySQL-compliant queries. DISTINCT ON will behave the same as GROUP BY.Change numerology regress test, which depended on the incorrectbehavior.
1 parentb48f983 commit751a14e

File tree

3 files changed

+40
-26
lines changed

3 files changed

+40
-26
lines changed

‎src/backend/parser/parse_clause.c‎

Lines changed: 17 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/parser/parse_clause.c,v 1.54 2000/02/15 23:09:08 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/parser/parse_clause.c,v 1.55 2000/02/19 23:45:05 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -721,7 +721,7 @@ parseFromClause(ParseState *pstate, List *frmList)
721721
* nodethe ORDER BY, GROUP BY, or DISTINCT ON expression to be matched
722722
* tlistthe existing target list (NB: this cannot be NIL, which is a
723723
*good thing since we'd be unable to append to it...)
724-
* clauseidentifies clause type for error messages.
724+
* clauseidentifies clause type(mainlyfor error messages).
725725
*/
726726
staticTargetEntry*
727727
findTargetlistEntry(ParseState*pstate,Node*node,List*tlist,intclause)
@@ -733,25 +733,34 @@ findTargetlistEntry(ParseState *pstate, Node *node, List *tlist, int clause)
733733
/*----------
734734
* Handle two special cases as mandated by the SQL92 spec:
735735
*
736-
* 1. ORDER/GROUP BY ColumnName
736+
* 1. ORDER BY ColumnName
737737
* For a bare identifier, we search for a matching column name
738738
* in the existing target list. Multiple matches are an error
739739
* unless they refer to identical values; for example,
740740
* we allow SELECT a, a FROM table ORDER BY a
741741
* but not SELECT a AS b, b FROM table ORDER BY b
742742
* If no match is found, we fall through and treat the identifier
743743
* as an expression.
744+
* We do NOT attempt this match for GROUP BY, since it is clearly
745+
* contrary to the spec to use an output column name in preference
746+
* to an underlying column name in GROUP BY. DISTINCT ON isn't in
747+
* the standard, so we can do what we like there; we choose to make
748+
* it work like GROUP BY.
744749
*
745-
* 2. ORDER/GROUP BY IntegerConstant
750+
* 2. ORDER BY/GROUP BY/DISTINCT ON IntegerConstant
746751
* This means to use the n'th item in the existing target list.
747-
* Note that it would make no sense to order/group by an actual
748-
* constant, so this does not create a conflict with our extension
749-
* to order/group by an expression.
752+
* Note that it would make no sense to order/group/distinct by an
753+
* actual constant, so this does not create a conflict with our
754+
* extension to order/group by an expression.
755+
* I believe that GROUP BY column-number is not sanctioned by SQL92,
756+
* but since the standard has no other behavior defined for this
757+
* syntax, we may as well continue to support our past behavior.
750758
*
751759
* Note that pre-existing resjunk targets must not be used in either case.
752760
*----------
753761
*/
754-
if (IsA(node,Ident)&& ((Ident*)node)->indirection==NIL)
762+
if (clause==ORDER_CLAUSE&&
763+
IsA(node,Ident)&& ((Ident*)node)->indirection==NIL)
755764
{
756765
char*name= ((Ident*)node)->name;
757766
foreach(tl,tlist)

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

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -88,42 +88,42 @@ SELECT DISTINCT f1 AS two FROM TEMP_GROUP;
8888

8989
SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float
9090
FROM TEMP_GROUP
91-
GROUP BYtwo
91+
GROUP BYf1
9292
ORDER BY two, max_float, min_float;
9393
two | max_float | min_float
9494
-----+----------------------+-----------------------
9595
1 | 1.2345678901234e+200 | 0
9696
2 | 0 | -1.2345678901234e+200
9797
(2 rows)
9898

99+
-- Postgres used to accept this, but it is clearly against SQL92 to
100+
-- interpret GROUP BY arguments as result column names; they should
101+
-- be source column names *only*. An error is expected.
99102
SELECT f1 AS two, max(f3) AS max_float, min(f3) AS min_float
100103
FROM TEMP_GROUP
101104
GROUP BY two
102105
ORDER BY two, max_float, min_float;
103-
two | max_float | min_float
104-
-----+----------------------+-----------------------
105-
1 | 1.2345678901234e+200 | 0
106-
2 | 0 | -1.2345678901234e+200
107-
(2 rows)
108-
106+
ERROR: Attribute 'two' not found
109107
SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1
110108
FROM TEMP_GROUP
111-
GROUP BYtwo
109+
GROUP BYf1
112110
ORDER BY two, min_minus_1;
113111
two | max_plus_1 | min_minus_1
114112
-----+----------------------+-----------------------
115113
1 | 1.2345678901234e+200 | -1
116114
2 | 1 | -1.2345678901234e+200
117115
(2 rows)
118116

119-
SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1
117+
SELECT f1 AS two,
118+
max(f2) + min(f2) AS max_plus_min,
119+
min(f3) - 1 AS min_minus_1
120120
FROM TEMP_GROUP
121-
GROUP BYtwo
121+
GROUP BYf1
122122
ORDER BY two, min_minus_1;
123-
two | max_plus_1 | min_minus_1
124-
-----+----------------------+-----------------------
125-
1 |1.2345678901234e+200 | -1
126-
2 | 1 | -1.2345678901234e+200
123+
two |max_plus_min | min_minus_1
124+
-----+--------------+-----------------------
125+
1 | 0 | -1
126+
2 |0 | -1.2345678901234e+200
127127
(2 rows)
128128

129129
DROP TABLE TEMP_INT2;

‎src/test/regress/sql/numerology.sql‎

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -67,22 +67,27 @@ SELECT DISTINCT f1 AS two FROM TEMP_GROUP;
6767

6868
SELECT f1AS two,max(f3)AS max_float,min(f3)as min_float
6969
FROM TEMP_GROUP
70-
GROUP BYtwo
70+
GROUP BYf1
7171
ORDER BY two, max_float, min_float;
7272

73+
-- Postgres used to accept this, but it is clearly against SQL92 to
74+
-- interpret GROUP BY arguments as result column names; they should
75+
-- be source column names *only*. An error is expected.
7376
SELECT f1AS two,max(f3)AS max_float,min(f3)AS min_float
7477
FROM TEMP_GROUP
7578
GROUP BY two
7679
ORDER BY two, max_float, min_float;
7780

7881
SELECT f1AS two, (max(f3)+1)AS max_plus_1, (min(f3)-1)AS min_minus_1
7982
FROM TEMP_GROUP
80-
GROUP BYtwo
83+
GROUP BYf1
8184
ORDER BY two, min_minus_1;
8285

83-
SELECT f1AS two, (max(f3)+1)AS max_plus_1, (min(f3)-1)AS min_minus_1
86+
SELECT f1AS two,
87+
max(f2)+min(f2)AS max_plus_min,
88+
min(f3)-1AS min_minus_1
8489
FROM TEMP_GROUP
85-
GROUP BYtwo
90+
GROUP BYf1
8691
ORDER BY two, min_minus_1;
8792

8893
DROPTABLE TEMP_INT2;

0 commit comments

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