Skip to content

Commit cf57ef4

Browse files
author
Neil Conway
committed
Implement width_bucket() for the float8 data type.
The implementation is somewhat ugly logic-wise, but I don't see an easy way to make it more concise. When writing this, I noticed that my previous implementation of width_bucket() doesn't handle NaN correctly: postgres=# select width_bucket('NaN', 1, 5, 5); width_bucket -------------- 6 (1 row) AFAICS SQL:2003 does not define a NaN value, so it doesn't address how width_bucket() should behave here. The patch changes width_bucket() so that ereport(ERROR) is raised if NaN is specified for the operand or the lower or upper bounds to width_bucket(). For float8, NaN is disallowed for any of the floating-point inputs, and +/- infinity is disallowed for the histogram bounds (but allowed for the operand). Update docs and regression tests, bump the catversion.
1 parent da07c81 commit cf57ef4

File tree

8 files changed

+210
-69
lines changed

8 files changed

+210
-69
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.350 2007/01/12 23:34:54 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.351 2007/01/16 21:41:12 neilc Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -854,6 +854,16 @@
854854
<entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>
855855
<entry><literal>3</literal></entry>
856856
</row>
857+
858+
<row>
859+
<entry><literal><function>width_bucket</function>(<parameter>op</parameter> <type>dp</type>, <parameter>b1</parameter> <type>dp</type>, <parameter>b2</parameter> <type>dp</type>, <parameter>count</parameter> <type>int</type>)</literal></entry>
860+
<entry><type>int</type></entry>
861+
<entry>return the bucket to which <parameter>operand</> would
862+
be assigned in an equidepth histogram with <parameter>count</>
863+
buckets, in the range <parameter>b1</> to <parameter>b2</></entry>
864+
<entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>
865+
<entry><literal>3</literal></entry>
866+
</row>
857867
</tbody>
858868
</tgroup>
859869
</table>

‎src/backend/utils/adt/float.c

Lines changed: 83 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.146 2007/01/06 20:21:29 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.147 2007/01/16 21:41:13 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2745,6 +2745,88 @@ float84ge(PG_FUNCTION_ARGS)
27452745
PG_RETURN_BOOL(float8_cmp_internal(arg1, arg2) >= 0);
27462746
}
27472747

2748+
/*
2749+
* Implements the float8 version of the width_bucket() function
2750+
* defined by SQL2003. See also width_bucket_numeric().
2751+
*
2752+
* 'bound1' and 'bound2' are the lower and upper bounds of the
2753+
* histogram's range, respectively. 'count' is the number of buckets
2754+
* in the histogram. width_bucket() returns an integer indicating the
2755+
* bucket number that 'operand' belongs to in an equiwidth histogram
2756+
* with the specified characteristics. An operand smaller than the
2757+
* lower bound is assigned to bucket 0. An operand greater than the
2758+
* upper bound is assigned to an additional bucket (with number
2759+
* count+1). We don't allow "NaN" for any of the float8 inputs, and we
2760+
* don't allow either of the histogram bounds to be +/- infinity.
2761+
*/
2762+
Datum
2763+
width_bucket_float8(PG_FUNCTION_ARGS)
2764+
{
2765+
float8 operand = PG_GETARG_FLOAT8(0);
2766+
float8 bound1 = PG_GETARG_FLOAT8(1);
2767+
float8 bound2 = PG_GETARG_FLOAT8(2);
2768+
int32 count = PG_GETARG_INT32(3);
2769+
int32 result;
2770+
2771+
if (count <= 0.0)
2772+
ereport(ERROR,
2773+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
2774+
errmsg("count must be greater than zero")));
2775+
2776+
if (isnan(operand) || isnan(bound1) || isnan(bound2))
2777+
ereport(ERROR,
2778+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
2779+
errmsg("operand, lower bound and upper bound cannot be NaN")));
2780+
2781+
/* Note that we allow "operand" to be infinite */
2782+
if (is_infinite(bound1) || is_infinite(bound2))
2783+
ereport(ERROR,
2784+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
2785+
errmsg("lower and upper bounds must be finite")));
2786+
2787+
if (bound1 < bound2)
2788+
{
2789+
if (operand < bound1)
2790+
result = 0;
2791+
else if (operand >= bound2)
2792+
{
2793+
result = count + 1;
2794+
/* check for overflow */
2795+
if (result < count)
2796+
ereport(ERROR,
2797+
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
2798+
errmsg("integer out of range")));
2799+
}
2800+
else
2801+
result = ((float8) count * (operand - bound1) / (bound2 - bound1)) + 1;
2802+
}
2803+
else if (bound1 > bound2)
2804+
{
2805+
if (operand > bound1)
2806+
result = 0;
2807+
else if (operand <= bound2)
2808+
{
2809+
result = count + 1;
2810+
/* check for overflow */
2811+
if (result < count)
2812+
ereport(ERROR,
2813+
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
2814+
errmsg("integer out of range")));
2815+
}
2816+
else
2817+
result = ((float8) count * (bound1 - operand) / (bound1 - bound2)) + 1;
2818+
}
2819+
else
2820+
{
2821+
ereport(ERROR,
2822+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
2823+
errmsg("lower bound cannot equal upper bound")));
2824+
result = 0; /* keep the compiler quiet */
2825+
}
2826+
2827+
PG_RETURN_INT32(result);
2828+
}
2829+
27482830
/* ========== PRIVATE ROUTINES ========== */
27492831

