Skip to content

Commit 9d5c0af

Browse files
scrappyscrappy
committed
From: Thomas Lockhart <Thomas.G.Lockhart@jpl.nasa.gov>
Subject: [HACKERS] Aggregate function patches Here are the aggregate function patches I originally sent in last December. They fix sum() and avg() behavior for ints and floats when NULL values are involved. I was waiting to resubmit these until I had a chance to write a v6.0->v6.1 database upgrade script to ensure that existing v6.0 databases which have not been reloaded for v6.1 do no break with the new aggregate behavior. These scripts are included below. It's OK with me if someone wants to do something different with the upgrade strategy, but something like this was discussed a few weeks ago. Also, there were a couple of small items which cropped up in doing a clean install of 970403 (actually 970402 + 970403 changes since the full 970403 tar file appears to be damaged or at least suspect). They are the first two patches below and can be omitted if desired (although I think they aren't dangerous :).
1 parent 164cd7a commit 9d5c0af

File tree

6 files changed

+127
-21
lines changed

6 files changed

+127
-21
lines changed

‎src/backend/executor/nodeAgg.c

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -368,7 +368,12 @@ ExecAgg(Agg *node)
368368
char *args[2];
369369
AggFuncInfo *aggfns = &aggFuncInfo[i];
370370

371-
if (aggfns->finalfn && nTuplesAgged > 0) {
371+
if (noInitValue[i]) {
372+
/*
373+
* No values found for this agg; return current state.
374+
* This seems to fix behavior for avg() aggregate. -tgl 12/96
375+
*/
376+
} else if (aggfns->finalfn && nTuplesAgged > 0) {
372377
if (aggfns->finalfn_nargs > 1) {
373378
args[0] = (char*)value1[i];
374379
args[1] = (char*)value2[i];

‎src/backend/optimizer/geqo/geqo_eval.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
* Copyright (c) 1994, Regents of the University of California
77
*
8-
* $Id: geqo_eval.c,v 1.6 1997/03/03 23:26:45 scrappy Exp $
8+
* $Id: geqo_eval.c,v 1.7 1997/04/03 19:55:35 scrappy Exp $
99
*
1010
*-------------------------------------------------------------------------
1111
*/
@@ -23,7 +23,9 @@
2323
#include <math.h>
2424
#ifdef HAVE_LIMITS_H
2525
# include <limits.h>
26-
# define MAXINT INT_MAX
26+
# ifndef MAXINT
27+
# define MAXINT INT_MAX
28+
# endif
2729
#else
2830
# include <values.h>
2931
#endif

‎src/backend/tcop/aclchk.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/tcop/Attic/aclchk.c,v 1.7 1997/03/12 20:48:17 scrappy Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/tcop/Attic/aclchk.c,v 1.8 1997/04/03 19:55:12 scrappy Exp $
1111
*
1212
* NOTES
1313
* See acl.h.
@@ -277,7 +277,7 @@ aclcheck(Acl *acl, AclId id, AclIdType idtype, AclMode mode)
277277
* the system never creates an empty ACL.
278278
*/
279279
if (num < 1) {
280-
#ifdef ACLDEBUG_TRACE || 1
280+
#if ACLDEBUG_TRACE || 1
281281
elog(DEBUG, "aclcheck: zero-length ACL, returning 1");
282282
#endif
283283
return ACLCHECK_OK;

‎src/include/catalog/pg_aggregate.h

Lines changed: 12 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
* Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: pg_aggregate.h,v 1.4 1997/04/02 18:36:09 scrappy Exp $
10+
* $Id: pg_aggregate.h,v 1.5 1997/04/03 19:56:19 scrappy Exp $
1111
*
1212
* NOTES
1313
* the genbki.sh script reads this file and generates .bki
@@ -19,7 +19,7 @@
1919
#define PG_AGGREGATE_H
2020

2121
/* ----------------
22-
* postgres.h contains the system type definintions and the
22+
* postgres.h contains the system type definitions and the
2323
* CATALOG(), BOOTSTRAP and DATA() sugar words so this file
2424
* can be read by both genbki.sh and the C compiler.
2525
* ----------------
@@ -87,15 +87,15 @@ typedef FormData_pg_aggregate *Form_pg_aggregate;
8787
* ---------------
8888
*/
8989

90-
DATA(insert OID = 0 ( avg PGUID int4pl int4inc int4div 23 23 23 23 0 0 ));
91-
DATA(insert OID = 0 ( avg PGUID int2pl int2inc int2div 21 21 21 21 0 0 ));
92-
DATA(insert OID = 0 ( avg PGUID float4pl float4inc float4div 700 700 700 700 0.0 0.0 ));
93-
DATA(insert OID = 0 ( avg PGUID float8pl float8inc float8div 701 701 701 701 0.0 0.0 ));
90+
DATA(insert OID = 0 ( avg PGUID int4pl int4inc int4div 23 23 23 23 _null_ 0 ));
91+
DATA(insert OID = 0 ( avg PGUID int2pl int2inc int2div 21 21 21 21 _null_ 0 ));
92+
DATA(insert OID = 0 ( avg PGUID float4pl float4inc float4div 700 700 700 700 _null_ 0.0 ));
93+
DATA(insert OID = 0 ( avg PGUID float8pl float8inc float8div 701 701 701 701 _null_ 0.0 ));
9494

95-
DATA(insert OID = 0 ( sum PGUID int4pl - - 23 23 0 23 0 _null_ ));
96-
DATA(insert OID = 0 ( sum PGUID int2pl - - 21 21 0 21 0 _null_ ));
97-
DATA(insert OID = 0 ( sum PGUID float4pl - - 700 700 0 700 0.0 _null_ ));
98-
DATA(insert OID = 0 ( sum PGUID float8pl - - 701 701 0 701 0.0 _null_ ));
95+
DATA(insert OID = 0 ( sum PGUID int4pl - - 23 23 0 23 _null_ _null_ ));
96+
DATA(insert OID = 0 ( sum PGUID int2pl - - 21 21 0 21 _null_ _null_ ));
97+
DATA(insert OID = 0 ( sum PGUID float4pl - - 700 700 0 700 _null_ _null_ ));
98+
DATA(insert OID = 0 ( sum PGUID float8pl - - 701 701 0 701 _null_ _null_ ));
9999

100100
DATA(insert OID = 0 ( max PGUID int4larger - - 23 23 0 23 _null_ _null_ ));
101101
DATA(insert OID = 0 ( max PGUID int2larger - - 21 21 0 21 _null_ _null_ ));
@@ -113,10 +113,10 @@ DATA(insert OID = 0 ( min PGUID int4smaller - - 702 702 0 702 _null_ _nul
113113
DATA(insert OID = 0 ( min PGUID date_smaller - - 1082 1082 0 1082 _null_ _null_ ));
114114
DATA(insert OID = 0 ( min PGUID float8smaller - - 1084 1084 0 1084 _null_ _null_ ));
115115

116-
DATA(insert OID = 0 ( count PGUID - int4inc - 0 0 23 23 _null_ 0 ));
116+
DATA(insert OID = 0 ( count PGUID - int4inc - 0 0 23 23 _null_ 0 ));
117117

118118
/*
119-
* prototypes for fucnctions in pg_aggregate.c
119+
* prototypes for functions in pg_aggregate.c
120120
*/
121121
extern void AggregateCreate(char *aggName,
122122
char *aggtransfn1Name,
@@ -131,7 +131,3 @@ extern char *AggNameGetInitVal(char *aggName, Oid basetype,
131131
int xfuncno, bool *isNull);
132132

133133
#endif /* PG_AGGREGATE_H */
134-
135-
136-
137-

‎src/update6_0-6_1.sh

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
1+
#!/bin/sh
2+
# update
3+
# Script to apply patches to existing databases
4+
# to upgrade from Postgres v6.0 to v6.1.
5+
echo ""
6+
echo "This utility does a minimal upgrade for existing v6.0 databases."
7+
echo "Note that several new features and functions in Postgres"
8+
echo " will not be available unless the databases are reloaded"
9+
echo " from a clean install of v6.1."
10+
echo ""
11+
echo "This update script is not necessary for new or reloaded databases,"
12+
echo " but will not damage them. You should update existing v6.1beta"
13+
echo " databases created on or before 1997-04-04 (when the patches for"
14+
echo " aggregate functions were applied to the v6.1beta source tree)."
15+
echo ""
16+
echo "Features present with this simple update include:"
17+
echo " - aggregate operators sum() and avg() behave correctly with NULLs"
18+
echo " - the point distance operator '<===>' returns float8 rather than int4"
19+
echo " - some duplicate function OIDs are renumbered to eliminate conflicts"
20+
echo ""
21+
echo "Features unavailable with only this simple update include:"
22+
echo " - new string handling functions a la Oracle/Ingres"
23+
echo " - new date and time data types and expanded functionality"
24+
echo " - some new function overloading to simplify function names"
25+
echo ""
26+
echo "Note that if v6.0 databases are not reloaded from a clean install of v6.1"
27+
echo " or if this update is not applied to existing v6.0 databases:"
28+
echo " - aggregate functions avg() and sum() may divide-by-zero for int4 data types"
29+
#
30+
srcdir=`pwd`
31+
srcsql="update6_0-6_1.sql"
32+
CMDSQL="psql"
33+
SRCSQL="$srcdir/$srcsql"
34+
#
35+
if [ -z $SRCSQL ]; then
36+
echo "unable to locate $SRCSQL"
37+
exit 1
38+
fi
39+
#
40+
echo ""
41+
echo "updating databases found in $PGDATA/base"
42+
echo ""
43+
#
44+
cd $PGDATA/base
45+
for d in *
46+
do
47+
echo "updating $d at `date` ..."
48+
echo "try $CMDSQL $d < $SRCSQL"
49+
$CMDSQL $d < $SRCSQL
50+
echo "completed updating $d at `date`"
51+
done
52+
#
53+
echo ""
54+
echo "completed all updates at `date`"
55+
echo ""
56+
exit

‎src/update6_0-6_1.sql

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
1+
-- Aggregate functions
2+
-- Thomas Lockhart
3+
-- This fixes the behavior of built-in aggregate functions avg() and sum().
4+
-- Code tested on postgres95 v1.0.9, postgres v6.0, and postgres v6.1b-970315.
5+
-- Original definitions return zero rather than null for empty set attributes.
6+
-- Postgres source code says that null behavior for aggregates is not correct,
7+
-- but does describe the correct behavior for pass-by-reference data types
8+
-- if it is given null initial values (from pg_aggregate).
9+
-- Note that pass-by-value data types (e.g. int4) require a simple source code
10+
-- change in backend/executor/nodeAgg.c to avoid divide-by-zero results.
11+
-- If this SQL update is applied without making the corresponding source code
12+
-- patch, then floating point types will work correctly but integer types will
13+
-- divide-by-zero.
14+
-- If the source code patch is applied but this SQL update is not, then there
15+
-- will be divide-by-zero results for floating point types.
16+
17+
-- For aggregate attributes, the correct behavior is as follows:
18+
-- count(*) should return a count of all tuples, null or otherwise
19+
-- count(col) should return a count of all non-null values, zero if none
20+
-- avg(col), sum(col), etc should ignore null fields and return null if there
21+
-- are no non-null inputs
22+
-- Ref: the original Date book
23+
24+
update pg_aggregate set agginitval1=null
25+
where aggname = 'avg' or aggname = 'sum';
26+
27+
-- Geometric functions
28+
-- Thomas Lockhart
29+
-- This replaces the distance operator with one returning a floating point number.
30+
-- The original operator 'pointdist' returned an integer.
31+
-- There is no corresponding source code change required for this patch.
32+
33+
update pg_operator set oprresult = 701, oprcode = 'point_distance'::regproc
34+
where oprname = '<===>' and oprresult = 23;
35+
36+
-- Date functions
37+
-- Thomas Lockhart
38+
-- This fixes conflicting OIDs within the date and time declarations.
39+
40+
update pg_proc set oid = 1138::oid where proname = 'date_larger';
41+
update pg_proc set oid = 1139::oid where proname = 'date_smaller';
42+
update pg_proc set oid = 1140::oid where proname = 'date_mi';
43+
update pg_proc set oid = 1141::oid where proname = 'date_pli';
44+
update pg_proc set oid = 1142::oid where proname = 'date_mii';
45+
update pg_proc set oid = 1143::oid where proname = 'timein';
46+
update pg_proc set oid = 1144::oid where proname = 'timeout';
47+
update pg_proc set oid = 1145::oid where proname = 'time_eq';

0 commit comments

Comments
 (0)