27502832
#ifndef HAVE_CBRT

‎src/backend/utils/adt/numeric.c

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
* Copyright (c) 1998-2007, PostgreSQL Global Development Group
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.98 2007/01/05 22:19:41 momjian Exp $
17+
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.99 2007/01/16 21:41:13 neilc Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -874,16 +874,17 @@ numeric_floor(PG_FUNCTION_ARGS)
874874
}
875875

876876
/*
877-
* width_bucket_numeric() -
877+
* Implements the numeric version of the width_bucket() function
878+
* defined by SQL2003. See also width_bucket_float8().
878879
*
879880
* 'bound1' and 'bound2' are the lower and upper bounds of the
880881
* histogram's range, respectively. 'count' is the number of buckets
881882
* in the histogram. width_bucket() returns an integer indicating the
882-
* bucket number that 'operand' belongs in for an equiwidth histogram
883+
* bucket number that 'operand' belongs to in an equiwidth histogram
883884
* with the specified characteristics. An operand smaller than the
884885
* lower bound is assigned to bucket 0. An operand greater than the
885886
* upper bound is assigned to an additional bucket (with number
886-
* count+1).
887+
* count+1). We don't allow "NaN" for any of the numeric arguments.
887888
*/
888889
Datum
889890
width_bucket_numeric(PG_FUNCTION_ARGS)
@@ -901,6 +902,13 @@ width_bucket_numeric(PG_FUNCTION_ARGS)
901902
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
902903
errmsg("count must be greater than zero")));
903904

905+
if (NUMERIC_IS_NAN(operand) ||
906+
NUMERIC_IS_NAN(bound1) ||
907+
NUMERIC_IS_NAN(bound2))
908+
ereport(ERROR,
909+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
910+
errmsg("operand, lower bound and upper bound cannot be NaN")));
911+
904912
init_var(&result_var);
905913
init_var(&count_var);
906914

@@ -937,6 +945,7 @@ width_bucket_numeric(PG_FUNCTION_ARGS)
937945
break;
938946
}
939947

948+
/* if result exceeds the range of a legal int4, we ereport here */
940949
result = numericvar_to_int4(&result_var);
941950

942951
free_var(&count_var);
@@ -946,8 +955,6 @@ width_bucket_numeric(PG_FUNCTION_ARGS)
946955
}
947956

948957
/*
949-
* compute_bucket() -
950-
*
951958
* If 'operand' is not outside the bucket range, determine the correct
952959
* bucket for it to go. The calculations performed by this function
953960
* are derived directly from the SQL2003 spec.

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.371 2007/01/09 02:14:15 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.372 2007/01/16 21:41:13 neilc Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200701081
56+
#define CATALOG_VERSION_NO 200701161
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.436 2007/01/05 22:19:53 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.437 2007/01/16 21:41:13 neilc Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -636,6 +636,8 @@ DATA(insert OID = 309 ( float84gt PGNSP PGUID 12 f f t f i 2 16 "701 700" _
636636
DESCR("greater-than");
637637
DATA(insert OID = 310 ( float84ge PGNSP PGUID 12 f f t f i 2 16 "701 700" _null_ _null_ _null_ float84ge - _null_ ));
638638
DESCR("greater-than-or-equal");
639+
DATA(insert OID = 320 ( width_bucket PGNSP PGUID 12 f f t f i 4 23 "701 701 701 23" _null_ _null_ _null_ width_bucket_float8 - _null_ ));
640+
DESCR("bucket number of operand in equidepth histogram");
639641

640642
DATA(insert OID = 311 ( float8 PGNSP PGUID 12 f f t f i 1 701 "700" _null_ _null_ _null_ ftod - _null_ ));
641643
DESCR("convert float4 to float8");

‎src/include/utils/builtins.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.285 2007/01/05 22:19:58 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.286 2007/01/16 21:41:14 neilc Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -380,6 +380,7 @@ extern Datum float84lt(PG_FUNCTION_ARGS);
380380
extern Datum float84le(PG_FUNCTION_ARGS);
381381
extern Datum float84gt(PG_FUNCTION_ARGS);
382382
extern Datum float84ge(PG_FUNCTION_ARGS);
383+
extern Datum width_bucket_float8(PG_FUNCTION_ARGS);
383384

384385
/* dbsize.c */
385386
extern Datum pg_tablespace_size_oid(PG_FUNCTION_ARGS);

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

Lines changed: 63 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -730,55 +730,77 @@ SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;
730730
(7 rows)
731731

732732
DROP TABLE ceil_floor_round;
733-
-- Testing for width_bucket()
734-
-- NULL result
735-
SELECT width_bucket(NULL, NULL, NULL, NULL);
736-
width_bucket
737-
--------------
738-
739-
(1 row)
740-
733+
-- Testing for width_bucket(). For convenience, we test both the
734+
-- numeric and float8 versions of the function in this file.
741735
-- errors
742736
SELECT width_bucket(5.0, 3.0, 4.0, 0);
743737
ERROR: count must be greater than zero
744738
SELECT width_bucket(5.0, 3.0, 4.0, -5);
745739
ERROR: count must be greater than zero
746-
SELECT width_bucket(3.0, 3.0, 3.0, 888);
740+
SELECT width_bucket(3.5, 3.0, 3.0, 888);
741+
ERROR: lower bound cannot equal upper bound
742+
SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, 0);
743+
ERROR: count must be greater than zero
744+
SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, -5);
745+
ERROR: count must be greater than zero
746+
SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888);
747747
ERROR: lower bound cannot equal upper bound
748+
SELECT width_bucket('NaN', 3.0, 4.0, 888);
749+
ERROR: operand, lower bound and upper bound cannot be NaN
750+
SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888);
751+
ERROR: operand, lower bound and upper bound cannot be NaN
748752
-- normal operation
749-
CREATE TABLE width_bucket_test (operand numeric);
750-
COPY width_bucket_test FROM stdin;
753+
CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8);
754+
COPY width_bucket_test (operand_num) FROM stdin;
755+
UPDATE width_bucket_test SET operand_f8 = operand_num::float8;
751756
SELECT
752-
operand,
753-
width_bucket(operand, 0, 10, 5) AS wb_1,
754-
width_bucket(operand, 10, 0, 5) AS wb_2,
755-
width_bucket(operand, 2, 8, 4) AS wb_3,
756-
width_bucket(operand, 5.0, 5.5, 20) AS wb_4,
757-
width_bucket(operand, -25, 25, 10) AS wb_5
757+
operand_num, operand_f8,
758+
width_bucket(operand_num, 0, 10, 5) AS wb_1,
759+
width_bucket(operand_f8, 0, 10, 5) AS wb_1f,
760+
width_bucket(operand_num, 10, 0, 5) AS wb_2,
761+
width_bucket(operand_f8, 10, 0, 5) AS wb_2f,
762+
width_bucket(operand_num, 2, 8, 4) AS wb_3,
763+
width_bucket(operand_f8, 2, 8, 4) AS wb_3f,
764+
width_bucket(operand_num, 5.0, 5.5, 20) AS wb_4,
765+
width_bucket(operand_f8, 5.0, 5.5, 20) AS wb_4f,
766+
width_bucket(operand_num, -25, 25, 10) AS wb_5,
767+
width_bucket(operand_f8, -25, 25, 10) AS wb_5f
758768
FROM width_bucket_test;
759-
operand | wb_1 | wb_2 | wb_3 | wb_4 | wb_5
760-
------------------+------+------+------+------+------
761-
-5.2 | 0 | 6 | 0 | 0 | 4
762-
-0.0000000000001 | 0 | 6 | 0 | 0 | 5
763-
0.0000000000001 | 1 | 5 | 0 | 0 | 6
764-
1 | 1 | 5 | 0 | 0 | 6
765-
1.99999999999999 | 1 | 5 | 0 | 0 | 6
766-
2 | 2 | 5 | 1 | 0 | 6
767-
2.00000000000001 | 2 | 4 | 1 | 0 | 6
768-
3 | 2 | 4 | 1 | 0 | 6
769-
4 | 3 | 4 | 2 | 0 | 6
770-
4.5 | 3 | 3 | 2 | 0 | 6
771-
5 | 3 | 3 | 3 | 1 | 7
772-
5.5 | 3 | 3 | 3 | 21 | 7
773-
6 | 4 | 3 | 3 | 21 | 7
774-
7 | 4 | 2 | 4 | 21 | 7
775-
8 | 5 | 2 | 5 | 21 | 7
776-
9 | 5 | 1 | 5 | 21 | 7
777-
9.99999999999999 | 5 | 1 | 5 | 21 | 7
778-
10 | 6 | 1 | 5 | 21 | 8
779-
10.0000000000001 | 6 | 0 | 5 | 21 | 8
780-
NaN | 6 | 0 | 5 | 21 | 11
781-
(20 rows)
769+
operand_num | operand_f8 | wb_1 | wb_1f | wb_2 | wb_2f | wb_3 | wb_3f | wb_4 | wb_4f | wb_5 | wb_5f
770+
------------------+------------------+------+-------+------+-------+------+-------+------+-------+------+-------
771+
-5.2 | -5.2 | 0 | 0 | 6 | 6 | 0 | 0 | 0 | 0 | 4 | 4
772+
-0.0000000001 | -1e-10 | 0 | 0 | 6 | 6 | 0 | 0 | 0 | 0 | 5 | 5
773+
0.000000000001 | 1e-12 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
774+
1 | 1 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
775+
1.99999999999999 | 1.99999999999999 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
776+
2 | 2 | 2 | 2 | 5 | 5 | 1 | 1 | 0 | 0 | 6 | 6
777+
2.00000000000001 | 2.00000000000001 | 2 | 2 | 4 | 4 | 1 | 1 | 0 | 0 | 6 | 6
778+
3 | 3 | 2 | 2 | 4 | 4 | 1 | 1 | 0 | 0 | 6 | 6
779+
4 | 4 | 3 | 3 | 4 | 4 | 2 | 2 | 0 | 0 | 6 | 6
780+
4.5 | 4.5 | 3 | 3 | 3 | 3 | 2 | 2 | 0 | 0 | 6 | 6
781+
5 | 5 | 3 | 3 | 3 | 3 | 3 | 3 | 1 | 1 | 7 | 7
782+
5.5 | 5.5 | 3 | 3 | 3 | 3 | 3 | 3 | 21 | 21 | 7 | 7
783+
6 | 6 | 4 | 4 | 3 | 3 | 3 | 3 | 21 | 21 | 7 | 7
784+
7 | 7 | 4 | 4 | 2 | 2 | 4 | 4 | 21 | 21 | 7 | 7
785+
8 | 8 | 5 | 5 | 2 | 2 | 5 | 5 | 21 | 21 | 7 | 7
786+
9 | 9 | 5 | 5 | 1 | 1 | 5 | 5 | 21 | 21 | 7 | 7
787+
9.99999999999999 | 9.99999999999999 | 5 | 5 | 1 | 1 | 5 | 5 | 21 | 21 | 7 | 7
788+
10 | 10 | 6 | 6 | 1 | 1 | 5 | 5 | 21 | 21 | 8 | 8
789+
10.0000000000001 | 10.0000000000001 | 6 | 6 | 0 | 0 | 5 | 5 | 21 | 21 | 8 | 8
790+
(19 rows)
791+
792+
-- for float8 only, check positive and negative infinity: we require
793+
-- finite bucket bounds, but allow an infinite operand
794+
SELECT width_bucket(0.0::float8, 'Infinity'::float8, 5, 10); -- error
795+
ERROR: lower and upper bounds must be finite
796+
SELECT width_bucket(0.0::float8, 5, '-Infinity'::float8, 20); -- error
797+
ERROR: lower and upper bounds must be finite
798+
SELECT width_bucket('Infinity'::float8, 1, 10, 10),
799+
width_bucket('-Infinity'::float8, 1, 10, 10);
800+
width_bucket | width_bucket
801+
--------------+--------------
802+
11 | 0
803+
(1 row)
782804

783805
DROP TABLE width_bucket_test;
784806
-- TO_CHAR()
@@ -800,7 +822,7 @@ SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999')
800822
(10 rows)
801823

802824
SELECT '' AS to_char_2, to_char(val, '9G999G999G999G999G999D999G999G999G999G999')
803-
FROM num_data;
825+
FROM num_data;
804826
to_char_2 | to_char
805827
-----------+--------------------------------------------
806828
| .000,000,000,000,000

0 commit comments

Comments
 (0)